Re: DBMS_SQL
Date: 2000/05/30
Message-ID: <8h16ji$q5s$1_at_nnrp1.deja.com>#1/1
In article <8h10g5$l93$1_at_nnrp1.deja.com>,
jeffremj_at_my-deja.com wrote:
> Hi guys and girls,
>
> Can you run functions via DBMS_SQL. I am having problems with this:
>
> FUNCTION SUBROUTINE1
> (v_subroutine IN VARCHAR2, v_in IN VARCHAR2)
> RETURN VARCHAR2
> IS
> v_cursor_id INTEGER;
> v_dummy INTEGER;
> v_temp_string1 VARCHAR2(255);
> v_temp_string2 VARCHAR2(255);
> BEGIN
> v_cursor_id := DBMS_SQL.OPEN_CURSOR;
> v_temp_string1 := ':returned_data := ' || v_subroutine || '(' ||
> v_in || ')';';
> DBMS_SQL.PARSE(v_cursor_id, v_temp_string1, DBMS_SQL.NATIVE);
> DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':returned_data', 255);
> v_dummy := DBMS_SQL.EXECUTE(v_cursor_id);
> DBMS_SQL.VARIABLE_VALUE(v_cursor_id, ':returned_data',
> v_temp_string2);
> DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
> RETURN v_temp_string2;
> END SUBROUTINE1;
>
> A long shot, but .....
>
> TIA.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
You can do this. you can use a package variable OR bind variables with dbms_sql. Here is an example:
ops$tkyte_at_8i> create or replace package value_of
2 as
3 theValue varchar2(4000);
4
5 function something( p_fieldname in varchar2 ) return varchar2;
6 end;
7 /
Package created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body value_of
2 as
3
4 g_theCursor integer default dbms_sql.open_cursor;
5
6 function something( p_fieldname in varchar2 ) return varchar2
7 is
8 l_status integer;
9 begin
10 dbms_sql.parse( g_theCursor, 11 'begin value_of.theValue := ' || p_fieldname || ';end;', 12 dbms_sql.native ); 13 14 l_status := dbms_sql.execute(g_theCursor); 15 return theValue;
16 end something;
17
18 end value_of;
19 /
Package body created.
ops$tkyte_at_8i> ops$tkyte_at_8i> ops$tkyte_at_8i> ops$tkyte_at_8i> REM -------- demo below ---------------------
>
ops$tkyte_at_8i> create or replace package demo 2 as
3 x number default 5;
4
5 function foo return varchar2;
6 end;
7 /
Package created.
ops$tkyte_at_8i> create or replace package body demo
2 as
3
4 function foo return varchar2
5 is
6 begin
7 return 'hello';
8 end;
9
10 end demo;
11 /
Package body created.
ops$tkyte_at_8i> ops$tkyte_at_8i> ops$tkyte_at_8i> set serveroutput on ops$tkyte_at_8i> create or replace procedure run_demo 2 as 3 l_theVar varchar2(255);
4 begin
5
6 demo.x := 55;
7
8 l_theVar := 'ops$tkyte.demo.x'; 9
10 dbms_output.put_line( 'the value of ' || l_theVar || ' is ' || 11 value_of.something( l_theVar ) ); 12 13 l_theVar := 'demo.foo'; 14 15 dbms_output.put_line( 'the value of ' || l_theVar || ' is ' || 16 value_of.something( l_theVar ) );17 end;
18 /
Procedure created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> exec run_demo
the value of ops$tkyte.demo.x is 55
the value of demo.foo is hello
PL/SQL procedure successfully completed.
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Tue May 30 2000 - 00:00:00 CEST