Asked by:
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
-
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;
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 charactertry 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 || multisetTuesday, 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
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.
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 -
User-1716253493 posted
http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm
Tuesday, June 17, 2014 10:17 PM -
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