| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: insert in function not possible?
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
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
 |
 |