Re: ORA-06571 from user defined function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 21 Jan 1999 01:54:47 GMT
Message-ID: <36a78849.2611595@192.86.155.100>

A copy of this was sent to Jim Poe <jpoe_at_fulcrumit.com> (if that email address didn't require changing) On Wed, 20 Jan 1999 14:04:24 -0800, you wrote:

>I wrote the following function:
>CREATE OR REPLACE function jbp_FUNCT1 ( TABLENAME VARCHAR2 )
>RETURN NUMBER IS
> vNewNumber NUMBER( 10 ) := 0;
> TName VARCHAR2( 50 );
>
> vcurs INTEGER;
> vretcd INTEGER;
> vstmt VARCHAR2(100);
>
>BEGIN
> vcurs :=dbms_sql.open_cursor;
>
> TName:=UPPER( TABLENAME );
>
> vstmt := 'select '||TName|| '_SEQ.NextVal into :NewNumber from dual';
>
>
>
> dbms_sql.parse(vcurs, vstmt, dbms_sql.v7);
> dbms_sql.bind_variable( vcurs, ':NewNumber', vNewNumber );
> vretcd := dbms_sql.execute(vcurs);
> dbms_sql.variable_value( vcurs, ':NewNumber', vNewNumber );
>
> Return vNewNumber;
>
> commit;
>
> dbms_sql.close_cursor( vcurs );
>END;
>
>
>I use it with the following sql:
> select jbp_funct1( 'PDCR' ) NewNumber from dual;
>
>i get the ORA-06571 error. 'Function jbp_funct1 does not guarantee not
>to update database.' The documentation for the error states:
>Action: If the referenced function is a packaged PL/SQL function:
>Recreate the PL/SQL function with the required
>pragma; be certain to include the "Write No Database State" (WNDS)
>argument in the argument list of the pragma.
>
>What is the 'right pragma' and why would I not want the database
>updated? I assume it is referring to updating the sequence.
>

You are apparently trying to call this function in a SELECT. Currently, in 8.0 and less, a function called from SQL cannot write to the database state. This function does for 2 reasons:

• dbms_sql can procedure inserts, update, delete, create etc. it has the ability to write to the database
• you are selecting a sequence.nextval -- that is writing to the database state. nextval increments and writes the new value into the database.

This function cannot be called from SQL in 8.0 and below (8i -- that will be a different story).

>Thanks
 

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 20 1999 - 19:54:47 CST