Re: Oracle function or procedure

From: Mark Gumbs <mgumbs--AT--hotmail.com>
Date: Fri, 14 May 1999 08:58:26 +0100
Message-ID: <373bd572.0@145.227.194.253>

It worked fine with me. I'm using Oracle 8. I rewrote the insert part of the proc slightly.

SQL> create table job
  2 (job_id number,
  3 TYPE VARCHAR2(1),
  4 DISK VARCHAR2(1),
  5 NAME VARCHAR2(1),
  6 DATE1 VARCHAR2(1),
  7 CLIENT VARCHAR2(1),
  8 TEST VARCHAR2(1),
  9 TEST_S VARCHAR2(1),
 10 STATE VARCHAR2(1),
 11 CREATOR VARCHAR2(1)); Table created.

SQL> CREATE OR REPLACE PROCEDURE ADD_JOB(

  2   TYPE IN VARCHAR2,
  3   DISK IN VARCHAR2,
  4   NAME IN VARCHAR2,
  5   DATE IN VARCHAR2,

  6 CLIENT IN VARCHAR2,
  7 TEST IN VARCHAR2,
  8 TEST_S IN VARCHAR2,
  9 STATE IN VARCHAR2,
 10 CREATOR IN VARCHAR2)
 11 as
 12 BEGIN
 13 insert into JOB values
 14 (SEQ.NEXTVAL, TYPE, DISK, NAME, DATE, CLIENT, TEST, TEST_S, STATE, CREATOR);
 15 END;
 16 /

Procedure created.

SQL> If the procedure was part of a package and this procedure was being called from the SQL prompt, you may have to put in a pragma restrict_references line in the procedure declaration. Looking at your code, this wasn't the case.

HTH, Mark

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 - 02:58:26 CDT