Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Function returning table, SQL Error: ORA-00902, "invalid datatype"

PCMASENMay 11 2011 — edited May 13 2011
Hello !

I have created a package with a function returning a table, when I compile the package I got no errors.
When I try to access the new function I got the SQL Error: ORA-00902.
I have done a light version for test.

I cant get what's wrong in the datatype?

/H

Code for test:

select * from table(ABC.fC('qqqq','wwww'));

Error output:

Error starting at line 1 in command:
select * from table(ABC.fC('qqqq','wwww'))
Error at Command Line:1 Column:20
Error report:
SQL Error: ORA-00902: ogiltig datatyp
00902. 00000 - "invalid datatype"
*Cause:
*Action:

Package spec:

create or replace PACKAGE ABC AS

Type rA_Type is record (AA varchar2(50), AB DATE, AC varchar2(4000));
TYPE tB_type is table of rA_Type;

function fC(CA_ in varchar2, CB_ in varchar2) return tB_type;

END ABC;


Package body:

create or replace PACKAGE BODY ABC AS

function fC(CA_ in varchar2, CB_ in varchar2) return tB_type AS
sqlstm VARCHAR2(4000);
OutTable tB_type;
BEGIN
sqlstm := ' select ''Caaaa'',Sysdate,'|| CA_ || CB_ ||' into OutTable from dual;' ;
EXECUTE IMMEDIATE SQLSTM;
RETURN OutTable;
END fC;

END ABC;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 10 2011
Added on May 11 2011
5 comments
4,673 views