Home » SQL & PL/SQL » SQL & PL/SQL » Cursor already open Error
Cursor already open Error [message #313332] Fri, 11 April 2008 07:06 Go to next message
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: Cursor already open Error [message #313336 is a reply to message #313332] Fri, 11 April 2008 07:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
CLOSE cur_user_id;

Seems to be in the wrong place. It is outside the loop where it is opened in.
Re: Nested Cursor [message #313353 is a reply to message #313336] Fri, 11 April 2008 08:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous message
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

Previous Topic: ORA-02055 ORA-00001and :new
Next Topic: Query for Finding the Date History
Goto Forum:
  


Current Time: Sun Dec 04 04:45:32 CST 2016

Total time taken to generate the page: 0.09872 seconds