Home » SQL & PL/SQL » SQL & PL/SQL » Cursor already open Error
| Cursor already open Error [message #313332] |
Fri, 11 April 2008 07:06  |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi,
I am writting a procedure that is used to send sms to clients.
I have to later call some functions here to do that.
The data fetched from two cursors cur_visits and cur_leads needs to be sent to functions one record at a time. The dbms_output.put_line shows the resule.
But I am getting the error "Cursor already open.
It seems the nested cursors opening and closing is not working correctly.
Please help me on this as I have been trying since long but its not getting me correct result.
CREATE OR REPLACE PROCEDURE ts_sms IS
v_role VARCHAR2(50);
v_user_id VARCHAR2(50);
v_visits_escParam VARCHAR2(50);
v_leads_escParam VARCHAR2(50);
v_login_escParam VARCHAR2(50);
v_visits_sms_freq VARCHAR2(50);
v_leads_sms_freq VARCHAR2(50);
v_login_sms_freq VARCHAR2(50);
v_sms_str VARCHAR2(200);
v_login_percentage VARCHAR2(4) := '30';
n_cnt NUMBER(9);
n_visits_cnt NUMBER(9);
n_leads_cnt NUMBER(9);
n_login_cnt NUMBER(9);
n_tot_cnt NUMBER(9);
n_val NUMBER(9);
CURSOR cur_roleid IS
SELECT DISTINCT detl.esc_role_id roleid
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND escalation_type IN('187','188')
AND tolerance_type = '191'
AND is_active = '1';
CURSOR cur_visits IS
SELECT ESC_PARAMETER escParamValue, DECODE(sms_freq,192,1,193,7,194,30) sms_freq
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND tolerance_type='191'
AND is_active='1'
AND detl.ESC_ROLE_ID = v_role
AND escalation_type = '187';
CURSOR cur_leads IS
SELECT ESC_PARAMETER escParamValue,
DECODE(sms_freq,192,1,193,7,194,30) sms_freq
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND tolerance_type='191'
AND is_active='1'
AND detl.ESC_ROLE_ID = v_role
AND escalation_type = '188';
CURSOR cur_user_id IS
SELECT user_id
FROM adm_profile_master
WHERE role_id = v_role
AND is_active = 1;
BEGIN
n_cnt := 0;
OPEN cur_roleid;
LOOP
FETCH cur_roleid INTO v_role;
EXIT WHEN cur_roleid%NOTFOUND;
OPEN cur_user_id;
LOOP
FETCH cur_user_id INTO v_user_id;
EXIT WHEN cur_user_id%NOTFOUND;
SELECT MAX(COUNT(escalation_type)) INTO n_cnt
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND tolerance_type = '191'
AND is_active = '1'
AND detl.ESC_ROLE_ID = v_role
AND escalation_type IN('187','188')
GROUP BY escalation_type;
OPEN cur_visits;
OPEN cur_leads;
FOR i in 1..n_cnt
LOOP
FETCH cur_visits INTO v_visits_escParam, v_visits_sms_freq;
-- dbms_output.put_line('v_visits_escParam :'||v_visits_escParam);
dbms_output.put_line(v_visits_escParam||':'||v_visits_sms_freq);
FETCH cur_leads INTO v_leads_escParam, v_leads_sms_freq;
-- dbms_output.put_line('v_leads_escParam :'||v_leads_escParam);
dbms_output.put_line(v_leads_escParam||' :'||v_leads_sms_freq);
EXIT WHEN (cur_visits%NOTFOUND) AND (cur_leads%NOTFOUND);
-- DBMS_OUTPUT.PUT_LINE('Counter is : '||TO_CHAR(i));
END LOOP;
CLOSE cur_leads;
CLOSE cur_visits;
END LOOP;
END LOOP;
CLOSE cur_user_id;
CLOSE cur_roleid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
END ts_sms;
/
Thanks in advance,
Mahi
|
|
|
|
|
|
| Re: Nested Cursor [message #313353 is a reply to message #313336] |
Fri, 11 April 2008 08:28  |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Thanks Thomas,
I closed the cursor in right place and it worked.
I have one more problem with my procedure.
The two cursors cur_visits and cur_leads are both opened and data is fetched from them simultaneously.
I am displaying the values from both cursors as :
(v_visits_escParam||' :'||v_visits_sms_freq);
(v_leads_escParam||' :'||v_leads_sms_freq);
at one time in the loop.
In a case there are 3 records in cursor cur_visits and 2 records in cur_leads.
In that case I should get the values as :
30:1 -- from first cursor
3:7 -- from second cursor
60:2 -- from first cursor
6:7 -- from second cursor
90:3 -- from first cursor
But I am getting one extra value
30:1 -- from first cursor
3:7 -- from second cursor
60:2 -- from first cursor
6:7 -- from second cursor
90:3 -- from first cursor
6:7 -- from second cursor -- EXTRA VALUE
This extra value I am getting because the second cursor is still open and I can't close it in between as it needs to go in loop together as I need one record from each of them one at a time together.
I have tried using IF cur_leads%FOUND THEN but this is not working.
Please suggest a way to display the value from cursor not repeatedly or even in any case there is no record for second cursor then only the value of first cursor should be displayed.
Please advice a way to restrict that. The IF THEN ELSE condition using cur_leads%FOUND is not working as if I use this it display blank result for the values of cursor.
CREATE OR REPLACE PROCEDURE ts_sms IS
v_role VARCHAR2(50);
v_user_id VARCHAR2(50);
v_visits_escParam VARCHAR2(50);
v_leads_escParam VARCHAR2(50);
v_login_escParam VARCHAR2(50);
v_visits_sms_freq VARCHAR2(50);
v_leads_sms_freq VARCHAR2(50);
v_login_sms_freq VARCHAR2(50);
v_sms_str VARCHAR2(200);
v_login_percentage VARCHAR2(4) := '30';
n_cnt NUMBER(9);
n_visits_cnt NUMBER(9);
n_leads_cnt NUMBER(9);
n_login_cnt NUMBER(9);
n_tot_cnt NUMBER(9);
n_val NUMBER(9);
CURSOR cur_roleid IS
SELECT DISTINCT detl.esc_role_id roleid
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND escalation_type IN('187','188')
AND tolerance_type = '191'
AND is_active = '1'
AND detl.esc_role_id = '182';
CURSOR cur_visits IS
SELECT ESC_PARAMETER escParamValue, DECODE(sms_freq,192,1,193,7,194,30) sms_freq
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND tolerance_type='191'
AND is_active='1'
AND detl.ESC_ROLE_ID = v_role
AND escalation_type = '187'
ORDER BY escParamValue;
CURSOR cur_leads IS
SELECT ESC_PARAMETER escParamValue,
DECODE(sms_freq,192,1,193,7,194,30) sms_freq
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND tolerance_type='191'
AND is_active='1'
AND detl.ESC_ROLE_ID = v_role
AND escalation_type = '188'
ORDER BY escParamValue;
CURSOR cur_user_id IS
SELECT user_id
FROM adm_profile_master
WHERE role_id = '182' AND user_id IN ('AMEY_GODE', 'BORIVALIBANDRATL1') --v_role
AND is_active = 1;
BEGIN
n_cnt := 0;
OPEN cur_roleid;
LOOP
FETCH cur_roleid INTO v_role;
EXIT WHEN cur_roleid%NOTFOUND;
OPEN cur_user_id;
LOOP
FETCH cur_user_id INTO v_user_id;
dbms_output.put_line('userid is :'||v_user_id);
EXIT WHEN cur_user_id%NOTFOUND;
SELECT MAX(COUNT(escalation_type)) INTO n_cnt
FROM ADM_TOLERANCE_MGMT mgmt,
ADM_TOL_ESCALATION esc,
ADM_ESC_ROLE_DETAILS detl
WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID
AND esc.esc_id = detl.esc_id
AND operation_type_id = '2'
AND role = '182'
AND tolerance_type = '191'
AND is_active = '1'
AND detl.ESC_ROLE_ID = v_role
AND escalation_type IN('187','188')
GROUP BY escalation_type;
OPEN cur_visits;
OPEN cur_leads;
FOR i in 1..n_cnt
LOOP
IF cur_visits%FOUND THEN
FETCH cur_visits INTO v_visits_escParam, v_visits_sms_freq;
END IF;
dbms_output.put_line('v_visits_escParam :'||v_visits_escParam);
dbms_output.put_line(v_visits_escParam||':'||v_visits_sms_freq);
IF cur_leads%FOUND THEN
FETCH cur_leads INTO v_leads_escParam, v_leads_sms_freq;
END IF;
dbms_output.put_line('v_leads_escParam :'||v_leads_escParam);
dbms_output.put_line(v_leads_escParam||' :'||v_leads_sms_freq);
EXIT WHEN (cur_visits%NOTFOUND) AND (cur_leads%NOTFOUND);
END LOOP;
CLOSE cur_leads;
CLOSE cur_visits;
END LOOP;
CLOSE cur_user_id;
END LOOP;
CLOSE cur_roleid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
END ts_sms;
/
Thanks,
Mahi
[Updated on: Fri, 11 April 2008 08:34] Report message to a moderator |
|
|
|
| Re: Nested Cursor [message #313373 is a reply to message #313353] |
Fri, 11 April 2008 09:43 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
As it looks to me, both cursors are basically identical except the escalation_type in the where clause.
I would probably just make one cursor which selects both escalation types in the order I want to process them, and then do all if/then/else switching for different things I want to do inside just one cursor loop.
|
|
|
|
| Re: Nested Cursor [message #313408 is a reply to message #313373] |
Fri, 11 April 2008 15:00 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi Thomas,
I have put the IF condition in correct place and its giving correct result.
OPEN cur_visits;
OPEN cur_leads;
FOR i in 1..n_cnt
LOOP
FETCH cur_visits INTO v_visits_escParam, v_visits_sms_freq;
IF cur_visits%FOUND THEN
dbms_output.put_line(v_visits_escParam||':'||v_visits_sms_freq);
END IF;
FETCH cur_leads INTO v_leads_escParam, v_leads_sms_freq;
IF cur_leads%FOUND THEN
dbms_output.put_line(v_leads_escParam||' :'||v_leads_sms_freq);
END IF;
EXIT WHEN (cur_visits%NOTFOUND) AND (cur_leads%NOTFOUND);
END LOOP;
CLOSE cur_leads;
CLOSE cur_visits;
But its being done using two cursors.
Can you guide me as how can I do this using only one cursor.
Thanks,
Mahi
[Updated on: Fri, 11 April 2008 15:02] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Feb 15 08:37:46 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
