RE: Function

From: <Divya_pb/VGIL_at_vguard.satyam.net.in>
Date: Fri, 26 Oct 2001 22:51:58 -0700
Message-ID: <F001.003B6532.20011026223519@fatcity.com>

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)"       |
|        |          <RShankar1_at_CHN.COGN|
|        |          IZANT.COM>         |
|        |                             |
|        |          10/24/01 01:35 PM  |
|        |          Please respond to  |
|        |          ORACLE-L           |
|        |                             |


|--------+----------------------------->

  >--------------------------------------------------------|
  |                                                        |
  |       To:     Multiple recipients of list ORACLE-L     |
  |       <ORACLE-L_at_fatcity.com>                           |
  |       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: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). (See attached file: InterScan_Disclaimer.txt) Text - character set unknown

Received on Sat Oct 27 2001 - 00:51:58 CDT