Re: Functions that Insert data and return value

If you run the function from SQLplus like...

SQL> declare
SQL>  v_retval numner;
SQL> begin
SQL>  v_retval := Process(1);
SQL> End'

That should be ok. However, if you were running it from within a SQL statement, you have to tell oracle about what the function is going to do, ie the functions Purity.

If any part of your functions updates the database, inserts into the database, writes to packaged variables etc you need to declare it in your procedure or package spec.

If you look at documentation for PRAGMAS and in particular PRAGMA RESTRICT REFERENCES, it should tell you all you need to know about running functions within SQL statements.

The different pragmas are WNDS, WNPS, RNDS, RDPS (Write No Database State, Write No Package Stare, Read no.....)

Mark.

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!