Home » RDBMS Server » Performance Tuning » MULTI TABLE INSERT USING BULK COLLECT (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
MULTI TABLE INSERT USING BULK COLLECT [message #645454] Mon, 07 December 2015 04:37 Go to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
HI Team,

I have to insert data into two tables with same sequence value.

Below is my anonymous block please suggest me.



DECLARE
CURSOR cr_boa_info
IS
SELECT ACCOUNT,
BOA_DATE,
AMOUNT,
CHECK_NUMBER
FROM SB_CE_BOA_ITI01A_STG
WHERE PROCESS_CODE='ERROR';

type boa_type
IS
TABLE OF cr_boa_info%rowtype INDEX BY binary_integer;
boa_rec boa_type;
BEGIN
OPEN cr_boa_info;
LOOP
FETCH cr_boa_info bulk collect INTO boa_rec;
EXIT
WHEN boa_rec.count = 0;
FOR ALL i IN boa_rec.first..boa_rec.count
INSERT
INTO CE_STATEMENT_HEADERS_INT
(
STATEMENT_NUMBER,
BANK_ACCOUNT_NUM,
STATEMENT_DATE
)
VALUES
(
SB_CE_ITI_01A_SEQ.nextval,
boa_rec(i).ACCOUNT,
boa_rec(i).BOA_DATE
)
INSERT
INTO CE_STATEMENT_LINES_INTERFACE
(
STATEMENT_NUMBER,
BANK_ACCOUNT_NUM,
TRX_DATE,
AMOUNT,
ATTRIBUTE1
)
VALUES
(
SB_CE_ITI_01A_SEQ.nextval,
boa_rec(i).ACCOUNT,
boa_rec(i).BOA_DATE,
boa_rec(i).AMOUNT,
boa_rec(i).CHECK_NUMBER
);
END LOOP;
COMMIT;
CLOSE cr_boa_info;
END;
/


I have to pass same sequence number to statement_number column in both the tables.
Please modify the above block and help me.

Thanks in Advance..



Ramana.

[Updated on: Mon, 07 December 2015 04:39]

Report message to a moderator

Re: MULTI TABLE INSERT USING BULK COLLECT [message #645455 is a reply to message #645454] Mon, 07 December 2015 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Fetch sequence value into a variable and use it in both INSERT statements.
Re: MULTI TABLE INSERT USING BULK COLLECT [message #645456 is a reply to message #645454] Mon, 07 December 2015 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I suggest you to:
1/ Format your post as already requested to you several times
2/ Not do in PL/SQL something you can do in SQL
3/ Post a test case as already requested to you several times


Re: MULTI TABLE INSERT USING BULK COLLECT [message #645457 is a reply to message #645455] Mon, 07 December 2015 04:45 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
For each record the sequence number should change where i have to declare and assign the sequence
Re: MULTI TABLE INSERT USING BULK COLLECT [message #645459 is a reply to message #645454] Mon, 07 December 2015 04:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need nothing more than a simple SQL statement, with a multi-table insert. None of that PL/SQL junk. Something like this,
orclz> create table d1 as select * from dept where 1=2;

Table created.

orclz> create table d2 as select * from dept where 1=2;

Table created.


orclz> create sequence s1;

Sequence created.

orclz> insert all into d1 values(s1.nextval,dname,loc) into d2 values(s1.nextval,dname,loc)
  2  select dname,loc from dept;

8 rows created.

orclz> select * from d1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 ACCOUNTING     NEW YORK
         2 RESEARCH       DALLAS
         3 SALES          CHICAGO
         4 OPERATIONS     BOSTON

orclz> select * from d2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 ACCOUNTING     NEW YORK
         2 RESEARCH       DALLAS
         3 SALES          CHICAGO
         4 OPERATIONS     BOSTON

orclz>
Re: MULTI TABLE INSERT USING BULK COLLECT [message #645466 is a reply to message #645459] Mon, 07 December 2015 05:17 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
I have to insert some common and different columns to two different tables having same sequence number using bulk collect.
Re: MULTI TABLE INSERT USING BULK COLLECT [message #645467 is a reply to message #645466] Mon, 07 December 2015 05:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So do it. I already shown you how. I'm not going to write the exact code for you.





Re: MULTI TABLE INSERT USING BULK COLLECT [message #645471 is a reply to message #645467] Mon, 07 December 2015 05:36 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
Any one can help...
Re: MULTI TABLE INSERT USING BULK COLLECT [message #645473 is a reply to message #645471] Mon, 07 December 2015 05:40 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do what John told you.

Previous Topic: Please help for tuning .sql query
Next Topic: Export from table vs external table
Goto Forum:
  


Current Time: Thu Mar 28 08:20:10 CDT 2024