Home » SQL & PL/SQL » SQL & PL/SQL » using variable inside a stored proc
using variable inside a stored proc [message #203613] Wed, 15 November 2006 17:23 Go to next message
smalavia
Messages: 1
Registered: November 2006
Junior Member
Goodday people,

I want to use the variable vCount in my stored proc below.
I cannot seem to get the following code working.
Will anyone pls help?

Thanks
SM

create or replace procedure log(
PUTTIMESTAMP VARCHAR,
LOGLEVEL VARCHAR,
LOGDESCR VARCHAR,
EVENTDESCR VARCHAR,
MSGID VARCHAR,
MSGTYPE VARCHAR,
SOURCEID VARCHAR,
DESTINATIONID VARCHAR,
USERNAME VARCHAR,
TIMETOLIVE VARCHAR,
PAYLOAD VARCHAR,
NAME1 VARCHAR,
VALUE1 VARCHAR,
NAME2 VARCHAR,
VALUE2 VARCHAR,
NAME3 VARCHAR,
VALUE3 VARCHAR,
NAME4 VARCHAR,
VALUE4 VARCHAR,
NAME5 VARCHAR,
VALUE5 VARCHAR,
NAME6 VARCHAR,
VALUE6 VARCHAR,
NAME7 VARCHAR,
VALUE7 VARCHAR,
NAME8 VARCHAR,
VALUE8 VARCHAR
) IS
vCount NUMBER NOT NULL DEFAULT 0;
begin
vCount = logid_seq.nextval; <--- Is this correct?

insert into clogger values(
concat(TO_CHAR(CURRENT_DATE, 'DDMM'),logid_seq.nextval),
to_date(PUTTIMESTAMP,'YYYYMMDDHH24MISS'),
LOGLEVEL,
LOGDESCR,
EVENTDESCR,
MSGID,
MSGTYPE,
SOURCEID,
DESTINATIONID,
USERNAME ,
to_date(TIMETOLIVE,'YYYYMMDDHH24MISS'),
NAME1,
VALUE1,
NAME2,
VALUE2,
NAME3,
VALUE3,
NAME4,
VALUE4,
NAME5,
VALUE5,
NAME6,
VALUE6,
NAME7,
VALUE7,
NAME8,
VALUE8
);


insert into cpayload values(concat(TO_CHAR(CURRENT_DATE,'DDMM'),logid_seq.currval), PAYLOAD);
end;
Re: using variable inside a stored proc [message #203644 is a reply to message #203613] Wed, 15 November 2006 23:01 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

vCount = logid_seq.nextval; <--- Is this correct?

'=' is a relational operator which compares equality.

so it is not correct for your context

use := for assignment

or you can write a select statement

select logid_seq.nextval into vCount from dual;
Re: using variable inside a stored proc [message #203677 is a reply to message #203613] Thu, 16 November 2006 00:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot assign a sequence value, you have to use select-into.
Further, your vCount is not used after the assignment, so what exactly do you want to do with it?
Maybe you can use the returning into clause of the insert command.

Also, don't generate a primary key as concatenation like you do. If you are interested in CURRENT_DATE (which is not declared, btw), store it in a separate column.
Previous Topic: Query - Urgent (Merged)
Next Topic: Issue with UPDATE
Goto Forum:
  


Current Time: Thu Dec 08 08:43:24 CST 2016

Total time taken to generate the page: 0.09495 seconds