Remove From My Forums

stored procedure in oracle sql developer

Question

User364480375 posted

this is my db structure

FNAME	VARCHAR2(30 BYTE)
LNAME	VARCHAR2(30 BYTE)
EMAIL	VARCHAR2(30 BYTE)
PASSWORD	VARCHAR2(30 BYTE)
GENDER	VARCHAR2(30 BYTE)
DOB	VARCHAR2(30 BYTE)
MOBILE	VARCHAR2(30 BYTE)
ADDRESS	VARCHAR2(100 BYTE)

I have to create SP . if any of the details are present in above column then message will be displyed that

records are already present.

else new records will be inserted.

I have tried this ..so suggest. getting error

Error(4,3): PLS-00103: Encountered the symbol "@" when expecting one of the following: <an identifier> <a double-quoted delimited-identifier> current

CREATE OR REPLACE PROCEDURE REGISTER 
(
  @FNAME IN VARCHAR2  
, @LNAME IN VARCHAR2  
, @EMAIL IN VARCHAR2  
, @PASSWORD IN VARCHAR2  
, @GENDER IN VARCHAR2  
, @DOB IN VARCHAR2  
, @MOBILE IN VARCHAR2  
, @ADDRESS IN VARCHAR2  
, @return varchar2(30 byte) out
) AS 
BEGIN
if exists (select * from USERDETAILS WHERE fname=@fname and lname=@lname and
 EMAIL=@EMAIL and PASSWORD=@PASSWORD and GENDER=@GENDER and DOB=@DOB 
 and MOBILE=@MOBILE and ADDRESS=@ADDRESS
 begin
     set @return = 'Records Already Present'    
   end
    else
   begin
    INSERT INTO UserDetails (Fname,Lname,Email,Password,Gender,Dob,Mobile,Address)
    values(@fname,@lname,@EMAIL,@PASSWORD,@GENDER,@DOB ,@MOBILE,@ADDRESS))
     set @return = 'Records Inserted Successfully'
   end
  NULL;
END REGISTER;

Tuesday, June 17, 2014 1:28 AM

All replies

0

Sign in to vote

User364480375 posted

kindly suggest.

Tuesday, June 17, 2014 1:57 AM
0

Sign in to vote
User1957004874 posted

NULL; END REGISTER;

Remove the null from statement. In case if error persist, check the line number where the error is. Try generating templated sp in SQL management studio, it will help.
Tuesday, June 17, 2014 2:44 AM

User724169276 posted

CREATE OR REPLACE PROCEDURE REGISTER 
(
  @FNAME IN VARCHAR2  
, @LNAME IN VARCHAR2  
, @EMAIL IN VARCHAR2  
, @PASSWORD IN VARCHAR2  
, @GENDER IN VARCHAR2  
, @DOB IN VARCHAR2  
, @MOBILE IN VARCHAR2  
, @ADDRESS IN VARCHAR2  
, @return varchar2(30 byte) out
) AS 
BEGIN
if exists (select * from USERDETAILS WHERE fname=@fname and lname=@lname and
 EMAIL=@EMAIL and PASSWORD=@PASSWORD and GENDER=@GENDER and DOB=@DOB 
 and MOBILE=@MOBILE and ADDRESS=@ADDRESS
 begin
     set @return = 'Records Already Present'    
   end
    else
   begin
    INSERT INTO UserDetails (Fname,Lname,Email,Password,Gender,Dob,Mobile,Address)
    values(@fname,@lname,@EMAIL,@PASSWORD,@GENDER,@DOB ,@MOBILE,@ADDRESS))
     set @return = 'Records Inserted Successfully'
   end
  NULL;
END REGISTER;

http://msdn.microsoft.com/en-us/library/ms971506.aspx

Tuesday, June 17, 2014 4:34 AM

User1558924997 posted

CREATE OR REPLACE PROCEDURE REGISTER 
(
  vFNAME IN VARCHAR2  
, vLNAME IN VARCHAR2  
, vEMAIL IN VARCHAR2  
, vPASSWORD IN VARCHAR2  
, vGENDER IN VARCHAR2  
, vDOB IN VARCHAR2  
, vMOBILE IN VARCHAR2  
, vADDRESS IN VARCHAR2  
, vreturn varchar2(30 byte) out
) AS 
BEGIN 
-- declare vreturn varchar(100);

if exists (select * from USERDETAILS WHERE fname=vfname and lname=vlname and
 EMAIL=vEMAIL and PASSWORD=vPASSWORD and GENDER=vGENDER and DOB=vDOB 
 and MOBILE=vMOBILE and ADDRESS=vADDRESS
 begin
     set vreturn = 'Records Already Present'    
   end
    else
   begin
    INSERT INTO UserDetails (Fname,Lname,Email,Password,Gender,Dob,Mobile,Address)
    values(vfname,vlname,vEMAIL,vPASSWORD,vGENDER,vDOB ,vMOBILE,vADDRESS))
     set vreturn = 'Records Inserted Successfully'
   end

