retrieve content o user defined table

Question

What I wanna do is, in a function I populate a table (with a user defined type) and then do a select of that table.

I did as follow :

1/ Define the Custom Type :

create or replace TYPE "MVT_CD" AS OBJECT 
( 
  ACCOUNTPK NUMBER(19,0),
  ACCOUNTCODE VARCHAR2(255)  
)

2/ Define a table of that Type

create or replace TYPE "MVT_TAB_CD"  AS TABLE OF MVT_CD

3/ Declare My Funct

create or replace FUNCTION OUTPUT_CD (BUS_DAY VARCHAR2, CODE_AGENCE VARCHAR2) RETURN CD_OUTPUT_TABLE AS
  RETVAL CD_OUTPUT_TABLE := CD_OUTPUT_TABLE();
  ...      
  MVT_TAB MVT_TAB_CD := MVT_TAB_CD();
  ...

BEGIN

   MVT_TAB.EXTEND;
   MVT_TAB(MVT_TAB.COUNT) :=  MVT_CD(123, ' ');

   MVT_TAB.EXTEND;
   MVT_TAB(MVT_TAB.COUNT) :=  MVT_CD(456, ' ');

   FOR I IN (
          SELECT  ACCOUNTPK, ACCOUNTCODE FROM MVT_TAB
   )LOOP
     RETVAL.EXTEND;
     RETVAL(RETVAL.COUNT) := I.ACCOUNTPK|| I.ACCOUNTCODE;
   END LOOP;
   ...
END

The Variable that I used is called : MVT_TAB and I want to do a select to retrieve its containt and used that latter in another part of my function.

When I try to Comile i had : ORA-000942 at the line :

SELECT  ACCOUNTPK, ACCOUNTCODE FROM MVT_TAB

I thought about using BULK COLLECT but I don't know if I am in the good way.

Answer 1

You simply need to cast your structure as a table:

SELECT  ACCOUNTPK, ACCOUNTCODE FROM table(MVT_TAB)