ORA-00902: invalid datatype [message #272359] |
Thu, 04 October 2007 13:07  |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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.....
i am sorry I cannot give out the db version I am using, as I work at Oracle . 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 #272388 is a reply to message #272366] |
Thu, 04 October 2007 17:02   |
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   |
flyboy
Messages: 1903 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   |
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   |
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   |
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   |
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?
|
|
|
|
|