END REGISTER;

try after removing all @

Tuesday, June 17, 2014 4:54 AM

0

Sign in to vote

User364480375 posted

ashim, i tried your suggested procedure..but i a getting error:

Error(3,3): PLS-00103: Encountered the symbol "@" when expecting one of the following: <an identifier> <a double-quoted delimited-identifier> current

Tuesday, June 17, 2014 5:24 AM
0

Sign in to vote

User364480375 posted

akfkmupiwu i tried your procedure getting error:

Error(11,18): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character

Tuesday, June 17, 2014 5:26 AM

User724169276 posted

Error(11,18): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character

try this:

CREATE OR REPLACE PROCEDURE REGISTER 
(
  vFNAME IN VARCHAR2  
, vLNAME IN VARCHAR2  
, vEMAIL IN VARCHAR2  
, vPASSWORD IN VARCHAR2  
, vGENDER IN VARCHAR2  
, vDOB IN VARCHAR2  
, vMOBILE IN VARCHAR2  
, vADDRESS IN VARCHAR2  
, vreturn IN VARCHAR2 out
) AS 
BEGIN 

if exists (select * from USERDETAILS WHERE fname=vfname and lname=vlname and
 EMAIL=vEMAIL and PASSWORD=vPASSWORD and GENDER=vGENDER and DOB=vDOB 
 and MOBILE=vMOBILE and ADDRESS=vADDRESS
 begin
     set vreturn = 'Records Already Present'    
   end
    else
   begin
    INSERT INTO UserDetails (Fname,Lname,Email,Password,Gender,Dob,Mobile,Address)
    values(vfname,vlname,vEMAIL,vPASSWORD,vGENDER,vDOB ,vMOBILE,vADDRESS))
     set vreturn = 'Records Inserted Successfully'
   end

END REGISTER;

Tuesday, June 17, 2014 5:31 AM

0

Sign in to vote

User364480375 posted

ashim getting:

Error(11,23): PLS-00103: Encountered the symbol "OUT" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "OUT" to continue.
Error(18,2): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: . ( ) * @ % & - + / at mod remainder rem with <an exponent (**)> and or group having intersect minus start union where connect || multiset

Tuesday, June 17, 2014 5:34 AM
0

Sign in to vote

User1558924997 posted

CREATE OR REPLACE PROCEDURE REGISTER
(
vFNAME IN VARCHAR2
, vLNAME IN VARCHAR2
, vEMAIL IN VARCHAR2
, vPASSWORD IN VARCHAR2
, vGENDER IN VARCHAR2
, vDOB IN VARCHAR2
, vMOBILE IN VARCHAR2
, vADDRESS IN VARCHAR2
, vreturn VARCHAR2 OUT
) AS
BEGIN
DECLARE cnt INT;
SELECT COUNT(*) INTO cnt FROM USERDETAILS WHERE fname=vfname AND lname=vlname AND EMAIL=vEMAIL AND PASSWORD=vPASSWORD AND GENDER=vGENDER AND DOB=vDOB AND MOBILE=vMOBILE AND ADDRESS=vADDRESS;

IF (cnt>0) THEN
BEGIN
vreturn := 'Records Already Present' ;
END

ELSE
BEGIN
INSERT INTO UserDetails (Fname,Lname,Email,PASSWORD,Gender,Dob,Mobile,Address)
VALUES(vfname,vlname,vEMAIL,vPASSWORD,vGENDER,vDOB ,vMOBILE,vADDRESS));
vreturn := 'Records Inserted Successfully';
END IF;
END REGISTER;

Tuesday, June 17, 2014 5:50 AM
0

Sign in to vote

User1558924997 posted

If this still does not work then please create your schema in sql fiddle, we will correct the sp there

http://sqlfiddle.com

Tuesday, June 17, 2014 5:54 AM

User1558924997 posted

Sorry, this is PL sql you can not use if exists and SET, so updated SP;

CREATE OR REPLACE PROCEDURE REGISTER 
(
vFNAME IN VARCHAR2 
, vLNAME IN VARCHAR2 
, vEMAIL IN VARCHAR2 
, vPASSWORD IN VARCHAR2 
, vGENDER IN VARCHAR2 
, vDOB IN VARCHAR2 
, vMOBILE IN VARCHAR2 
, vADDRESS IN VARCHAR2 
, vreturn OUT VARCHAR2 
) AS 

DECLARE cnt INT;
BEGIN 

