| MULTI TABLE INSERT USING BULK COLLECT [message #645454] |
Mon, 07 December 2015 04:37  |
 |
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 #645459 is a reply to message #645454] |
Mon, 07 December 2015 04:50  |
John Watson
Messages: 8964
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> |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
