SQL & PL/SQL

Hi,

I have written a procedure (lookup) inside a package (lookup_pkg) as shown below.
Procedure has an output variable of type PL/SQL TABLE which is defined in the package.
I want to write a wrapper procedure lookupref to the procedure lookup to return a ref cursor.

CREATE OR REPLACE PACKAGE lookup_pkg AS
TYPE t_lookup_refcur IS REF CURSOR;

CURSOR c_lookup IS
Select columns1,2,3,....100
FROM A, B, C, D, E
WHERE ROWNUM < 1;

TYPE t_lookup IS TABLE OF c_lookup%ROWTYPE;

Procedure lookup(id Number, o_lookup OUT t_lookup);
End lookup_pkg;

CREATE OR REPLACE PACKAGE BODY lookup_pkg As
Procedure lookup(id Number, o_lookup OUT t_lookup) IS
BEGIN
....
....
END lookup;

Procedure lookupref(id Number, o_lookupref OUT t_lookup_refcur) IS
o_lookup t_lookup;
BEGIN
lookup(id, o_lookup t_lookup);

OPEN t_lookup_refcur FOR
SELECT *
FROM TABLE(CAST(o_lookup AS t_lookup));
Exception
...
End lookupref;
END lookup_pkg;


When I compile this procedure, I am getting invalid datatype Oracle error and
cursor points the datatype t_lookup in the CAST function.
1. Can anyone tell me what is wrong in this. Can I convert a PL/SQL collection (pl/sql table in this case) to PL/SQL datatype table or does it need to be a SQL datatype only (which is created as a type in database).


Also, to resolve this error, I have created a SQL type and table type instead of PL/SQL table in the package as shown below.

create or replace type t_lookuprec as object
(Select columns1,2,3,....100
FROM A, B, C, D, E
WHERE ROWNUM < 1);

create or replace type t_lookup_tab AS table of t_lookuprec;

CREATE OR REPLACE PACKAGE BODY lookup_pkg As
Procedure lookup(id Number, o_lookup OUT t_lookup) IS
BEGIN
....
....
END lookup;

Procedure lookupref(id Number, o_lookupref OUT t_lookup_refcur) IS
o_lookup t_lookup;
BEGIN
lookup(id, o_lookup t_lookup);

OPEN t_lookup_refcur FOR
SELECT *
FROM TABLE(CAST(o_lookup AS t_lookup_tab));
Exception
...
End lookupref;
END lookup_pkg;

When I compile this package, I am getting "PL/SQL: ORA-22800: invalid user-defined type" Oracle error and
points the datatype t_lookup_tab in the CAST function.
2. Can anyone tell me what is wrong. Can I create a type with a select statement and create a table type using type created earlier?

I have checked the all_types view and found that
value for Incomplete column for these two types are YES.
3. What does that mean?

Any suggestions and help is appreciated.

Thanks
Srinivas