Re: Oracle function or procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 14 May 1999 11:53:36 GMT
Message-ID: <373c0cf7.2878138@192.86.155.100>

A copy of this was sent to svein <sveinf_at_petrodata.no> (if that email address didn't require changing) On Fri, 14 May 1999 08:55:46 +0200, you wrote:

>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".

that means you tried to "select the_function() from T". You cannot call PLSQL from SQL if the PLSQL writes (inserts) the database. You are writing the database -- you will not be able to call that function or procedure from a SQL select (or insert/update/delete) statement. In Oracle8i, release 8.1 -- you will be able to if you use autonomous transactions but thats another story.

>Does this mean I have to use a procedure to insert new
>records? I tried with a procedure:
>

No, you can use a function.

The error with your procedure below is you are selecting seq.nextval into JOB_ID but I do not see a declare for job_id anywhere. If you create a procedure or function that compiles with errors, you can issue:

SQL> show errors procedure add_job

to see what the errors where (or issue a select against user_errors, they are in there as well)

Your function could look like:

CREATE OR REPLACE function ADD_JOB(

 p_TYPE IN VARCHAR2,
 p_DISK IN VARCHAR2,
 p_NAME IN VARCHAR2,
 p_DATE IN VARCHAR2,
 p_CLIENT IN VARCHAR2,
 p_TEST IN VARCHAR2,
 p_TEST_S IN VARCHAR2,
 p_STATE IN VARCHAR2,
 p_CREATOR IN VARCHAR2) return number

as
  l_job_id number;
BEGIN
  select SEQ.NEXTVAL into l_JOB_ID from dual;   insert into JOB values
  (l_JOB_ID, p_TYPE, p_DISK, p_NAME, p_DATE, p_CLIENT, p_TEST, p_TEST_S,

             p_STATE, p_CREATOR);

  return l_job_id;
END;
/

You would execute this in sqlplus as follows:

SQL> set serveroutput on
SQL> exec dbms_output.put_line( add_job( 'blah', 'blah', ... ) )

it will print out the new id. In your application, you would call this as such:

   begin

      :my_host_variable := add_job( 'blah', 'blah', ... );    end;

I put a p_ on all of the input parameters. I did this because I bet the names of the columns in your table matched your names of your input parameters (eg, the table job has a column whose name is TYPE). This is bad and will get you in trouble. For example, the following procedure:

create procedure list_emps( ename in varchar2 ) is
begin

    for x in ( select * from emp where ename = ename ) loop

       ...
    end loop;
end;

always processes EVERY record in the emp table. To avoid confusion between column names and plsql variable names, i suggest you start parameters with a p_, local variables with a l_. That way there is no abiguity (unless you start database column names with p_/l_ of course :)

>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

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 14 1999 - 06:53:36 CDT