Home » SQL & PL/SQL » SQL & PL/SQL » How to create procedure (Oracle)
How to create procedure [message #419016] Wed, 19 August 2009 10:45 Go to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
I need to know how to create a procedure for a scenario like this: e.g,
Create a stored procedure that will insert and update the data into table



TABLE: ITEM_TBL

ITEM_ID number(5)
TOTAL_Q number(5)
INSERT_DATE date
LAST_UPDATE_DATE date
USER_ID varchar2(5)
COUNT_QTY number(5)
STATUS_CODE varchar2(30)
REMARKS varchar2(240)


Insert Proc:
Name: proc_insert
Input Parameter:  
P_ITEM_ID  number
P_TOTAL_Q number
P_UPDATE_USR_ID Varchar
P_COUNT_QTY DEFAULT IS ZERO FOR INSERT 
P_STATUS_CODE    
Output parameter  
 0 or -1 (-1 FOR FAILURE)

INSERT_DATE  (use Sysdate)  
--only when insert is done for the first time
LAST_UPDATE_DATE (Use Sysdate) 
-- occurs all the time both insert and update 


------------------------------------------------------------------------------------------------------------------------------------- --------------
Update Proc:

Name: proc_update

 

Input Parameter:  
P_ITEM_ID  number
P_UPDATE_USR_ID Varchar
P_COUNT_QTY DEFAULT Zero always for Insert
P_STATUS_CODE    

Output parameter  Success or Failure ( 0 or -1)

UPDATE_DATE ( need to use Sysdate) 
-- occurs all the time both insert and update, when inserted for first time update date is insert date

Thanks

[Updated on: Wed, 19 August 2009 10:54] by Moderator

Report message to a moderator

Re: How to create procedure [message #419020 is a reply to message #419016] Wed, 19 August 2009 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I need to know how to create a procedure for a scenario like this: e.g,
This can be done by using any text editor you choose.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: How to create procedure [message #419042 is a reply to message #419016] Wed, 19 August 2009 11:30 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
I tried this way...


CREATE OR REPLACE PROCEDURE test
( 	List all the in parameters,
	o_ret_cde   OUT  NUMBER,
o_err_mesg OUT VARCHAR2)
AS
o_ret_cde:= 1;
o_err_mesg:='';
BEGIN
       o_err_mesg:='';     
       INSERT INTO item_tbl					
			VALUES( THE PARAMETERS);
			O_ret_code:=0;
      
    EXCEPTION        
        WHEN OTHERS THEN
            RAISE;
			o_ret_code:=-1
			o_err_mesg:=SQLERRM;
END;

[Updated on: Wed, 19 August 2009 11:30]

Report message to a moderator

Re: How to create procedure [message #419045 is a reply to message #419016] Wed, 19 August 2009 11:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what happened?
Re: How to create procedure [message #419051 is a reply to message #419045] Wed, 19 August 2009 12:16 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
Should we use RAISE after WHEN OTHERS?
Re: How to create procedure [message #419077 is a reply to message #419051] Wed, 19 August 2009 15:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
nishita2009 wrote on Wed, 19 August 2009 19:16
Should we use RAISE after WHEN OTHERS?


That's a trick question, since you should never use WHEN OTHERS in this way anyway.

You should definitely never (well, in 99.99999% of cases) use

WHEN OTHERS THEN
     RAISE;


or

WHEN OTHERS THEN
     NULL;


since you do nothing to handle the exception. You just hide and obfuscate the exception.
Re: How to create procedure [message #419213 is a reply to message #419077] Thu, 20 August 2009 07:59 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
EXCEPTION        
        WHEN OTHERS THEN
            RAISE;
			o_ret_code:=-1
			o_err_mesg:=SQLERRM;



Return -1 if successful
so for this logic we can write as
EXCEPTION        
        WHEN OTHERS THEN
          		o_ret_code:=-1
			o_err_mesg:=SQLERRM;


Is this correct?
Thank you!
Re: How to create procedure [message #419293 is a reply to message #419213] Fri, 21 August 2009 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This depends on in what layer of the code this procedure is.
If it is the outer layer, that is exposed to the outside world (e.g. part of an interface, or API) it could be that you don't want to propagate exceptions.
However, if this is a procedure that you only call from other code in your own application, it would be better to not catch this exception but let the outer layer of the application handle it.
Re: How to create procedure [message #419354 is a reply to message #419293] Fri, 21 August 2009 08:05 Go to previous messageGo to next message
nishita2009
Messages: 39
Registered: August 2009
Location: INDIA
Member
its calling from jsp page
Re: How to create procedure [message #419739 is a reply to message #419213] Tue, 25 August 2009 07:24 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
nishita2009 wrote on Thu, 20 August 2009 14:59
EXCEPTION        
        WHEN OTHERS THEN
            RAISE;
			o_ret_code:=-1
			o_err_mesg:=SQLERRM;



Return -1 if successful
so for this logic we can write as
EXCEPTION        
        WHEN OTHERS THEN
          		o_ret_code:=-1
			o_err_mesg:=SQLERRM;


Is this correct?
Thank you!

Shouldn't that assignment happen before RAISE?
Why you are returning SQLERRM? You can provide SQLCODE which is smaller and says the same.
If you handle that error then you should probably log the issue somewhere... Probably you should add a trace there... under that circumstance I might agree that you might not re-raise the exception.
OUT parameters? Wouldn't it be better to create external package that logs the problem and just return a single value (thus create a function) that returns thinks like .exe/ELF/... (meaning: 0=success, !0=failure and its number)... You could just make "RETURN SQLCODE" since it is NULL outside exception - and should be set to a value in the exception.
Previous Topic: SQL Query performance
Next Topic: How to replce fetch in Refcursor
Goto Forum:
  


Current Time: Fri Dec 09 22:54:27 CST 2016

Total time taken to generate the page: 0.13376 seconds