Home » SQL & PL/SQL » SQL & PL/SQL » Help with the stored proc
Help with the stored proc [message #232128] Thu, 19 April 2007 10:04 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Hi all,

I am new to pl/sql coding. I have written the following procedure which moves data from work_ tables to prod_tables.

I would really appreciate on how & where error handling should be done.The called procedures does the complex logic of moving the monthly data into timeseries based data(prod tables).


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 OTHERS THEN RETURN SQLERRM;

END;
End;
/


Re: Help with the stored proc [message #232138 is a reply to message #232128] Thu, 19 April 2007 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already posted this question and procedure in another topic.
Please continue on this one don't start a new one.

Regards
Michel
Re: Help with the stored proc [message #232297 is a reply to message #232138] Fri, 20 April 2007 01:30 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel, you can post duplicates if you encounter them.

@Ananthi, continue in your other thread.

http://www.orafaq.com/forum/fa/448/0/

MHE

[Updated on: Fri, 20 April 2007 01:31]

Report message to a moderator

Previous Topic: regular expressions
Next Topic: Execute Immediate with DML error: column not allowed here
Goto Forum:
  


Current Time: Fri Dec 02 14:14:41 CST 2016

Total time taken to generate the page: 0.10334 seconds