0

I have a strange problem using bulk collection as element of FROM clause. When I execute this code example, I get, just at run-time, the error "invalid table name".

If I replace the collection with a table everything works well.

Is there any restriction about bulk collection that I'm missing? Maybe I cannot use anonymous block in FROM clause? In the sql debugger I see that l_vol(i) has values but l_vol(i).FIELD doesn't exists.

Thanks.

 TYPE t_bulk_vol is table of vol%ROWTYPE;
 l_vol t_bulk_vol;
 ...
 cursor cur is SELECT * FROM vol where ... ;

 OPEN CUR;
 LOOP 
     FETCH CUR BULK COLLECT INTO l_vol;
     ....
     insert into dest
     select col1, col2, ... from 
     (inner view with some pivot, unpivot and l_vol(i).FIELD ...) src where l_vol(i).FIELD = src.FIELD;

PS: I cannot paste original code.

frank86ba
  • 23
  • 6
  • 1
    The only thing more fun than debugging somebody's shonky code is debugging somebody's shonky code blindfolded. – APC Jul 11 '13 at 17:44

2 Answers2

0

TYPE t_bulk_vol is a PL/SQL type. That means you can only use it in PL/SQL constructs. You cannot use it in SQL, even if it's SQL in a PL/SQL program.

If you want to use a nested table in the FROM clause of a SELECT you will need to define a SQL TYPE. This is a pain, because it means you can't use the %ROWTYPE definition (that's a PL/SQL only keyword). So you'll have to create an object whose signature matches the projection of the table, and then create nested table of that type. Find out more.

Your cursor is defined wrongly. It should just be a SELECT statement. 

cursor cur is SELECT * FROM vol where ... ;

Save the BULK COLLECT INTO l_vol for the actual fetch.

Although presumably this is just a artefact of you faking some PL/SQL because you "cannot paste original code."

APC
  • 144,005
  • 19
  • 170
  • 281
0

I have created the type as you said, but I get the same error at the same point (ORA-00903 - invalid table name).

This is an example of what I've done:

  CREATE TYPE REC_VOL AS OBJECT (
       FIELD1    VARCHAR2(25),
       ...
  );
  create TYPE T_BULK IS TABLE OF REC_VOL;
  ....
  l_vol t_bulk;
  ...

This is the way I collect the records (I don't use the cursor anymore):

    SELECT REC_VOL(FIELD1, ...) BULK COLLECT INTO l_vol
    FROM vol where ...;

The exception is still raised at the insert-select statement.

frank86ba
  • 23
  • 6