Home » SQL & PL/SQL » SQL & PL/SQL » Extracting a variable value
Extracting a variable value [message #214455] Tue, 16 January 2007 08:56 Go to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I wonder if anyone could point me in the right direction with this query? I'm not sure if I can actually do this?

I have a variable that exists in a package e.g.

retentioncell := 1;


In a table I have a field that contains the name of the variable. Is it possible for me to get the value of the variable whose name is stored in the table.

I have tried using dynamic sql but that just pulls the string name back
declare

cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;

local_var varchar2(100);
var_1 varchar2(100) := 'retentioncell';
retentioncell number;

begin

retentioncell := 1;

DBMS_SQL.PARSE (cur, 'BEGIN :tmp := :tmp2; END;' ,2);
DBMS_SQL.BIND_VARIABLE (cur, 'tmp', '                 ');
DBMS_SQL.BIND_VARIABLE (cur, 'tmp2', var_1);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.VARIABLE_VALUE (cur, 'tmp', local_var);

dbms_output.put_line(local_var);

end;


My dbms_output shows retentioncell - reasonably enough, but I want to get the value 1.
So I really need to do a kind of double dynamic sql where I get the name and then the value. Can I do this? We have recently upgraded from 7.3 to 9i so there may well be new functionality that I'm not aware of?

I appreciate it's a strange request but I wanted to table drive some logic checking rather than having to hard code package values every time Marketing change their mind.


Re: Extracting a variable value [message #214461 is a reply to message #214455] Tue, 16 January 2007 09:06 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Technically there is not any problem:

SQL> create or replace package pkg_t
  2  is
  3    public_var number := 10;
  4  end;
  5  /
 
Package created.
 
SQL> declare
  2   varname varchar2(30) := 'public_var';
  3   value number;
  4  begin
  5   execute immediate 'begin :1 := pkg_t.' || varname || '; end;' using out value;
  6   dbms_output.put_line(value);
  7  end;
  8  /
10
 
PL/SQL procedure successfully completed.

But the purpose of this design is quite unclear.

Rgds.
Re: Extracting a variable value [message #214464 is a reply to message #214455] Tue, 16 January 2007 09:37 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Many thanks for that, I shall go away and read up on execute immediate.

The purpose is unclear to me too and I'm writing it Laughing . Seriously though I'm exploring solutions to a problem with maintaining a couple of packages. Package_1 obtains a lot of data, and passes a great deal of it to Package_2. Whilst it's initial incarnation involved a few parameters it's grown considerably and I'm trying to come up with a few solutions that can make it more dynamic.

This particular thought involved storing the variable names in a table so that they could be changed as required without having to alter either package. So I thought I'd give it a go but I couldn't work out a way to do it.

My main issue is that Package_2 does logic based on the values in those variables, but I don't know which variables will be required until runtime (through another table lookup that gets pseudo logic and uses it to create proper logic). At the moment we just pass in everything that might be needed, which is fine until Marketing change their mind again Mad

Thanks again for you help.
Previous Topic: Calling two variables in a script?
Next Topic: need urgent help !!!
Goto Forum:
  


Current Time: Tue Dec 06 12:14:02 CST 2016

Total time taken to generate the page: 0.15762 seconds