Oracle Database - re:stored procedure - Asked By nithin kumar1519 on 08-Nov-11 04:48 AM

I am trying to create a stored procedure in oracle
Is the syntax right......


CREATE OR REPLACE PROCEDURE emp_selectnext(
   ieno   IN    NUMBER,
   

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


END;
Suchit shah replied to nithin kumar1519 on 08-Nov-11 04:50 AM
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 ]
nithin kumar1519 replied to Suchit shah on 08-Nov-11 04:53 AM
Thanks suchit shah


it seems to be executing withour errors but one compilation errror s coming..
Jitendra Faye replied to nithin kumar1519 on 08-Nov-11 04:53 AM
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.
nithin kumar1519 replied to Jitendra Faye on 08-Nov-11 05:02 AM
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....
Suchit shah replied to nithin kumar1519 on 08-Nov-11 05:04 AM

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;


s j replied to nithin kumar1519 on 08-Nov-11 05:14 AM

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.
nithin kumar1519 replied to s j on 08-Nov-11 05:22 AM
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
Anoop S replied to nithin kumar1519 on 08-Nov-11 06:35 AM
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
s j replied to nithin kumar1519 on 08-Nov-11 11:07 PM


Hi Nithin,

You are always welcome.


Cheers,
Samantha.
Kirtan Patel replied to nithin kumar1519 on 11-Nov-11 03:56 PM
Write your procedure like below code 

CREATE PROCEDURE emp_selectnext
(
 ieno IN NUMBER
)
IS
BEGIN
	SELECT max(eno)+FROM emp_information WHERE ieno =eno;
END;