Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT
BULK COLLECT [message #268471] Tue, 18 September 2007 16:10 Go to next message
comson
Messages: 5
Registered: September 2007
Location: Mississauga
Junior Member
Hi,
I am trying to archive data in one table to another table in a different schema using bulk collect. Version is Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production. It looks like my bulk collect didn't collect the intented data. Can you please help me find out what went wrong? Here is my procedure
CREATE OR REPLACE procedure ArchiveTest
txn_period IN NUMBER)
as
type ID1_tmp is table of CDS_TXNHEADER_LOG.ID%type index by binary_integer;
type TXNDATE1_tmp is table of CDS_TXNHEADER_LOG.TXNDATE%type index by binary_integer;
type LOCATIONID1_tmp is table of CDS_TXNHEADER_LOG.LOCATIONID%type index by binary_integer;
type REGISTERID1_tmp is table of CDS_TXNHEADER_LOG.REGISTERID%type index by binary_integer;
type TXNSEQNO1_tmp is table of CDS_TXNHEADER_LOG.TXNSEQNO%type index by binary_integer;
type RECEIPTNO1_tmp is table of CDS_TXNHEADER_LOG.RECEIPTNO%type index by binary_integer;
type CUSTOMERID1_tmp is table of CDS_TXNHEADER_LOG.CUSTOMERID%type index by binary_integer;
type TOTAL1_tmp is table of CDS_TXNHEADER_LOG.TOTAL%type index by binary_integer;
type CONTACT1_tmp is table of CDS_TXNHEADER_LOG.CONTACT%type index by binary_integer;

ID_tmp ID1_tmp;
TXNDATE_tmp TXNDATE1_tmp;
LOCATIONID_tmp LOCATIONID1_tmp;
REGISTERID_tmp REGISTERID1_tmp;
TXNSEQNO_tmp TXNSEQNO1_tmp;
RECEIPTNO_tmp RECEIPTNO1_tmp;
CUSTOMERID_tmp CUSTOMERID1_tmp;
TOTAL_tmp TOTAL1_tmp;
CONTACT_tmp CONTACT1_tmp;

begin
select
txnHdr.ID,
txnHdr.TXNDATE,
txnHdr.LOCATIONID,
txnHdr.REGISTERID,
txnHdr.TXNSEQNO,
txnHdr.RECEIPTNO,
txnHdr.CUSTOMERID,
txnHdr.TOTAL,
txnHdr.CONTACT
BULK COLLECT INTO
ID_tmp,
TXNDATE_tmp,
LOCATIONID_tmp,
REGISTERID_tmp,
TXNSEQNO_tmp,
RECEIPTNO_tmp,
CUSTOMERID_tmp,
TOTAL_tmp,
CONTACT_tmp
from CDS_TXNHEADER_LOG txnHdr
where
TXNDATE < ADD_MONTHS(SYSDATE ,-txn_period)
;

for i in 1..ID_tmp.count
loop
--Bulk Insertion into Archive Schema
INSERT INTO CDS_TXNHEADER_LOG@Remote_Connect
(
ID, TXNDATE,
LOCATIONID,REGISTERID,
TXNSEQNO, RECEIPTNO,
CUSTOMERID, TOTAL,
CONTACT
)
SELECT ID_tmp(i), TXNDATE_tmp(i),
LOCATIONID_tmp(i),REGISTERID_tmp(i),
TXNSEQNO_tmp(i),RECEIPTNO_tmp(i),
CUSTOMERID_tmp(i),TOTAL_tmp(i),
CONTACT_tmp(i)
FROM DUAL;

commit;
end loop;
end;
/


Thanks and Regards,
Arun
Re: BULK COLLECT [message #268472 is a reply to message #268471] Tue, 18 September 2007 16:17 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW the posting guidelines as stated in the #1 STICKY post at top of this forum.


>It looks like my bulk collect didn't collect the intented data.
If you say so.

How are we supposed to know exactly what was intended & what was actually collected??????????????????

[Updated on: Tue, 18 September 2007 16:18] by Moderator

Report message to a moderator

Re: BULK COLLECT [message #268473 is a reply to message #268472] Tue, 18 September 2007 16:24 Go to previous messageGo to next message
comson
Messages: 5
Registered: September 2007
Location: Mississauga
Junior Member
Hi Ana,

The bulk collect should collect rows from CDS_TXNHEADER_LOG table satisfying the date criteria. I gave a valid input such that bulk collect should fetch some rows. When I printed ID_tmp.size, it printed 0. Thats why I said that my bulk collect didn't collect the intented data.

Regards,
Arun

[Updated on: Tue, 18 September 2007 16:25]

Report message to a moderator

Re: BULK COLLECT [message #268587 is a reply to message #268473] Wed, 19 September 2007 02:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why are you using a BULK COLLECT at all?
Why not simply insert straight into CDS_TXNHEADER_LOG@Remote_Connect with the Select from CDS_TXNHEADER_LOG

If you are going to do it your way, you'll want to urgently remove that SELECT ... FROM DUAL from your insert statement, as that will make things a lot slower than they need to be, and you'll want to get that Commit outside the loop.
You'll also want to lookup the FORALL syntax to speed things up a bit more.

If your table doesn't contain any data, then that's most likely because your query didn't return any. Add some code to do a SELECT count(*) using the same criteria, and output that using Dbms_output - then we'll be able to see ho many rows it should return.
Previous Topic: object type
Next Topic: Putting Identification flag with query
Goto Forum:
  


Current Time: Sat Dec 03 01:20:59 CST 2016

Total time taken to generate the page: 0.09929 seconds