RE: Urgent --> Calling function from a select statement in which

Hello, Thanks very much for your responses unfortunately I am still up against a wall with this one. It looks like what I am trying to do in version 7 simply can not be done.

<<You need to use pragma RESTRICT_REFERENCES(function_name, WNDS) in your package. It is not required for stand alone function.>>

I looked up the definition of WNDS and definition is as follows:

<<tells the compiler that the function contains no INSERT, UPDATE or DELETE statements exist, and no dynamic SQL>>

so based on that I will not be able to use this option because my code does contain dynamic SQL. The only purpose of my function is a wrapper so that I can call the underlying stored procedure form a select statement.

Below you will find an example of my package that I created a long with the error message that gets generated once I use the WNDS option:

CREATE OR REPLACE PACKAGE packagename

AS

FUNCTION myfunct

RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (funct, WNDS);

END packagename;

Error Message:

Errors for PACKAGE PACKAGE_name:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/1      PLS-00452: Subprogram 'package_name' violates its
         associated pragma



==================================================

If anyone has any other ideas please throw them my way.

Thanks!

Derek

-----Original Message-----
From: Alex Hillman [mailto:alex_hillman_at_physia.com] Sent: Saturday, June 03, 2000 5:54 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Urgent --> Calling function from a select statement in which

You need to use pragma RESTRICT_REFERENCES(function_name, WNDS) in your package. It is not required for stand alone function.

Alex Hillman

-----Original Message-----
From: Wasmund, Derek [ mailto:DWasmund_at_EFTIA.com <mailto:DWasmund_at_EFTIA.com> ]
Sent: Saturday, June 03, 2000 3:37 PM
To: Multiple recipients of list ORACLE-L Subject: Urgent --> Calling function from a select statement in which the

Hello, I am trying to call a function from within a select statement and I am receiving the message "Function function_name does not guarantee not to update database". The function contains dynamic sql, however there is no update code. This will work under version 8 (8i) but does not work under version 7(7.34). Unfortunately I need to rely on executing the function from

a select statement and of course is of an extermely urgent nature. Does anyone have any ideas?

Thanks!

Derek

-- 
Author: Wasmund, Derek 
  INET: DWasmund_at_EFTIA.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may