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: insert in function not possible?

Re: insert in function not possible?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 15 Dec 1998 13:33:23 GMT
Message-ID: <367d64a9.70583603@192.86.155.100>


A copy of this was sent to rosinowski_at_gmx.de (Jan Rosinowski) (if that email address didn't require changing) On Tue, 15 Dec 1998 12:34:10 GMT, you wrote:

>in sqlaw it's common practice to use functions like
>
>create function f_newfoo(bar varchar)
>as
>begin
> insert into foo(b) values (bar);
> return @@identity;
>end
>
>try this in oarcle gives an ora-6571 ?!
>
>what's wrong? why can't i modify the database in a function? using a
>stored proc seems to work but actually it's crap to use a procedure
>with an out-parameter to simulate a function.
>
>how do i retrieve the current sequencevalue correctly?
>
>
>
>ciao, jan

You cannot modify the database in a function you call from a SQL statement such as select, insert, update, delete. You must be trying to run the function from a 'select f_newfoo('x') from dual;'.

Instead, if you are in sqlplus just:

SQL> set serveroutput on
SQL> exec dbms_output.put_line( f_newfoo( 'x' ) );

(set serveroutput on will let you see the return value, dbms_output just prints it)

In pro*c it would look like:

   exec sql execute
   begin

       :my_host_variable := f_newfoo( 'x' );    end;
   end-exec;

and so on...  

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 Tue Dec 15 1998 - 07:33:23 CST

Original text of this message

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