Home » SQL & PL/SQL » SQL & PL/SQL » help with pl/sql proc
help with pl/sql proc [message #230660] Thu, 12 April 2007 09:24 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
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 COUNT (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;
/

I am pl/sql newbie and i need some help with the above stored procedures.The above stored procs move data from the work table to approved table. Depending on the status of the move, 'SUCCESS'/'FAILURE' is sent as a output parameter.

Should exceptions be handled in all the inner stored procedures also??


[Updated on: Fri, 20 April 2007 01:27] by Moderator

Report message to a moderator

Re: help with pl/sql proc [message #230682 is a reply to message #230660] Thu, 12 April 2007 10:31 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can handle it where you want.
Here is a good place if yu want to handle it here and don't need to handle it in other procedure.

Regards
Michel
Previous Topic: pl/sql views
Next Topic: pragma restrict_references
Goto Forum:
  


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

Total time taken to generate the page: 0.20571 seconds