Home » SQL & PL/SQL » SQL & PL/SQL » Create Procedure
Create Procedure [message #258115] Fri, 10 August 2007 05:31 Go to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Hi Friends,
I am new to Oracle.I wrote a Procedure like this. But it is giving me an error
"Encountered the symbol "@" when expecting one of the following:
begin function package pragma procedure subtype type use"

My Procedure is

CREATE OR REPLACE PROCEDURE Addcustinfo
(
vcharName IN VARCHAR2(100),
intSex IN VARCHAR2(1),
intAge IN INTEGER,
intPpl IN INTEGER,
vcharAddress IN VARCHAR2(1000),
vcharBookingFromDate IN VARCHAR2(10),
vcharBookingToDate IN VARCHAR2(10),
intRoomId IN INTEGER
)
AS
BEGIN
DECLARE @CustId INTEGER
BEGIN TRANSACTION
INSERT INTO tblCustinfo VALUES(@vcharName,@intSex,@intAge,@intPpl,@vcharAddress)
--SET @CustId=@@IDENTITY
INSERT INTO tblBookings VALUES(@intRoomId,@vcharBookingFromDate,@vcharBookingToDate,@CustId)
--IF (@@ERROR <> 0)
-- BEGIN
-- RAISE ('Transaction failed',16,-1)
-- ROLLBACK TRANSACTION
--END
COMMIT TRANSACTION
END Addcustinfo;

Please correct me as this is very urgent for me.

Re: Create Procedure [message #258118 is a reply to message #258115] Fri, 10 August 2007 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'd be s SQL Server developer primarily?

Variable names (and indeed all names) in oracle by default use a-z, 0-9, _ and a couple of other characters I can't be bothered looking up.
Starting with a '@' is not allowed.
Re: Create Procedure [message #258120 is a reply to message #258115] Fri, 10 August 2007 05:45 Go to previous messageGo to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Hi,
Yes. I will be using SQL Server most of the time. I have removed the @.

When using this
CREATE OR REPLACE PROCEDURE Addcustinfo
(
vcharName IN VARCHAR2(100),
intSex IN VARCHAR2(1),
intAge IN INTEGER,
intPpl IN INTEGER,
vcharAddress IN VARCHAR2(1000),
vcharBookingFromDate IN VARCHAR2(10),
vcharBookingToDate IN VARCHAR2(10),
intRoomId IN INTEGER
)
IS
DECLARE CustId INTEGER
BEGIN
BEGIN TRANSACTION
INSERT INTO tblCustinfo VALUES(@vcharName,@intSex,@intAge,@intPpl,@vcharAddress)
--SET @CustId=@@IDENTITY
INSERT INTO tblBookings VALUES(@intRoomId,@vcharBookingFromDate,@vcharBookingToDate,@CustId)
--IF (@@ERROR <> 0)
-- BEGIN
-- RAISE ('Transaction failed',16,-1)
-- ROLLBACK TRANSACTION
--END
COMMIT TRANSACTION
END Addcustinfo;

I am getting error with Declare. If i remove Declare I am getting error with BEGIN.

One more thing how can I use @@IDENTITY Here??

Thanks. (Sorry for the stupid questions but I am unable to manage.)
Re: Create Procedure [message #258127 is a reply to message #258120] Fri, 10 August 2007 06:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, in Pl/Sql, you only need a DECLARE statement at the start of a Pl/sql block if it is anonymous. Stored procedures don't need the DECLARE statement.

You will be getting the error with the BEGIN because you have the 'BEGIN TRANSACTION' phrase in your code as well. Oracle is a little more sophisticated than SQL Server and doesn't need you to tell it when a transaction starts. So, loose that line.

You'll need to add trailing ';' to each command.

Loose the TRANSACTION from the Commit statement.

As to the @@IDENTITY:

Doing some quick reading up on what it is, to replace it you will need to create a database object called a Sequence. Once you've got a sequence (called, say CUST_ID_SEQ) then you can replace the CustId in the insert statement with "CUST_ID_SEQ.nextval"
Re: Create Procedure [message #258138 is a reply to message #258127] Fri, 10 August 2007 06:34 Go to previous messageGo to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Really Many Thanks for your help.
I have corrected my SP like this.

CREATE OR REPLACE PROCEDURE Addcustinfo
(
vcharName IN VARCHAR2(100),
intSex IN VARCHAR2(1),
intAge IN INTEGER,
intPpl IN INTEGER,
vcharAddress IN VARCHAR2(1000),
vcharBookingFromDate IN VARCHAR2(10),
vcharBookingToDate IN VARCHAR2(10),
intRoomId IN INTEGER
)
IS
CustId INTEGER;
BEGIN
CustId := SQ_Identity.nextval;
INSERT INTO tblCustinfo VALUES(CustId,vcharName,intSex,intAge,intPpl,vcharAddress);
INSERT INTO tblBookings VALUES(intRoomId,vcharBookingFromDate,vcharBookingToDate,CustId);
end;

Now it is giving me that an error with 'end-of-file'.

ERROR at line 18: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
Re: Create Procedure [message #258140 is a reply to message #258138] Fri, 10 August 2007 06:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Add a '/' to the end of the file.
Re: Create Procedure [message #258142 is a reply to message #258138] Fri, 10 August 2007 06:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure you got that error. when i try to execute your procedure even though i don't have the tables in my schema i am getting the following error
SQL> CREATE OR REPLACE PROCEDURE Addcustinfo
  2  (
  3  vcharName IN VARCHAR2(100),
  4  intSex IN VARCHAR2(1),
  5  intAge IN INTEGER,
  6  intPpl IN INTEGER,
  7  vcharAddress IN VARCHAR2(1000),
  8  vcharBookingFromDate IN VARCHAR2(10),
  9  vcharBookingToDate IN VARCHAR2(10),
 10  intRoomId IN INTEGER
 11  )
 12  IS
 13  CustId INTEGER;
 14  BEGIN
 15  CustId := SQ_Identity.nextval;
 16  INSERT INTO tblCustinfo VALUES(CustId,vcharName,intSex,intAge,intPpl,vcharAddress);
 17  INSERT INTO tblBookings VALUES(intRoomId,vcharBookingFromDate,vcharBookingToDate,CustId);
 18  end;
 19  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE ADDCUSTINFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/22     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

4/19     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

7/25     PLS-00103: Encountered the symbol "(" when expecting one of the

LINE/COL ERROR
-------- -----------------------------------------------------------------
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

8/33     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

9/31     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.


It is because you won't mention the size of the variable in your formal parameters while creating a procedure. Instead you just mention only the datatype.

Something like this
  1  CREATE OR REPLACE PROCEDURE Addcustinfo
  2  (
  3  vcharName IN VARCHAR2,
  4  intSex IN VARCHAR2,
  5  intAge IN INTEGER,
  6  intPpl IN INTEGER,
  7  vcharAddress IN VARCHAR2,
  8  vcharBookingFromDate IN VARCHAR2,
  9  vcharBookingToDate IN VARCHAR2,
 10  intRoomId IN INTEGER
 11  )
 12  IS
 13  CustId INTEGER;
 14  BEGIN
 15  NULL;
 16* end;
SQL> /

Procedure created.


Ofcourse i have removed the insert statements because i don't have it my schema.
HTH

Regards

Rajaram

[Updated on: Fri, 10 August 2007 06:57]

Report message to a moderator

Re: Create Procedure [message #258144 is a reply to message #258140] Fri, 10 August 2007 06:57 Go to previous messageGo to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
hi
I have added '/'. But I am getting the same error.
Re: Create Procedure [message #258146 is a reply to message #258144] Fri, 10 August 2007 07:04 Go to previous messageGo to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Hi,
Thanks for spending your time.

Can you please tell me whether I can use Sequences in the Procedure or not?Because when I wrote my proc like this

CREATE OR REPLACE PROCEDURE Addcustinfo
(
vcharName IN VARCHAR2,
intSex IN VARCHAR2,
intAge IN INTEGER,
intPpl IN INTEGER,
vcharAddress IN VARCHAR2,
vcharBookingFromDate IN VARCHAR2,
vcharBookingToDate IN VARCHAR2,
intRoomId IN INTEGER
)
IS
CustId INTEGER;
BEGIN
CustId := SQ_Identity.nextval;
INSERT INTO tblCustinfo VALUES(CustId,vcharName,intSex,intAge,intPpl,vcharAddress);
INSERT INTO tblBookings VALUES(intRoomId,vcharBookingFromDate,vcharBookingToDate,CustId);
end;
/

I am getting error
Table,View Or Sequence reference 'SQ_IDENTITY.NEXTVAL' not allowed in this context.

If Sequence cannot be used, how can I get this work Done? Because I have to insert auto number in the both tables.
Re: Create Procedure [message #258148 is a reply to message #258146] Fri, 10 August 2007 07:09 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
CustId := SQ_Identity.nextval;
INSERT INTO tblCustinfo VALUES(CustId,vcharName,intSex,intAge,intPpl,vcharAddress);

to


INSERT INTO tblCustinfo VALUES(SQ_Identity.nextval,vcharName,intSex,intAge,intPpl,vcharAddress);


hth
Re: Create Procedure [message #258149 is a reply to message #258146] Fri, 10 August 2007 07:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Yes it is possible.
select SQ_Identity.nextval into custid from dual;

Also spend some time in reading the sql reference and pl/sql reference.

Thanks

Rajaram
Re: Create Procedure [message #258151 is a reply to message #258146] Fri, 10 August 2007 07:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you had used the sequence in the Select statement as I said, you wouldn't have this problem - your initial example only had the Cust_Id in one table, but it seems to have moved into both of them now.

Remove this line:
CustId := SQ_Identity.nextval;

Either:

1) Add this extra SELECT in where the deleted line was, to explicitly populate the Cust_id
SELECT SQ_Identity.nextval
INTO CustId
FROM dual;
or

Modify the first insert statment to use the sequence, and get it to return the value it uses like this (this assumes the column hat CustId goes into is called CUSTOMER_ID):
INSERT INTO tblCustinfo 
VALUES(SQ_Identity.nextval,vcharName,intSex,intAge,intPpl,vcharAddress)
RETURNING customer_id INTO CustId;
Re: Create Procedure [message #258166 is a reply to message #258151] Fri, 10 August 2007 07:32 Go to previous message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Hi,
I got it.
Many Thanks for all your help. Thank you so much....


Thanks and Regards,
Santu.
Previous Topic: SELECT within DECODE
Next Topic: Temporary tables
Goto Forum:
  


Current Time: Sat Dec 10 10:35:35 CST 2016

Total time taken to generate the page: 0.05495 seconds