Visibility of types inside a procedure (pl/sql)

Question

i have some problems with a pl/sql procedure.

I'm tryng to collect a resulset of records from a query sql into a pl data collection. The code is like this:

DECLARE
testProcedure TB_TESTPROCEDURETYPE;
sqlQuery VARCHAR2(1000);
BEGIN

     sqlQuery := 'SELECT * FROM myTable FETCH FIRST 100 ROWS ONLY';
     EXECUTE IMMEDIATE sqlQuery BULK COLLECT INTO testProcedure;
     DBMS_OUTPUT.PUT_LINE(testProcedure.count);

END;  

The type "TB_TESTPROCEDURETYPE" is a table of the object "TESTPROCEDURETYPE". The structure of TESTPROCEDURETYPE is the same of the "myTable" in the query. Here the way that i used to create these types:

CREATE OR REPLACE TYPE TESTPROCEDURETYPE AS OBJECT(
par1 VARCHAR2(50),
par2 VARCHAR2(2000),
par3 DATE,
par4 VARCHAR2(100),
CONSTRUCTOR FUNCTION TESTPROCEDURETYPE RETURN SELF AS RESULT
);

CREATE OR REPLACE TYPE BODY TESTPROCEDURETYPE AS
CONSTRUCTOR FUNCTION TESTPROCEDURETYPE RETURN SELF AS RESULT IS
BEGIN
SELF.par1 := NULL;
SELF.par2 := NULL;
SELF.par3 := NULL;
SELF.par4 := NULL;

      RETURN;

END;
END;

CREATE TYPE TB_TESTPROCEDURETYPE AS TABLE OF TESTPROCEDURETYPE;

if i run the procedure i got this error:

Errore SQL [932] [42000]: ORA-00932: inconsistent datatypes: expected - got - ORA-06512: at line 9

Someone could help pls?

note: if i don't create the type "TB_TESTPROCEDURETYPE" and i try this way:

DECLARE
  TYPE TB_TESTPROCEDURETYPE IS TABLE OF myTable%ROWTYPE;
  testProcedure TB_TESTPROCEDURETYPE;
  sqlQuery VARCHAR2(1000);
BEGIN
    ....
end;

it works for local procedures. But if i create a procedure and i call it from another context, i got the error "the table doesnt exist".

I tryed different solutions taken on internet but noone works

Answer 1

You have to convert result set to the object TESTPROCEDURETYPE.

For that I just used your type's constructor and it worked:

DECLARE
testProcedure TB_TESTPROCEDURETYPE;
sqlQuery VARCHAR2(1000);
BEGIN

     sqlQuery := 'SELECT TESTPROCEDURETYPE(par1,par2,par3,par4) FROM myTable FETCH FIRST 100 ROWS ONLY';
     EXECUTE IMMEDIATE sqlQuery BULK COLLECT INTO testProcedure;
     DBMS_OUTPUT.PUT_LINE(testProcedure.count);

END;