BULK COLLECT problems...

Dev Shed Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

June 29th, 2004, 04:29 PM

metaBarf

Contributing User

Join Date: Jun 2003

Posts: 373

Time spent in forums: 3 h 49 m 6 sec
Reputation Power: 6

BULK COLLECT problems...

I'm using Oracle 8i --
so, I defined some collection types, declared instances, and did a BULK COLLECT from a cursor into my collections:

Code:

CREATE OR REPLACE PROCEDURE vend_ord_dllr_proc AS

TYPE refcursor IS REF CURSOR;
TYPE pva_type IS TABLE OF MF_ADDR_VEND_ACCT_BK.parn_vend_acct_id%TYPE;
TYPE alv_type IS TABLE OF MF_ADDR_VEND_ACCT_BK.addr_levl_vend_cd%TYPE;

pva_list                  pva_type;
alv_list                  alv_type;


CURSOR vend_acct_info_curs IS
       SELECT PARN_VEND_ACCT_ID, ADDR_LEVL_VEND_CD
       FROM MF_ADDR_VEND_ACCT_BK;

so, then I want to use the collections in a FOR loop that creates a REF CURSOR statement to execute. The problem is, if I do it with FOR, then even though I used the BULK COLLECT, I can catch it executing the "SELECT PARN_VEND_ACCT_ID, ADDR_LEVL_VEND_CD
FROM MF_ADDR_VEND_ACCT_BK"

statement for every entry in the collection!!! (BAD!) So, I am confused -- I thought that creating that collection would get rid of the problem. Or, do I have to find a way to write it using FORALL ?

here's the FOR loop:

Code:

OPEN vend_acct_info_curs;
   FETCH vend_acct_info_curs BULK COLLECT INTO pva_list, alv_list;
FOR i in 1..pva_list.count LOOP

       dbms_output.put_line('data[i].t_pva_id = ' || pva_list(i));
       dbms_output.put_line('data[i].t_alv_cd = ' || alv_list(i));
       pv_refcurs_string := NULL;
       substr_vend_acct := NULL;
       substr_vend_acct := SUBSTR(pva_list(i),
                       (instr (pva_list(i), '&', 1, 2) + 1),
                       (instr (pva_list(i), '&', 1, 3)
                              - instr (pva_list(i), '&', 1, 2) - 1));

       pv_refcurs_string := 'SELECT SUM(T.DLLR_AM) dollar, v.parn_vend_acct_id, v.addr_levl_vend_cd
                              FROM MF_TJ T, MF_AJ A, mf_addr_vend_acct_bk v
                              WHERE A.ACTG_EVNT IN (''SP02'', ''SN02'')
                              AND (T.DOC_ACTN = ''O''
                                  OR T.DOC_ACTN = ''C''
                                  OR T.DOC_ACTN = ''X''
                                  OR T.DOC_ACTN = ''A'')
                              AND T.UID_LO = A.TJ_UID_LO
                              AND T.VEND_ADDR_CD = ''' || alv_list(i) || '''
                              AND T.VEND_CD = ''' || substr_vend_acct || '''
                              and v.parn_vend_acct_id = ''' || pva_list(i) || '''
                              and v.addr_levl_vend_cd = ''' || alv_list(i) || '''
                              group by v.parn_vend_acct_id, v.addr_levl_vend_cd';

	OPEN pv_acct_id_refcurs FOR pv_refcurs_string;
	FETCH pv_acct_id_refcurs INTO v_dllr_am, v_pva_id, v_addr_levl_vend_cd;
      	 	--IF v_dllr_am <> 0 THEN
	DBMS_OUTPUT.PUT_LINE('v_dllr_am = ' || to_char(v_dllr_am));
	DBMS_OUTPUT.PUT_LINE('v_pva_id = ' || v_pva_id);
	DBMS_OUTPUT.PUT_LINE('v_addr_levl_vend_cd = ' || v_addr_levl_vend_cd);
		--added close after inital run - check/clear temp tablespace
        CLOSE pv_acct_id_refcurs;
         --DBMS_OUTPUT.PUT_LINE('dllr_curs_row.DOLLAR = ' || dllr_curs_row.DOLLAR);
         --DBMS_OUTPUT.PUT_LINE('dllr_curs_row.parn_vend_acct_id = ' || dllr_curs_row.parn_vend_acct_id);
         --DBMS_OUTPUT.PUT_LINE('dllr_curs_row.addr_levl_vend_cd = ' || dllr_curs_row.addr_levl_vend_cd);
         --------------UPDATE FROM ORIGINAL CURSOR--------------------
         --UPDATE MF_ADDR_VEND_ACCT V
	 --SET V.ORDD_AM =  dllr_curs_row.DOLLAR, V.TOTL_ORDD_AM = dllr_curs_row.DOLLAR
         --where v.parn_vend_acct_id = dllr_curs_row.parn_vend_acct_id
         --       and v.addr_levl_vend_cd = dllr_curs_row.addr_levl_vend_cd;
        --------------------------------------------------------------
         --UPDATE MF_ADDR_VEND_ACCT_BK V
	 --SET V.ORDD_AM =  v_dllr_am, V.TOTL_ORDD_AM = v_dllr_am
         --where v.parn_vend_acct_id = data(i).t_pva_id
         --       and v.addr_levl_vend_cd = data(i).t_alv_cd;
      --  END IF;

    end loop;

June 30th, 2004, 09:57 AM

metaBarf

Contributing User

Join Date: Jun 2003

Posts: 373

Time spent in forums: 3 h 49 m 6 sec
Reputation Power: 6

I've got a more general explanation of the specific problem I'm having with the algorithm:

what I am trying to achieve is that in this situation:

CURSOR curs1
SELECT var1, var2 FROM tabl1 WHERE bla;

