Oracle: Bulk collect with DBMS_SQL defined array

Question

It doesn't compile. What's wrong?

TYPE recEniFlussiHub IS RECORD
      (
         FLVO_ID                 dbms_sql.varchar2_table,
         flh_id_messaggio        dbms_sql.varchar2_table,
         flh_integrazione_id     dbms_sql.varchar2_table
      );

      TYPE taof_RowCurEniFlussiHub IS TABLE OF recEniFlussiHub;

      curEniFlussiHub        taof_RowCurEniFlussiHub;


      SELECT null FLVO_ID, 
             c.flh_id_messaggio,
             c.flh_integrazione_id,        
        BULK COLLECT INTO curEniFlussiHub
        FROM ENI_FLUSSI_HUB c
       WHERE     c.flh_fornitura = P_FLH_FORNITURA

Is this your complete code example? I guess not as you're missing several elements like a BEGIN keyword. If you want us to spot a syntax error you need to post a coherent code sample which demonstrates just the error you don't understand. And post the compilation error stack as well! — APC
– APC, Commented Jul 17, 2012 at 9:18

APC · Accepted Answer · 2012-07-17 09:24:36Z

If you're going to use nested tables in a record you need to reference their elements in the BULK COLLECT clause. Something like this:

  SELECT null FLVO_ID,   
         c.flh_id_messaggio,  
         c.flh_integrazione_id        
    BULK COLLECT INTO curEniFlussiHub.FLVO_ID
              ,   curEniFlussiHub.flh_id_messaggio        
              ,   curEniFlussiHub.flh_integrazione_id
    FROM ENI_FLUSSI_HUB c  
   WHERE     c.flh_fornitura = P_FLH_FORNITURA

Also you just need to define the variable as an instance of the record: you don't need to have an additional table type i.e.

declare
    recEniFlussiHub IS RECORD 
      ( 
         FLVO_ID                 dbms_sql.varchar2_table, 
         flh_id_messaggio        dbms_sql.varchar2_table, 
         flh_integrazione_id     dbms_sql.varchar2_table 
     ); 

  curEniFlussiHub        recEniFlussiHub;

Ollie · Accepted Answer · 2012-07-17 09:23:23Z

1

You have declared your record type incorrectly (I am assuming your columns flh_id_messaggio and flh_integrazione_id are not themselves nested tables).

If you are creating an associative array to hold the values selected, each field doesn't have to be a collection in itself (and you had an extra comma in your select statement after c.flh_integrazione_id):

(I have assumed your flvo_id is a VARCHAR2 as you tried to declare it as a VARCHAR2_TABLE, if it is a NUMBER then declare it as such in your record declaration).

TYPE receniflussihub IS RECORD(
     flvo_id                 VARCHAR2,
     flh_id_messaggio        eni_flussi_hub.flh_id_messaggio%TYPE,
     flh_integrazione_id     eni_flussi_hub.flh_integrazione_id%TYPE
);

TYPE taof_rowcureniflussihub IS TABLE OF receniflussihub;


cureniflussihub  taof_rowcureniflussihub;

SELECT NULL flvo_id,
       c.flh_id_messaggio,
       c.flh_integrazione_id
  BULK COLLECT INTO cureniflussihub
  FROM eni_flussi_hub C
 WHERE c.flh_fornitura = p_flh_fornitura;

See Here

hope it helps...

EDIT:

From your comment, if you NEED to select into a record of collections (rather than a collection of records) then you do not need to declare the table TYPE, just a record of your decalred record type and select into that (don't forget that in your code you still had the extra comma after c.flh_integrazione_id in your select statement):

Try this:

TYPE receniflussihub IS RECORD(
     flvo_id                 dbms_sql.varchar2_table,
     flh_id_messaggio        dbms_sql.varchar2_table,
     flh_integrazione_id     dbms_sql.varchar2_table
);

receniflussihub_rec receniflussihub;

SELECT NULL flvo_id,
       c.flh_id_messaggio,
       c.flh_integrazione_id
  BULK COLLECT INTO receniflussihub_rec.flvo_id,
                    receniflussihub_rec.flh_id_messaggio,
                    receniflussihub_rec.flh_integrazione_id
  FROM eni_flussi_hub C
 WHERE c.flh_fornitura = p_flh_fornitura;

edited Jul 17, 2012 at 9:23

answered Jul 17, 2012 at 9:12

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

2 Comments

Revious Over a year ago

Thanks, but I need to use it with a FORALL insert and the table doesn't have the last field (flh_integrazione_id) so I think I can't manage in a way like that: "FORALL i IN 1 .. curEniFlussiHub.COUNT \n INSERT INTO ENI_FLUSSI_VAR_OPZTAR\n curEniFlussiHub(i)\n#####I follow thi example: "Fast Way 3 Fetch into DBMS_SQL defined array" --> psoug.org/reference/array_processing.html

Ollie Over a year ago

What version of Oracle are you running?

Collectives™ on Stack Overflow

Oracle: Bulk collect with DBMS_SQL defined array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related