Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00902: invalid datatype
ORA-00902: invalid datatype [message #272359] Thu, 04 October 2007 13:07 Go to next message
armalock
Messages: 7
Registered: October 2007
Junior Member
Hi all,

I am getting this ORA-00902: invalid datatype error on something that should work IMO. I have these in my pkgdef:

TYPE TEST_REC IS RECORD
( message VARCHAR2(4000),
params VARCHAR2(4000)
);
TYPE TEST_RECS IS REF CURSOR RETURN TEST_REC;
TYPE TEST_REC_ARRAY IS TABLE OF TEST_REC;

and I defined a procedure that returns TEST_RECS (the cursor).

PROCEDURE TEST_ME(p_results OUT TEST_RECS)
IS
my_array TEST_REC_ARRAY := TEST_REC_ARRAY();
BEGIN
OPEN p_results FOR SELECT * FROM TABLE(CAST(my_array AS TEST_REC_ARRAY));
END;

This throws the aforementioned exception.

I tried variations such as:

OPEN p_results FOR
SELECT TEST_REC(message, params) FROM TABLE(CAST(........

None of it seems to work.

What am I missing here? Shouldnt I be able to construct a cursor with TEST_REC signature out of a TEST_REC_ARRAY?

Thanks for your help.
Re: ORA-00902: invalid datatype [message #272361 is a reply to message #272359] Thu, 04 October 2007 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste the session.

Regards
Michel

[Updated on: Thu, 04 October 2007 13:12]

Report message to a moderator

Re: ORA-00902: invalid datatype [message #272363 is a reply to message #272359] Thu, 04 October 2007 13:16 Go to previous messageGo to next message
armalock
Messages: 7
Registered: October 2007
Junior Member
BTW, same code works if I define the types test_rec and test_rec_array as schema level types. I seriously do not want to do that though, is there any way around this problem via using package level types?

Also:

1. Even if I use a generic cursor (with no RETURN clause), I get the same error.
2. If I remove the CAST() operator, I get PLS-00642: local collection types not allowed in SQL statements. Great..... Confused

i am sorry I cannot give out the db version I am using, as I work at Oracle Smile. However, this should be a generic problem I think, not a db version specific one. Regardless, please let me know if there is a workaround you can think of.

[Updated on: Thu, 04 October 2007 13:22]

Report message to a moderator

Re: ORA-00902: invalid datatype [message #272366 is a reply to message #272363] Thu, 04 October 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am sorry I cannot give out the db version I am using, as I work at Oracle

When you work at Oracle you cannot "select * from v$version" or execute "dbms_utility.db_version"? Very strange.

PL/SQL types are not known at SQL level, no workaround to this.
You have to create permanent type.

Regards
Michel
Re: ORA-00902: invalid datatype [message #272388 is a reply to message #272366] Thu, 04 October 2007 17:02 Go to previous messageGo to next message
armalock
Messages: 7
Registered: October 2007
Junior Member
Thanks for the answer.

Since there is no workaround for this particular situation, is there any way to use local types and return data to the JDBC layer?

That is essentially what I am trying to do. I use records because they are an elegant way to abstract data, but its pretty much useless to me if I cannot push the results to the midtier w/o writing it to a permanent table.
Re: ORA-00902: invalid datatype [message #272407 is a reply to message #272359] Thu, 04 October 2007 23:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
No workaround.

Not sure what you meant with permanent table in your comment. SQL Types (see CREATE TYPE SQL statement) are just definitions as PL/SQL Types are. The only difference is their scope (database instance vs. PL/SQL package/block).
Re: ORA-00902: invalid datatype [message #272461 is a reply to message #272407] Fri, 05 October 2007 02:35 Go to previous messageGo to next message
armalock
Messages: 7
Registered: October 2007
Junior Member
I understand the scope issue now.

I just need to return a batch of TEST_RECs to midtier. Apparently, I cannot use SQL to open a cursor for them. I was just wondering if there is any other way of doing this using PL/SQL Package types w/o creation anything at the schema level.

The "permanent table" comment I made referred to a possible solution to that problem. I could potentially create a table to hold TEST_RECs in the schema and then write the records to it. Then I can use SQL to return a cursor to the midtier. But that defeats the purpose coz as I said im trying to avoid any schema modifications (table/type creation).
Re: ORA-00902: invalid datatype [message #272462 is a reply to message #272461] Fri, 05 October 2007 02:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use SQL to return a cursor containing this data. The only restriction is that all the data types have to be visible to SQL.
So, instead of defining the type in the package header, just define it in SQL using the CREATE TYPE command.

You are already creating objects in the schema by using a CREATE OR REPLACE PACKAGE command - what does one more object matter?

SQL can only return columns of types that it knows about. If you can't create a new type for this data, then SQL can't return data of that type.
Re: ORA-00902: invalid datatype [message #272464 is a reply to message #272462] Fri, 05 October 2007 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could return a collection of records of type TEST_REC.
Something like:
  TYPE TEST_REC IS RECORD
   (message VARCHAR2(4000),
    params  VARCHAR2(4000) );

  TYPE TEST_REC_ARRAY IS TABLE OF TEST_REC;

  t_test    test_rec_array := test_Rec_array();
  r_test    test_rec;
BEGIN
  for rec in (SELECT lvl,rw
              from (select level lvl,'Row '||level rw 
                    from dual connect by level <= 10)) loop
    t_Test.extend;
    r_test.message := rec.lvl;
    r_test.params  := rec.rw;
    t_test(t_test.count) := r_Test;
    
  end loop;
  
  RETURN t_Test;
end;
/
Re: ORA-00902: invalid datatype [message #272610 is a reply to message #272464] Fri, 05 October 2007 13:15 Go to previous messageGo to next message
armalock
Messages: 7
Registered: October 2007
Junior Member
Thanks for the reply. This compiles and works properly. However, how do I retrieve this collection from JDBC?

When I do this:
stmt.registerOutParameter(1,  OracleTypes.ARRAY, "TEST_REC_ARRAY");


I get "invalid name pattern" exception. I tried TEST.TEST_REC_ARRAY (the package name). Did not work either. Any thoughts?
Re: ORA-00902: invalid datatype [message #272612 is a reply to message #272610] Fri, 05 October 2007 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because, once again, this is not a SQL type but a PL/SQL one, and so it can't get out of the database.

Regards
Michel
Re: ORA-00902: invalid datatype [message #272615 is a reply to message #272612] Fri, 05 October 2007 13:32 Go to previous message
armalock
Messages: 7
Registered: October 2007
Junior Member
Alrite guys. Thanks a lot for your help Smile
Previous Topic: Using a RECORD as an IN parameter in a FUNCTION
Next Topic: can we call any procedure from select statement
Goto Forum:
  


Current Time: Sun Dec 04 16:44:35 CST 2016

Total time taken to generate the page: 0.05244 seconds