BEGIN

FOR i IN curs1 LOOP
--do some other stuff
END LOOP

I would find that while I thought it would stop executing my curs1 SQL statement because it did it once, and would then work just on my "other stuff", that it was doing the SELECT var1, var2 FROM tabl1 statement for every iteration of the loop. I've done a lot of plsql before, but it's been a while, I don't remember having this problem before; i.e. I would always get good performance even if I did stuff like this . However, in this situation we were trying to cut down the proc. to much lower runtimes, like 4-5 hours. So, I tried doing the SELECT var1, var2 FROM tabl1 BULK COLLECT INTO list_var; --where list_var is a TYPE declared to match records from this SELECT

then doing

FOR i in 1..list_var.count LOOP
--do stuff
END LOOP;

but it is still doing the SQL every iteration of the loop. I tried figuring out FORALL, but that does not seem to work, I need something more flexible -- maybe I should use varray in stead?. What am I doing wrong?

June 30th, 2004, 11:41 AM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

This code is how you execute a cursor

Code:

FOR i IN curs1 LOOP
--do some other stuff
END LOOP

Why don't you put the other data operations inside the same loop? Fetch a row, then process it at the ssame time.

It doesn't look like you need to know anything about the preceeding records (resultset row from cursor) to do your thing.

I do not see why the second loop is "re-running" the SQL in the original cursor. It is opening a second dynamic cursor.
BTW - dynamic cursors like that are slow. See

http://asktom.oracle.com/pls/ask/

This site has a load of extremely technical information. Do a search on "bind variables".

June 30th, 2004, 12:17 PM

metaBarf

Contributing User

Join Date: Jun 2003

Posts: 373

Time spent in forums: 3 h 49 m 6 sec
Reputation Power: 6

I wanted to do the cursor the way that you showed, but if I would watch the SQL statements that the procedure was executing, I could see it chugging away on the cursor that I had supposedly already opened:

OPEN vend_acct_info_curs;
FETCH vend_acct_info_curs BULK COLLECT INTO pva_list, alv_list;

even with this extra abstraction of making the pva_list a local temp table/collection -- I'm not sure if I get what you mean, b/c I do what you stated, "fetch a row, then process it at the same time", and then I open the refcursor inside the loop -- I'm hoping to catch why the backend will keep going abck to the def. of the original cursor.

Should I just reverse the order of the cursors, that's what I think you might be talking about?

June 30th, 2004, 02:29 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

I'm not sure what you are doing, but two points:

1. see the FOR i loop for what I mean. I don't see why you need a table,
unless you are using
it somewhere else
2. lose the dynamic sql -- it's marked by the terrible performance comment.
If you don't know why this is bad vist the asktom link I gave you.

Code:

TYPE refcursor IS REF CURSOR;
TYPE pva_type IS TABLE OF MF_ADDR_VEND_ACCT_BK.parn_vend_acct_id%TYPE;
TYPE alv_type IS TABLE OF MF_ADDR_VEND_ACCT_BK.addr_levl_vend_cd%TYPE;

pva_list                  pva_type;
alv_list                  alv_type;
BINARY_INTEGER            ctr:=0

CURSOR vend_acct_info_curs IS
       SELECT 
       PARN_VEND_ACCT_ID fld1, 
       ADDR_LEVL_VEND_CD fld2
       FROM 
       MF_ADDR_VEND_ACCT_BK;
FOR i in vend_acct_info_curs 
LOOP
       ctr:=ctr+1;
       pva_list(ctr):=  i.fld1;
       alv_type(ctr):=  i.fld2;
       pv_refcurs_string := NULL;
       substr_vend_acct := NULL;
       substr_vend_acct := SUBSTR(pva_list(ctr),
                       (instr (pva_list(ctr), '&', 1, 2) + 1),
                       (instr (pva_list(ctr), '&', 1, 3)
                              - instr (pva_list(ctr), '&', 1, 2) - 1));
       -- start: this is terrible for performance  ---------------------------
       pv_refcurs_string := 'SELECT SUM(T.DLLR_AM) dollar, v.parn_vend_acct_id, v.addr_levl_vend_cd
                              FROM MF_TJ T, MF_AJ A, mf_addr_vend_acct_bk v
                              WHERE A.ACTG_EVNT IN (''SP02'', ''SN02'')
                              AND (T.DOC_ACTN = ''O''
                                  OR T.DOC_ACTN = ''C''
                                  OR T.DOC_ACTN = ''X''
                                  OR T.DOC_ACTN = ''A'')
                              AND T.UID_LO = A.TJ_UID_LO
                              AND T.VEND_ADDR_CD = ''' || alv_list(ctr) || '''
                              AND T.VEND_CD = ''' || substr_vend_acct || '''
                              and v.parn_vend_acct_id = ''' || pva_list(ctr) || '''
                              and v.addr_levl_vend_cd = ''' || alv_list(ctr) || '''
                              group by v.parn_vend_acct_id, v.addr_levl_vend_cd';

	OPEN pv_acct_id_refcurs FOR pv_refcurs_string;
	FETCH pv_acct_id_refcurs INTO v_dllr_am, v_pva_id, v_addr_levl_vend_cd;
        
        CLOSE pv_acct_id_refcurs;
        --- end terrible performance ----------
END LOOP;

June 30th, 2004, 02:37 PM

metaBarf

Contributing User

Join Date: Jun 2003

Posts: 373

Time spent in forums: 3 h 49 m 6 sec
Reputation Power: 6

yea I read those articles on asktom, that's really helpful stuff, it just takes a while for it to sink in. Thanks for the help, I just need to look at the problem differently -- I had always been taught to use the refcursors with constructed SQL, and that is the way to do it in pl/pgsql as well.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: