Function returning table, SQL Error: ORA-00902, "invalid datatype"
PCMASENMay 11 2011 — edited May 13 2011Hello !
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;