Hi Everybody,

Is posible to create dynamic variables in a PL/SQL procedure?

This is my problem, I have a table called TABLE_VARIABLE with this
values :

# VAR_NAME VAR_VALUE
------- --------------- ------------------------
1 SALARY 1000
2 DISCOUNT 300
3 NET (SALARY - DISCOUNT)


I want create a procedure where the variables names should be the same
name defined in the table TABLE_VARIABLE.
The user is able to create ne records and new variables names an the
names should be processed like a pl/sql variables in order to store
the result information in other table.

Sample :

Create or replace read_variables as
Begin
for reg in (select VAR_NAME, VAR_VALUE from TABLE_VARIABLE ) loop
<create variable defined in the field VAR_NAME in TABLE_VARIABLE> ;
<Assign value using the field VAR_VALUE in TABLE_VARIABLE> ;
End Loop ;
End ;

Is possible do that?


Mgmonzon

mgmonzon wrote:
No. This is not possible. (As in all other programming languages I
know.)
Why would you want to do such a thing? It doesn't make too much sense
too me :-)

best,
Martin

mgmonzon wrote:
Probably not exactly what you are looking for, but you could create an
anonymous PL/SQL block from these variables definitions and then
EXECUTE IMMEDIATE it. You can bind a nested table or an array to this
block to get the results back, like this:

declare
type array_t is table of number index by binary_integer;
outarray array_t;
begin
execute immediate
'declare
salary number := 1000;
discount number := 300;
net number := (salary - discount);
begin
utarray(1) := salary;
utarray(2) := discount;
utarray(3) := net;
end;' using out outarray;
end;

and then process the output as needed. A little problem here is that
the table doesn't tell you data types of the variables. PL/SQL is not
VB or JavaScript, all variables are typed... If you add data types to
the picture, writing the anonymous block builder is pretty trivial.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

I'm trying to create a application where the users can define his own
calculations.

Regards,

Mgmonzon

Martin T. wrote:
Well, a lot of applications today use built-in scripting of different
sorts, which allows end users to extend them the way they want without
the need to ask app developers for required functionality. I think OP
looks for something similar in PL/SQL. There's Dynamic SQL to the
rescue, though it will require a bit of architecting before coding...
What the OP has now is not sufficient as I noted in my other post.

And by the way, it's possible to build and execute code dynamically in
quite a few languages. Classic interpreted BASIC is one good example -
you can dynamically define subprograms and call them. JavaScript and
VBScript allow this, too. In fact, any interpreted language at least
potentially allows dynamic code generation and execution. PL/SQL is not
really an interpreted language per se, but still allows dynamic code
generation and execution via Dynamic SQL (DBMS_SQL and EXECUTE
IMMEDIATE.)

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Thank you guys for your replys.

I can solve my problem using bidimensional arrays like vladimir wrote
before.

Is not posible use external interpreter because the quantity of
calculation is to much and I would like use the database to do that, in
order to do the calculation faster.


Thanks ,

Miguel

mgmonzon wrote:
You can do what you want but my instinct is to lecture you on what
appears to be a really bad idea and question what business rule has
led to you think this is a something you want to implement.

That said look at utilizing the DBMS_DDL built-in package. You can
use (provided you have 10g and you seem to think version information
unimportant) the CREATE_WRAPPED package to write wrapped code
on-the-fly. Of course it can also be done with NDS, DBMS_SQL, or


.... Now back to the fact that I think this is a terribly misguided
idea and question why anyone thinks doing this makes sense ....

Can you explain the point of this design?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

mgmonzon wrote:
Then the names of variables are unimportant I would think. Nor
do I see where there is any reason for users not to share the
same pool of variables.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

I'm working in a payroll application where the user can implement any
rule of calculation using a formula definition page.
The actual application make the calculation using a program created in
VB and we want eliminate all the VB code in order to make the
application transportable.

Regards,

Mgmonzon

mgmonzon wrote:
Apparently the country you live in is one in which you are not
subject to laws such as Sarbanes-Oxley, HIPAA, FACTA, Basel II,
PIPEDA, etc.

In this country what you describe, except for a very small company
or a public organization would be a violation of the law.

That said ... there is still nothing in what you've written that
requires specifically named variables. You might want to reconsider
what you are doing but also look at the ANYDATA variable type. You
can find a demo of its usage in Morgan's Library at www.psoug.org.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org