PLSQL : Insert Result from Cursor into one column of plsql table

Question

The following is my code To create a table object :

TYPE TempObjectsTable IS TABLE OF t_temp_objects%ROWTYPE
      INDEX BY BINARY_INTEGER;

nt_scb_temp_objects TempObjectsTable;

The t_temp_objects has the following Columns defined :

Name           Null? Type          
-------------- ----- ------------- 
INVC_REF             NUMBER        
ORDERS               NUMBER        
ORDER_POS_TYPE       NUMBER        
RULE_CONDITION       VARCHAR2(500) 
CHARGE               NUMBER        
CURRENCY             VARCHAR2(10)  
TXN_DT               DATE

Now, I have a cursor, which returns a lists of Orders, basically numbers.

CURSOR c_orders_frm_grp IS 
select a.ordr_id from sa_order a 
WHERE a.invc_ref is NULL

I am trying to add these to the plsql table created nt_scb_temp_objects above by using bulk collect. But i want the rest of the columns of nt_scb_temp_objects to filled as null for now, as i will be filling these columns as well in the coming steps.

Currently this is what i am trying.

IF c_orders_frm_grp %ISOPEN THEN
         CLOSE c_orders_frm_grp ;
    END IF;
    OPEN c_orders_frm_grp;

            FETCH c_orders_frm_grp BULK COLLECT INTO nt_scb_temp_objects.orders;             

     CLOSE   c_orders_frm_grp;

And this is the error i get : Error(44,74): PLS-00302: component 'ORDERS' must be declared

Kaushik Nayak · Accepted Answer · 2019-03-20 08:14:40Z

1

You do not want that CURSOR and OPEN..FETCH constructs. Simply run a SELECT BULK COLLECT INTO that collection.

DECLARE
TYPE TempObjectsTable IS TABLE OF t_temp_objects%ROWTYPE
      INDEX BY BINARY_INTEGER;
nt_scb_temp_objects TempObjectsTable;
BEGIN
select a.ordr_id as ORDERS,
    null as INVC_REF,
    null as ORDER_POS_TYPE,
    null as RULE_CONDITION,
    null as CHARGE,
    null as CURRENCY,
    null as TXN_DT
  BULK COLLECT INTO nt_scb_temp_objects from sa_order a
WHERE a.invc_ref is NULL ;             
END;
/

DEMO

answered Mar 20, 2019 at 8:14

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mastershefi Over a year ago

Yep. Works, Kaushik, Out of curiosity, why avoid that cursor construct? Will it affect performance?

Kaushik Nayak Over a year ago

@mastershefi : Cursor may be useful for some cases but not here since a simple SQL can achieve what you wanted.

Tim Biegeleisen · Accepted Answer · 2019-03-20 08:03:48Z

0

Why not use an INSERT INTO ... SELECT, and only specify the single column you want to populate now:

INSERT INTO TempObjectsTable(ORDERS)
SELECT ordr_id
FROM sa_order
WHERE invc_ref IS NULL;

In general you should avoid using cursors, as most regular database operations in SQL are already set based.

Note: If the temp table TempObjectsTable does not already exist, then you will have to create it.

edited Mar 20, 2019 at 8:03

answered Mar 20, 2019 at 7:55

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

3 Comments

mastershefi Over a year ago

I could use a command like this on a plsql table? I just tried it out, and it says 'table or view does not exist'

Tim Biegeleisen Over a year ago

@mastershefi My answer assumes that the temp table already exists. If not, then create it first.

mastershefi Over a year ago

So the current process i am using, is just how you are suggesting. I have a GTT , that i populate with insert commands. But due to the large set of orders and the repeated updating of the tables for other column, the time it takes to run the query is a lot. Hence i am trying to shift to Collections. Hoping that I could use Bulk collect and Bulk insert to increase efficiency.

Collectives™ on Stack Overflow

PLSQL : Insert Result from Cursor into one column of plsql table

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related