RE: Function

From: Ramasubramanian, Shankar (Cognizant) <RShankar1_at_CHN.COGNIZANT.COM>
Date: Wed, 24 Oct 2001 00:06:49 -0700
Message-ID: <F001.003B2B03.20011024000521@fatcity.com>

Hi Divya,

you have to use dynamicsql for calling the function . First step select the function name into a variable say ls_fn_name from the table func .Then create a dynamic statement

'Select' || ls_fn_name || ' from dual ' and cpature the return value into another variable . If u are using oracle 8i , u can use execute immediate statement . for prior oracle versions use DBMS_SQL statement .

Regards,
Shankar

-----Original Message-----
[mailto:Divya_pb/VGIL_at_vguard.satyam.net.in] Sent: Wednesday, October 24, 2001 11:50 AM To: Multiple recipients of list ORACLE-L

Hi All

I have stored a user defined function as Varchar field in a table. How do I execute this function.

Here is the table where the function is stored.

SQL> select * from func;

FUNCTION_NAME

Calc_radius(5)

This procedure contains the following Code :

create function calc_radius(r in number) return number is begin
return 3.14*r*r;
end;

Executing this funtion at SQL Prompt give the output as

SQL> SELECT CALC_RADIUS(5) FROM DUAL; CALC_RADIUS(5)

78.5

I want to execute this function from a PL/SQL Block. I tried to store this function into a variable and then execute it. But it returns only the content of the field FUNCTION_NAME and not the value.

Can anyone suggest a solution for this problem ?

Regards
Dpb

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Divya_pb/VGIL_at_vguard.satyam.net.in

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 also send the HELP command for other information (like subscribing).

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful.

                Visit us at http://www.cognizant.com

Received on Wed Oct 24 2001 - 02:06:49 CDT