SELECT COUNT(*) INTO cnt FROM USERDETAILS WHERE fname=vfname AND lname=vlname AND EMAIL=vEMAIL AND PASSWORD=vPASSWORD AND GENDER=vGENDER AND DOB=vDOB AND MOBILE=vMOBILE AND ADDRESS=vADDRESS;

IF (cnt>0) THEN
BEGIN 
vreturn := 'Records Already Present' ; 
END ;

ELSE 
BEGIN 
INSERT INTO UserDetails (Fname,Lname,Email,PASSWORD,Gender,Dob,Mobile,Address) 
VALUES(vfname,vlname,vEMAIL,vPASSWORD,vGENDER,vDOB ,vMOBILE,vADDRESS); 
vreturn := 'Records Inserted Successfully'; 
END;
END IF;
END REGISTER;

Try above sp

Tuesday, June 17, 2014 6:00 AM

0

Sign in to vote

User364480375 posted

hey akfkmupiwu i am using oracle sql developer..getting error

Error(11,20): PLS-00103: Encountered the symbol "OUT" when expecting one of the following:     := . ) , @ % default character The symbol ":=" was substituted for "OUT" to continue.
Error(15,1): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     begin function package pragma procedure subtype type use    <an identifier> <a double-quoted delimited-identifier> form    current cursor The symbol "begin" was substituted for "SELECT" to continue.
Error(22,1): PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:     ; <an identifier> <a double-quoted delimited-identifier> The symbol ";" was substituted for "ELSE" to continue.
Error(25,70): PLS-00103: Encountered the symbol ")" when expecting one of the following:     , ; return returning
Error(27,5): PLS-00103: Encountered the symbol "IF" when expecting one of the following:     ; <an identifier> <a double-quoted delimited-identifier>    delete exists prior <a single-quoted SQL string>

Tuesday, June 17, 2014 6:08 AM
0

Sign in to vote

User1558924997 posted

Try above corrected sp;

Begin END; corrected

Decelaration moved just below as,

OUT placed at proper place

insert statement was having )) one removed

Tuesday, June 17, 2014 6:28 AM
0

Sign in to vote

User364480375 posted

getting error:

Error(14,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language

Tuesday, June 17, 2014 6:30 AM

User1558924997 posted

We are basically wrong, refer how to create Create a PL/SQL Procedure http://docs.oracle.com/cd/E35137_01/appdev.32/e35117/tut_library.htm#CBACEAIJ

CREATE OR REPLACE
PROCEDURE list_a_rating(in_rating IN NUMBER) AS
  matching_title VARCHAR2(50);
  TYPE my_cursor IS REF CURSOR;
  the_cursor my_cursor;
BEGIN
  OPEN the_cursor
    FOR 'SELECT title 
           FROM books 
          WHERE rating = :in_rating'
    USING in_rating;
  DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
  LOOP
    FETCH the_cursor INTO matching_title;
    EXIT WHEN the_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(matching_title);
  END LOOP;
  CLOSE the_cursor;
END list_a_rating;

Tuesday, June 17, 2014 7:02 AM

0

Sign in to vote

User364480375 posted

what abt sp???

Tuesday, June 17, 2014 7:04 AM
0

Sign in to vote

User1558924997 posted

what abt sp???

what do you mean by sp; say this a procedure or a stored procedure, in SQL world both are same

Tuesday, June 17, 2014 7:07 AM
0

Sign in to vote

User364480375 posted

i am talking abt my thread.... my thread SP. kindly suggest

Tuesday, June 17, 2014 7:09 AM
0

Sign in to vote

User1558924997 posted

Do want me to rewrite your SP REGISTER ?

I think, it will be better you to give it a try to convert the SP as per guidelines from Oracle and if you do not succeed then please post converted sp. :)

Tuesday, June 17, 2014 7:16 AM
0

Sign in to vote

User364480375 posted

i think i have already tried ........thtas why i have posted this.

Tuesday, June 17, 2014 7:18 AM
0

Sign in to vote

User1558924997 posted

I suggest you to follow ; Create a PL/SQL Procedure http://docs.oracle.com/cd/E35137_01/appdev.32/e35117/tut_library.htm#CBACEAIJ as initially you were on wrong path, even we all were :)

Tuesday, June 17, 2014 7:20 AM
0

Sign in to vote

User-1716253493 posted

http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm

Tuesday, June 17, 2014 10:17 PM
0

Sign in to vote

User1558924997 posted

Hi oned_gk,

He is using Oracle SQL Developer, and here syntax is different for writing Procedures. Therefor i suggested him to follow SQL Dev URL.

Wednesday, June 18, 2014 1:00 AM

Asked by:

stored procedure in oracle sql developer

Question

All replies