| Unable to pass 1st value into the query [message #647113] |
Tue, 19 January 2016 23:01  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
My problem is as follows:
I am trying to fetch values from one table using BULK COLLECT. This brings a few values and based on the count , let's say if it brings 5 values, then I want all the five values to be passed into the cursor. However it's failing to pass all the five values and sends only the last value (i.e. collection.COUNT) and this is causing data to be missed for other values:
Code is as follows:
SET ECHO ON
SET SERVEROUTPUT ON
DECLARE
lv_NumIRFareacd IRF_MSG_TRNSCN_PRCS.irf_area_cd%TYPE;
TYPE Typirfareacd_aa IS TABLE OF IRF_MSG_TRNSCN_PRCS.irf_area_cd%TYPE INDEX BY PLS_INTEGER;
lv_Varirfareacd_aa Typirfareacd_aa;
lv_NumCountIRFareacd PLS_INTEGER;
TYPE TypIrfareacdetails_aa IS RECORD
(msgid IRF_MSG.msg_id%TYPE,
typecd IRF_MSG.type_cd%TYPE,
dscrpttxt IRF_MSG.dscrpt_txt%TYPE,
irf_areacd IRF_MSG_TRNSCN_PRCS.irf_area_cd%TYPE,
trnscnprcsid IRF_MSG_TRNSCN_PRCS.trnscn_prcs_id%TYPE,
recordcreatetms IRF_MSG_TRNSCN_PRCS.record_create_tms%TYPE);
TYPE lv_TypIrfareacodetails_aa IS TABLE OF TypIrfareacdetails_aa INDEX BY PLS_INTEGER;
lv_TypIrfareacdetails lv_TypIrfareacodetails_aa;
CURSOR cur_irfareacdmstrrecs IS
SELECT A.msg_id,
A.type_cd,
A.dscrpt_txt,
B.irf_area_cd,
B.trnscn_prcs_id,
B.record_create_tms
BULK COLLECT INTO lv_TypIrfareacdetails
FROM IRF_MSG A, IRF_MSG_TRNSCN_PRCS B
WHERE (B.irf_area_cd =UPPER(lv_Varirfareacd_aa(lv_NumCountIRFareacd)))
AND (B.msg_id = A.msg_id);
BEGIN
SELECT DISTINCT irf_area_cd
BULK COLLECT INTO lv_Varirfareacd_aa
FROM irf_msg_trnscn_prcs
WHERE irf_area_cd IN ('STCC1', 'STCC2','STCC3','STCC4','STCC5');
lv_NumCountIRFareacd:= lv_Varirfareacd_aa.COUNT;
DBMS_OUTPUT.Put_line('There are:'|| ' '||lv_NumCountIRFareacd||':'||'records or codes');
-- FOR i in 1..lv_NumCountIRFareacd
FOR i IN lv_Varirfareacd_aa.FIRST.. lv_Varirfareacd_aa.LAST
LOOP
DBMS_OUTPUT.put_line('====================================');
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
DBMS_OUTPUT.Put_line('DETAILS FOR:'||' '||lv_Varirfareacd_aa(i));
DBMS_OUTPUT.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
DBMS_OUTPUT.put_line('RECORD # '||' '||'MESSAGE ID'||' '||'TYPE'||' '||' DESCRIPTION'||' '||' AREA'||' '||' TRANSACTION #'||' '||' DATE');
DBMS_OUTPUT.put_line('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
END LOOP;
OPEN cur_irfareacdmstrrecs;
LOOP
FETCH cur_irfareacdmstrrecs BULK COLLECT INTO lv_TypIrfareacdetails LIMIT 100;
EXIT WHEN lv_TypIrfareacdetails.COUNT =0;
FOR j IN 1..lv_TypIrfareacdetails.COUNT
LOOP
DBMS_OUTPUT.put_line(j||' '|| TO_CHAR(lv_TypIrfareacdetails(j).msgid)||' '||TO_CHAR(lv_TypIrfareacdetails(j).typecd)||' '||
TO_CHAR(lv_TypIrfareacdetails(j).dscrpttxt)||' '||TO_CHAR(lv_TypIrfareacdetails(j).irf_areacd)||' '||
TO_CHAR(lv_TypIrfareacdetails(j).trnscnprcsid)||' '||TO_CHAR(lv_TypIrfareacdetails(j).recordcreatetms, 'MM/DD/RRRR HH24:MI:SS'));
END LOOP;
END LOOP;
CLOSE cur_irfareacdmstrrecs;
END;
Output of this is as follows:
There are: 2:records or codes
====================================
DETAILS FOR: STCC1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
RECORD # MESSAGE ID TYPE DESCRIPTION AREA TRANSACTION # DATE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
====================================
DETAILS FOR: STCC2
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
RECORD # MESSAGE ID TYPE DESCRIPTION AREA TRANSACTION # DATE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 82 E invalid. STCC2 45688912 05/21/2015 22:08:46
2 82 E invalid. STCC2 45603640 05/21/2015 13:53:15
3 82 E invalid. STCC2 45564718 04/28/2015 13:17:53
4 82 E invalid. STCC2 45441667 04/27/2015 18:39:48
5 82 E invalid. STCC2 45646276 05/21/2015 19:45:59
6 82 E invalid. STCC2 45495568 04/27/2015 18:40:40
7 82 E invalid. STCC2 45387611 03/27/2015 17:34:53
8 82 E invalid. STCC2 45226637 02/20/2015 15:02:39
9 82 E invalid. STCC2 45062947 11/25/2014 16:22:03
The problem lies here:
CURSOR cur_irfareacdmstrrecs IS
SELECT A.msg_id,
A.type_cd,
A.dscrpt_txt,
B.irf_area_cd,
B.trnscn_prcs_id,
B.record_create_tms
BULK COLLECT INTO lv_TypIrfareacdetails
FROM IRF_MSG A, IRF_MSG_TRNSCN_PRCS B
WHERE (B.irf_area_cd =UPPER(lv_Varirfareacd_aa(lv_NumCountIRFareacd)))
AND (B.msg_id = A.msg_id);
because lv_NumCountIRFareacd returns 2 (lv_NumCountIRFareacd:= lv_Varirfareacd_aa.COUNT;
My question is how can I pass both the values in the above cursor. i.e. at the moment its only fetching values for STCC2 and ignoring STCC1. There are a couple of records for STCC1 and I am unable to pass the 1st value to the above cursor.
Can any one help?
|
|
|
|
| Re: Unable to pass 1st value into the query [message #647118 is a reply to message #647113] |
Wed, 20 January 2016 00:35  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
My question is how can I pass both the values in the above cursor. i.e. at the moment its only fetching values for STCC2 and ignoring STCC1. There are a couple of records for STCC1 and I am unable to pass the 1st value to the above cursor.
Can any one help?
|
|
|
|
|
|
| Re: Unable to pass 1st value into the query [message #647126 is a reply to message #647122] |
Wed, 20 January 2016 03:14 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You have specifically told it to only use a single value out of the array.
You need an in-list: varying in list
Or just combine the two into a single select.
And if you're limiting the bulk collect to 100 at a time you might as well just use a FOR LOOP as that bulk collects 100 records in the background anyway.
|
|
|
|
| Re: Unable to pass 1st value into the query [message #647127 is a reply to message #647113] |
Wed, 20 January 2016 03:19 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
https://community.oracle.com/thread/3888715
Quote:You have a bigger problem. The code and approach are junk. Wrong. Flawed. Garbage.
You do realize that firstly (in the first loop) you are putting the headers to the DBMS_OUTPUT buffer and then you are putting there data of the last collection member (the one at LV_NUMCOUNTIRFAREACD).
Maybe you should use IN operator instead of the euality one (=) and a TABLE collection expression on a SQL type, something like
select * from dual where 'STCC1' in
( select column_value from table (sys.odcivarchar2list( 'STCC1', 'STCC2','STCC3','STCC4','STCC5' )) );
Or, better, use a single cursor as suggested in the other forum.
[Edit: Changed the query to use IN operator]
[Updated on: Wed, 20 January 2016 03:21] Report message to a moderator |
|
|
|
| Re: Unable to pass 1st value into the query [message #647139 is a reply to message #647127] |
Wed, 20 January 2016 05:05 |
 |
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
|
Member |
|
|
This will probably do the trick. Couldn't test it ofcourse 
DECLARE
lv_numirfareacd irf_msg_trnscn_prcs.irf_area_cd%TYPE;
TYPE typirfareacdetails_aa IS RECORD(
msgid irf_msg.msg_id%TYPE
,typecd irf_msg.type_cd%TYPE
,dscrpttxt irf_msg.dscrpt_txt%TYPE
,irf_areacd irf_msg_trnscn_prcs.irf_area_cd%TYPE
,trnscnprcsid irf_msg_trnscn_prcs.trnscn_prcs_id%TYPE
,recordcreatetms irf_msg_trnscn_prcs.record_create_tms%TYPE);
TYPE lv_typirfareacodetails_aa IS TABLE OF typirfareacdetails_aa INDEX BY PLS_INTEGER;
lv_typirfareacdetails lv_typirfareacodetails_aa;
CURSOR cur_irfareacdmstrrecs IS
SELECT a.msg_id
,a.type_cd
,a.dscrpt_txt
,b.irf_area_cd
,b.trnscn_prcs_id
,b.record_create_tms
BULK COLLECT
INTO lv_typirfareacdetails
FROM irf_msg a
,irf_msg_trnscn_prcs b
WHERE (b.irf_area_cd = IN ('STCC1'
,'STCC2'
,'STCC3'
,'STCC4'
,'STCC5'))
AND (b.msg_id = a.msg_id);
BEGIN
OPEN cur_irfareacdmstrrecs;
LOOP
FETCH cur_irfareacdmstrrecs BULK COLLECT
INTO lv_typirfareacdetails LIMIT 100;
EXIT WHEN lv_typirfareacdetails.count = 0;
FOR j IN 1 .. lv_typirfareacdetails.count
LOOP
IF j.irf_area_cd <> lv_numirfareacd
THEN
lv_numirfareacd := j.irf_area_cd;
dbms_output.put_line('====================================');
dbms_output.enable(buffer_size => NULL);
dbms_output.put_line('DETAILS FOR:' || ' ' || lv_numirfareacd);
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('RECORD # ' || ' ' || 'MESSAGE ID' || ' ' || 'TYPE' || ' ' || ' DESCRIPTION' ||
' ' || ' AREA' || ' ' || ' TRANSACTION #' ||
' ' || ' DATE');
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
END IF;
dbms_output.put_line(j || ' ' || to_char(lv_typirfareacdetails(j).msgid) || ' ' || to_char(lv_typirfareacdetails(j).typecd) ||
' ' || to_char(lv_typirfareacdetails(j).dscrpttxt) || ' ' ||
to_char(lv_typirfareacdetails(j).irf_areacd) || ' ' || to_char(lv_typirfareacdetails(j).trnscnprcsid) ||
' ' || to_char(lv_typirfareacdetails(j).recordcreatetms
,'MM/DD/RRRR HH24:MI:SS'));
END LOOP;
END LOOP;
CLOSE cur_irfareacdmstrrecs;
END;
/
|
|
|
|
| Re: Unable to pass 1st value into the query [message #647142 is a reply to message #647139] |
Wed, 20 January 2016 08:14 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Simpler version:
DECLARE
l_prev_irf_area_cd irf_msg_trnscn_prcs.irf_area_cd%TYPE;
BEGIN
FOR rec IN (SELECT a.msg_id
,a.type_cd
,a.dscrpt_txt
,b.irf_area_cd
,b.trnscn_prcs_id
,b.record_create_tms
FROM irf_msg a
JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
WHERE b.irf_area_cd = IN ('STCC1'
,'STCC2'
,'STCC3'
,'STCC4'
,'STCC5')) LOOP
IF rec.irf_area_cd <> l_prev_irf_area_cd OR l_prev_irf_area_cd IS NULL THEN
l_prev_irf_area_cd := rec.irf_area_cd;
dbms_output.put_line('====================================');
dbms_output.enable(buffer_size => NULL);
dbms_output.put_line('DETAILS FOR:' || ' ' || rec.irf_area_cd);
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('RECORD # ' || ' ' || 'MESSAGE ID' || ' ' || 'TYPE' || ' ' || ' DESCRIPTION' ||
' ' || ' AREA' || ' ' || ' TRANSACTION #' ||
' ' || ' DATE');
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
END IF;
dbms_output.put_line(j || ' ' || to_char(rec.msgid) || ' ' || to_char(rec.typecd) ||
' ' || to_char(rec.dscrpttxt) || ' ' ||
to_char(rec.irf_areacd) || ' ' || to_char(rec.trnscnprcsid) ||
' ' || to_char(rec.recordcreatetms
,'MM/DD/RRRR HH24:MI:SS'));
END LOOP;
END;
|
|
|
|
| Re: Unable to pass 1st value into the query [message #647163 is a reply to message #647142] |
Wed, 20 January 2016 22:05 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Cookie monster: Thanks a ton for your reply. I learnt a valuable lesson not to use BULK COLLECT as it took away a lot of PGA (actually I had developed another script which worked for the task in question but took away PGA;so ended up pasting the flawed code on the top - a kind of poor rework).
In fact later on in the day I used an implicit cursor(like the way you have mentioned) and then based on the results obtained, passed each transaction number to obtain the results via an explicit parametric cursor. In the end I was able to accomplish what I wanted via a FOR SELECT implicit cursor and then calling another explicit cursor. The query took about 59-1.09 seconds to execute.
Once again many thanks for your time.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
