dynamic plsql

[aidystew]

Hi,

I'm using oracle 7.3.4.5 (plsql 2.3.4.5.0) and am trying to do something akin to dynamic sql but with plsql. What I'm trying to achieve is to create a function that returns the values stored in a package variable by passing in the name of the table and column and returning the contents of the table and column.

For example:

policy_row stores all the variables in our policy table available to our package in question, but not to the outside.

my function (return_init_var) - has two incoming parameters - table_name and field_name, so a call may look like:

trfutil.return_init_var(table_name => 'policy',
field_name => 'trans_id');

My function would want to return the contents of policy_row.trans_id. Does anyone know how to do this? I can't use dbms_sql as the necessary statement is plsql not sql. I was wondering if I could use any other dbms utility to do the job.

Help and thanks!

 

[SantaMufasa]

I'm trying to achieve is to create a function that returns the values stored in a package variable by passing in the name of the table and column and returning the contents of the table and column.

A function returns only one discrete value. Do you not want to pass into your function some argument that limits the RETURN value to a row-column tuple?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[aidystew]

Sorry yes. The table_name and field_name will only reference a single value that will be returned.

Earlier in our process a whole bunch of variables are initialised and stored in memory in the form of rowtype variables that are available to the whole transaction - but only from within the package.

So the return variable would be referenced as table_name||'_row.'||field_name

So in the example above: policy_row.trans_id, which is a discrete value.

But how can I get from a variable that stores another variable to evaluate the contents of the policy_row.trans_id?

I hope I'm making sense.