Home » SQL & PL/SQL » SQL & PL/SQL » Procedure not returning value due to a select statement (Oracle 9i)
Procedure not returning value due to a select statement [message #316066] Wed, 23 April 2008 11:01 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I hav created a procedure. At a point in the proc I am fetching some data which in turn is not retrnig any record since its not present there. But the loop does't moves forward and exits from the main loop. I am not abl to nderstand why.
Plase have a look at the partial code below.
CREATE OR REPLACE PROCEDURE ts_sms IS
v_role VARCHAR2(50);
v_user_id VARCHAR2(50);
n_cnt NUMBER(9); 
n_tot_cnt NUMBER(9) := 0; 
v_mobileno VARCHAR2(20);
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_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;
--find the mobile number of users from ADM_USERS table
SELECT mobile INTO v_mobileno FROM adm_users
WHERE role_id = v_role
AND is_active = 1
AND user_id = v_user_id;

SELECT NVL(MAX(COUNT(escalation_type)),0) 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')
AND MOD((TRUNC(SYSDATE) - NVL(TRUNC(mgmt.MODIFIED_ON), 
TRUNC(mgmt.CREATED_ON))),DECODE(sms_freq,192,1,193,7,194,30))= 0
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; 
IF cur_visits%FOUND THEN 
SELECT lead_esc_param INTO v_nextv_escParam
FROM ( 
SELECT escParamValue,
LEAD(escParamValue,1) OVER (ORDER BY escParamValue) lead_esc_param
FROM (SELECT DISTINCT ESC_PARAMETER escParamValue,
DECODE(sms_freq,192,1,193,7,194,30,NULL) 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 escalation_type = '187'
AND MOD((TRUNC(SYSDATE) - NVL(TRUNC(mgmt.MODIFIED_ON),
TRUNC(mgmt.CREATED_ON))),DECODE(sms_freq,192,1,193,7,194,30))= 0
ORDER BY escParamValue ))
WHERE escParamValue = v_visits_escParam;
n_visits_cnt := fn_visit_cnt(v_user_id, v_visits_escParam, v_nextv_escParam); 
n_tot_cnt := n_tot_cnt + n_visits_cnt;
END IF;
.
.
..



I have written only the first part of the code.
The CURSOR cur_user_id returns the user_id from table adm_profile_master based on the roleid fetched from CURSOR cur_roleid.
The procedure runs correctly but doesn't executes in case when the SELECT statement returning mobile number doesn't return any record for the fact when due to some reason there is no record in the table ADM_USERS for this user_id. That is for some reason if there is no record entered in the table ADM_USERS and so no mobile, user_id or roleid entered in the ADM_USERS tables.

I understand that the v_mobileno should be blank. But the loop exits at this point though there are more roleids and more users with record in ADM_USERS present and as per logic they should have been processed.
But the procedure exits from the loop only when it doesn't find any record for the user_id for mobole number in the SELECT statement for seeking mobile number.

I tried commenting this SELECT statement and I was able to see the rest user_ids.
SELECT mobile INTO v_mobileno FROM adm_users
WHERE role_id = v_role
AND is_active = 1
AND user_id = v_user_id;


Please advice me as why the loop exits when this select statement doesn't return any value.

I am stuck in it since very long.

Thanks in advance,

Mahi
Re: Procedure not returning value due to a select statement [message #316431 is a reply to message #316066] Fri, 25 April 2008 02:11 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
Can anyone help me this please.....!!! I am new to Cursors.
The cursor is returning rocords but the other select statements when doesn't have data, it exits the main loop. Please give some suggestions as why this is happening.
I do not want it to exit when any statement inside has no data.



Thanks,
Mahi
Re: Procedure not returning value due to a select statement [message #316434 is a reply to message #316431] Fri, 25 April 2008 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, format the code. Enclosing it into [code] tags means nothing when it is not properly formatted. If you expect someone to help, don't make it difficult because people will just move on to the next, properly written question (while yours will remain unanswered).
Re: Procedure not returning value due to a select statement [message #316443 is a reply to message #316434] Fri, 25 April 2008 02:45 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Littlefoot,
I have formatted the code.

CREATE OR REPLACE PROCEDURE ts_sms IS
  v_role VARCHAR2(50);
  v_user_id VARCHAR2(50);
  n_cnt NUMBER(9); 
  n_tot_cnt NUMBER(9) := 0; 
  v_mobileno VARCHAR2(20);
  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_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;
--find the mobile number of users from ADM_USERS table
SELECT mobile INTO v_mobileno FROM adm_users
 WHERE role_id = v_role
   AND is_active = 1
   AND user_id = v_user_id;

SELECT NVL(MAX(COUNT(escalation_type)),0) 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')
   AND MOD((TRUNC(SYSDATE) - NVL(TRUNC(mgmt.MODIFIED_ON), 
       TRUNC(mgmt.CREATED_ON))),DECODE(sms_freq,192,1,193,7,194,30))= 0
 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; 
    IF cur_visits%FOUND THEN 
       SELECT lead_esc_param INTO v_nextv_escParam
         FROM ( 
       SELECT escParamValue,
             LEAD(escParamValue,1) OVER (ORDER BY escParamValue) lead_esc_param
        FROM (SELECT DISTINCT ESC_PARAMETER escParamValue,
                     DECODE(sms_freq,192,1,193,7,194,30,NULL) 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 escalation_type = '187'
                 AND MOD((TRUNC(SYSDATE) - NVL(TRUNC(mgmt.MODIFIED_ON),
                     TRUNC(mgmt.CREATED_ON))),DECODE(sms_freq,192,1,193,7,194,30))= 0
               ORDER BY escParamValue ))
               WHERE escParamValue = v_visits_escParam;
               n_visits_cnt := fn_visit_cnt(v_user_id, v_visits_escParam, v_nextv_escParam); 
               n_tot_cnt := n_tot_cnt + n_visits_cnt;
         END IF;


Thanks,
Mahi

[Updated on: Fri, 25 April 2008 03:05]

Report message to a moderator

Re: Procedure not returning value due to a select statement [message #316513 is a reply to message #316443] Fri, 25 April 2008 08:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You have no exception blocks for NO_DATA_FOUND.
Re: Procedure not returning value due to a select statement [message #317422 is a reply to message #316513] Thu, 01 May 2008 03:05 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Joy,
I have given only partial code of the procedure. Actually I have given the exception for DATA_NOT_FOUND
WHEN DATA_NOT_FOUND
NULL;


If I give some dbms message, it would have given that message for DATA_NOT_FOUND.

But in my case as you see that the SELECT stament only fetches a mobile no in a varible which even if is not present , then it doesn't matter . That is not necessary that every record will have mobile. It should have given NULL to the variable and moved forward to other statement but it exits from all loops and procedure end.
My problem is that it should only put null to the variable when a value is not found and move towards other statements.

Please advice as what should be done to attain this.


Thanks in advance,
Mahi
Re: Procedure not returning value due to a select statement [message #317427 is a reply to message #316066] Thu, 01 May 2008 03:21 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
I think you misunderstood joys reply - you need to handle the no_data_found exception at the select statement, so that it doesn't drop out of the loop to where ever your no_data_found exception is now (as you didn't show that information).

Something like :-
LOOP
 FETCH cur_user_id INTO v_user_id;
 EXIT WHEN cur_user_id%NOTFOUND;
--find the mobile number of users from ADM_USERS table

 begin
   SELECT mobile INTO v_mobileno FROM adm_users
   WHERE role_id = v_role
   AND is_active = 1
   AND user_id = v_user_id;
 exception
 when NO_DATA_FOUND then
   v_mobileno = null;
 end;

SELECT NVL(MAX(COUNT(escalation_type)),0) INTO n_cnt...
Re: Procedure not returning value due to a select statement [message #318845 is a reply to message #317427] Thu, 08 May 2008 03:45 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks you scorpio_biker, I was looking for a answer like this only. I have understood my mistake.

Thanks for helping me on this,

Mahi
Previous Topic: Getting Error : ORA-00911: invalid character
Next Topic: Why PLS-00306 error?
Goto Forum:
  


Current Time: Sun Dec 04 10:23:33 CST 2016

Total time taken to generate the page: 0.09511 seconds