Re: A variable name is in an string( other variable)
Date: 1997/11/13
Message-ID: <347254f6.18337417_at_newshost>#1/1
On 13 Nov 1997 16:39:30 GMT, support_at_placid.com wrote:
>I have the name of a variable in another string variable, and I would like to
>access its value in a stored procedure, is it possible?
>
>Thank you.
As long as the variable is 'global' -- yes. So, if you have a variable in a package specification, or a function you can call, you can dynamically access its value. If the variable is a stack or local variable -- no. The following package implements and then demo's how to do this....
create or replace package value_of
as
theValue varchar2(2000);
function something( p_fieldname in varchar2 ) return varchar2;
end;
/
create or replace package body value_of
as
function something( p_fieldname in varchar2 ) return varchar2 is
l_theCursor integer;
l_columnValue varchar2(2000);
l_status integer;
begin
l_theCursor := dbms_sql.open_cursor;
dbms_sql.parse( l_theCursor,
'begin value_of.theValue := ' || p_fieldname || ';end;',
dbms_sql.native );
l_status := dbms_sql.execute(l_theCursor);
dbms_sql.close_cursor(l_theCursor);
return theValue;
end something;
end value_of;
/
REM -------- demo below ---------------------
create or replace package demo
as
x number default 5;
function foo return varchar2;
end;
/
create or replace package body demo
as
function foo return varchar2
is
begin
return 'hello';
end;
end demo;
/
set serveroutput on
create or replace procedure run_demo
as
l_theVar varchar2(255);
begin
demo.x := 55;
l_theVar := 'demo.x';
dbms_output.put_line( 'the value of ' || l_theVar || ' is ' ||
value_of.something( l_theVar ) );
l_theVar := 'demo.foo';
dbms_output.put_line( 'the value of ' || l_theVar || ' is ' ||
value_of.something( l_theVar ) );
end;
/
exec run_demo
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 13 1997 - 00:00:00 CET