Re: INSERT in PL/SQL function

This is a multi-part message in MIME format.

--------------E226AF85374FEFA7E384BC74
Content-Type: text/plain; charset=iso-8859-1
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Content-Transfer-Encoding: 8bit

Susanne Heymann wrote:

> Hi all,
>
> I'm trying to find a way to insert or update table data within a
> function.

That's not a problem. Functions are allowed to do DML.

> I've tried cheating Oracle by encapsulating the insert statement in a
> procedure or hiding it behind dbms_sql but Oracle still tells me that
> the function violates its pragma.
>
> How can I work around it?

I'll tell you the same thing that I always tell the programmers I work with and that they so often disregard....

"Always read your error message - it is there to help you!!!!"

The key phrase in your description is "the function violates its pragma".

So did you look up "pragma" in the manual?

You must have a pragma in the package that says that the function is not allowed to modify the database. This is necessary in some cases. For example, a query can not modify the database so any function called in a query must have a pragma.

If your function must be called in a query then you can't modify the database. Rethink your application.

If your function does not get called from a query then why does it have that pragma? Find out. Who put it there? Call him up and ask him.

And then get rid of it unless you need it.

If you need it, then once again, rethink your application - you can't do an insert in a function that is required not to modify your data.

Good luck
Mike

> --------------------------------------------------------------------
> Susanne Heymann phone: (+49) (0)221 3091 251
> metronet GmbH fax: (+49) (0)221 3091 298
> Bonner Str. 172-176 e-mail: susanne_at_metronet.de
> D-50968 Köln-Bayenthal www: http://www.metronet.de/~susanne
> --------------------------------------------------------------------
> New address and phone! -- Susanne
> --------------------------------------------------------------------

--
-------------------------------------------------------------------------------

Building the future of business to business electronic commerce...
-------------------------------------------------------------------------------

ASM eTrade - Software from the Asian Sources Media Group
-------------------------------------------------------------------------------

24/F Vita Tower, Block B                  Tel: +852 2814 5678
29 Wong Chuk Hang Road                 Direct: +852 2814 5671
Aberdeen, Hong Kong                       Fax: +852 2311 3893
-------------------------------------------------------------------------------



--------------E226AF85374FEFA7E384BC74
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Michael Friedman
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Michael Friedman
n:              Friedman;Michael
org:            Asian Sources Media Group (eTrade)
email;internet: mfriedma_at_asiansources.com
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
end:            vcard


--------------E226AF85374FEFA7E384BC74--