Home » SQL & PL/SQL » SQL & PL/SQL » CONDITIONAL STORED PROC CALLING
CONDITIONAL STORED PROC CALLING [message #230357] Wed, 11 April 2007 11:46 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
I am trying to write the stored procedure that calls other stored procedures.

CREATE OR REPLACE PROCEDURE SP_APPROVE(V_ITEM_SID NUMBER, V_STATUS OUT VARCHAR2)
as
BEGIN 
Declare 
col_spec_item number :=0;
Begin
    SP_WORK_TO_PROD1(V_ITEM_SID);
    SP_WORK_TO_PROD2(V_ITEM_SID);
    SP_WORK_TO_PROD3(V_ITEM_SID);
    SP_WORK_TO_PROD4(V_ITEM_SID);
    Call the stored procedure SP_WORK_TO_PROD_COLSPEC only if the item has a COLUMNAR_SPECIFICATION.

      SELECT distinct 1 INTO col_spec_item FROM work_columnar_spec
      WHERE item_sid = v_item_sid;
     
       If col_spec_item = 1 Then 
        SP_WORK_TO_PROD5(V_ITEM_SID);
       End If;
    

    SP_WORK_TO_PROD6(V_ITEM_SID);
    SP_WORK_TO_PROD7(V_ITEM_SID);
      V_STATUS := 'SUCCESS';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN 'FAILURE';

END;
End;
/



What i would like to do here is to call sp_work_to_prod5 conditionally based on the item_sid being there in work_columnar_spec table.

If it is not it should continue to run sp_work_to_prod6 & 7. What happens here is i am getting ORA-01403: no data found because of the select statement. How to rewrite this so that it continues with calling stored procs sp_work_to_prod6 & 7.

A
Re: CONDITIONAL STORED PROC CALLING [message #230359 is a reply to message #230357] Wed, 11 April 2007 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many way to do it.
For instance:
for rec in (select 1 from dual where exists (SELECT null FROM work_columnar_spec WHERE item_sid = v_item_sid))
loop
  SP_WORK_TO_PROD5(V_ITEM_SID);
end loop;
SP_WORK_TO_PROD6(V_ITEM_SID);
SP_WORK_TO_PROD7(V_ITEM_SID);
...

Regards
Michel

Re: CONDITIONAL STORED PROC CALLING [message #230363 is a reply to message #230357] Wed, 11 April 2007 12:16 Go to previous messageGo to next message
srinivas.k2005
Messages: 340
Registered: August 2006
Senior Member
Hi,

SELECT distinct 1 INTO col_spec_item FROM work_columnar_spec
WHERE item_sid = v_item_sid;

Change the above query to:

SELECT count(1) INTO col_spec_item FROM work_columnar_spec
WHERE item_sid = v_item_sid;

This query will not rtuen any exception.

Thx


Re: CONDITIONAL STORED PROC CALLING [message #230466 is a reply to message #230363] Thu, 12 April 2007 01:35 Go to previous message
oralover
Messages: 97
Registered: January 2005
Member
srinivas.k2005 wrote on Wed, 11 April 2007 12:16
Hi,

SELECT distinct 1 INTO col_spec_item FROM work_columnar_spec
WHERE item_sid = v_item_sid;

Change the above query to:

SELECT count(1) INTO col_spec_item FROM work_columnar_spec
WHERE item_sid = v_item_sid;

This query will not rtuen any exception.

Thx





yes, count() always returns a value, zero (0) if has nothing so it will not raise exception.
scott@Test35> truncate table y;

Table truncated.

scott@Test35> select count(*) from y;

 COUNT(*)
---------
        0

1 row selected.

scott@Test35> 

Previous Topic: Eliminate Duplicates
Next Topic: Could Oracle work on Windows Vista?
Goto Forum:
  


Current Time: Thu Dec 08 00:33:58 CST 2016

Total time taken to generate the page: 0.22156 seconds