Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle function or procedure

Re: Oracle function or procedure

From: Kenny Gump <kgump_at_mylanlabs.com>
Date: Fri, 14 May 1999 07:55:06 -0400
Message-ID: <373c0ef0.0@news.mountain.net>


CREATE OR REPLACE PROCEDURE ADD_JOB(

TYPE IN VARCHAR2,
DISK IN VARCHAR2,
NAME IN VARCHAR2,
DATE IN VARCHAR2,

CLIENT IN VARCHAR2,
TEST IN VARCHAR2,
TEST_S IN VARCHAR2,
STATE IN VARCHAR2,
CREATOR IN VARCHAR2
job_id out number) /* You didn't have job_id declared. This will return it to the calling routine if you need it */ as
BEGIN
select SEQ.NEXTVAL into JOB_ID from dual; insert into JOB values
(JOB_ID, TYPE, DISK, NAME, DATE, CLIENT, TEST, TEST_S, STATE, CREATOR); END;
/

Kenny Gump
OCP 7.3 DBA svein wrote in message <373BC8F2.1BA8407B_at_petrodata.no>...
>Hello!
>
>I tried to make a function insert a new record in one of my
>tables and return the NEXTVAL value of the sequence on
>the index of the table.
>The server responded "Function does not guarantee not to
>update the database".
>Does this mean I have to use a procedure to insert new
>records? I tried with a procedure:
>
>CREATE OR REPLACE PROCEDURE ADD_JOB(
> TYPE IN VARCHAR2,
> DISK IN VARCHAR2,
> NAME IN VARCHAR2,
> DATE IN VARCHAR2,
> CLIENT IN VARCHAR2,
> TEST IN VARCHAR2,
> TEST_S IN VARCHAR2,
> STATE IN VARCHAR2,
> CREATOR IN VARCHAR2)
>as
>BEGIN
>select SEQ.NEXTVAL into JOB_ID from dual;
>insert into JOB values
>(JOB_ID, TYPE, DISK, NAME, DATE, CLIENT, TEST, TEST_S, STATE, CREATOR);
>END;
>/
>
>But the server responds
>"Procedure created with compilation errors", and I can't see
>where I do my mistakes... How can I return the true
>NEXTVAL value if I must use a procedure to insert records?
>Returning CURRVAL is not enough...
>
>I'd be greatful if someone could help me
>
>Regards,
>Svein
Received on Fri May 14 1999 - 06:55:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US