Hai All
We are using Oracle 8 . I tried out using DBMS_SQL.
declare
id number;
x varchar2(100);
y varchar2(40);
z number;
t number;
begin
select function_name into x from func;
id:=dbms_sql.open_cursor;
y:='select '||x||' from dual';
dbms_sql.parse(id,y,dbms_sql.NATIVE);
z:=dbms_sql.execute(id);
dbms_sql.close_cursor(id);
end;
The PL/SQL procedure was executed successfully.
But how do I display the output (i.e the return value of the function )
or store the output into a variable .
Thanks in advance
Dpb
|--------+----------------------------->
| | "Ramasubramanian, |
| | Shankar |
| | (Cognizant)" |
| | <[EMAIL PROTECTED]|
| | IZANT.COM> |
| | |
| | 10/24/01 01:35 PM |
| | Please respond to |
| | ORACLE-L |
| | |
|--------+----------------------------->
>--------------------------------------------------------|
| |
| To: Multiple recipients of list ORACLE-L |
| <[EMAIL PROTECTED]> |
| cc: (bcc: Divya pb/VGIL) |
| Subject: RE: Function |
>--------------------------------------------------------|
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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
(See attached file: InterScan_Disclaimer.txt)
Text - character set unknown
