Oracle Database - re:stored procedure

Yes, it is seem to be a correct syntax for sored procedure if u want to check for SP syntax then check below one

CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

Thanks suchit shah


it seems to be executing withour errors but one compilation errror s coming..

Follow this sysntax to create stored procedure in oracle-

/* Procedure name */
CREATE PROCEDURE bal_enquiry( 
/* The Parameters area */
cust_id   NUMBER,
account_num NUMBER) 
/* The Returning section */
RETURNING NUMBER;

Hope this will help you.

thankx got it.......


CREATE OR REPLACE PROCEDURE emp_selectnext
(
 ieno        IN             NUMBER,
 iReturn      IN OUT        NUMBER,
 RCT1        OUT          GlobalPkg.RCT1
   

)
AS
 
BEGIN
    OPEN RCT1 FOR       
select max(eno)+1 from emp_information where ieno =eno;

EXCEPTION WHEN OTHERS THEN
      iReturn := -1;
    RETURN;
END;
/

after adding this i got it....

what compilation error is coming i check your SP and i would suggest few things for it

1.  u defined the one parameter called ieno for that i would suggest also defined some default value also like belo

  ieno   IN    NUMBER DEFAULT 0,

2.  check your select query syntax :
    here u have given query like below

    select max(eno)+1 from emp_information where ieno =eno;

    I think it should be 

    select max(eno)+1 from emp_information where ieno =ieno;

Hi,

Im posting a sample procedure. just follow that and let me know if u have any more queries.

create or replace procedure sp_testing
@uname nvarchar(50),
@pwd nvarchar(50),
@age nvarchar(50)

as
begin

insert into login values(@uname,@pwd,@age)
end



Cheers,
Samantha.

Suchit shah and Samantha thank you very much guyz..

hey suchit the sp which you posted also got executed thanks buddy...
thank you samanata


thanks and regards
A NithinKumar

I think there is no error on your syntax, if you have any doubt just refer this example

http://www.sqlinfo.net/oracle/oracle_stored_procedure_SELECT.php

Hi Nithin,

You are always welcome.


Cheers,
Samantha.