locked
stored procedure in oracle sql developer RRS feed

  • 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

  • User364480375 posted

    kindly suggest.

    Tuesday, June 17, 2014 1:57 AM
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • User364480375 posted

    what abt sp???

    Tuesday, June 17, 2014 7:04 AM
  • 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
  • User364480375 posted

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

    Tuesday, June 17, 2014 7:09 AM
  • 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
  • User364480375 posted

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

    Tuesday, June 17, 2014 7:18 AM
  • 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
  • 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