Variable Substitution, Execute Formula

[surajitmitra78]

Hi,

I want to calculate some numericals on a stored formula in the table. The scenario is as follows:

Declare
ll_val1 number(5) := 25;
ll_val2 number(5) := 50;
ll_formula varchar2(100) := 'll_val1 + ll_val2';

Begin
/* Pls help me writing a script which will print a output called 75 on executing the ll_formula variable i.e., i want to execute the formula variable(ll_formula) and display the result as '75' */
print the value 75 was per the variable.
End ;

[Vikas Verma]

Do you want to print the output only...?

if only printing the use
1. set serveroutput on - on the sql prompt
2. use dbms_output.put_line() - in the script

re-write the entire code like this
Declare
ll_val1 number(5) := 25;
ll_val2 number(5) := 50;
ll_formula varchar2(100);
Begin
ll_formula := ll_val1 + ll_val2;
dbms_output.put_line(ll_formula);
end;


i think it'll work the way you want;

cheers
vikas
_________________________________________________________________
Freedom is not worth having if it does not include the freedom to make mistakes.

Mahatma Gandhi
_________________________________________________________________

[surajitmitra78]

Hi Vikas,

If you see the code, i have written that i want to execute the variable ll_formula, which will have the formula pre-existing. I am not redefining the ll_formula in the code block as you did.

Anyways, thanks for thinking on it.

Anyone, please help.

[DaPi]

Not sure WHY you'd want to do this, but how about:

EXECUTE IMMEDIATE ('Select to_char('||ll_formula||') From dual') INTO my_varchar;

[surajitmitra78]

The concept worked but not the implementation that i wanted.

Say when i execute the statement using "EXECUTE IMMEDIATE", i have to replace the variable ll_val1 & ll_val2 with the actual values, otherwise it prompts me with the following error

Declare
ll_val1 number(5) := 25;
ll_val2 number(5) := 50;
ll_formula varchar2(100) := 'll_val1 + ll_val2';
ll_result number(7) := 0;
begin
dbms_output.put_line('ll_val1 : ' || ll_val1 || ' , ll_val2 : ' || ll_val2 );
EXECUTE IMMEDIATE ('Select ' || ll_formula || ' From dual') INTO ll_result;
dbms_output.put_line(ll_result);
end;
/

ERROR at line 1:
ORA-00904: "LL_VAL1": invalid identifier
ORA-06512: at line 8

so, i had to change ll_formula := '25 + 50' to get the result.

So, as of now my problem is not solved.

[DaPi]

http://download-west.oracle.com/docs...dynam.htm#8074

[surajitmitra78]

The article was great. It solved my other issues that i had. Now, I know how powerfull is EXECUTE IMMEDIATE..

But, the issue that i posted cannot be tracked down with the same.

I will change the scenario a bit, so that more light is put on the same.

CREATE OR REPLACE PROCEDURE P_DYNAMICFORMULA
( ll_formula char )
is
ll_val1 number(5) := 25;
ll_val2 number(5) := 50;
ll_result number(5);
begin
EXECUTE IMMEDIATE ('SELECT ' || ll_formula || ' FROM DUAL') INTO ll_result;
dbms_output.put_line('result : ' || ll_result );
end P_DYNAMICFORMULA;

Now, from the prompt users can fire any type of formula which is as follows:

exec P_DYNAMICFORMULA('ll_val1+ll_val2')

exec P_DYNAMICFORMULA('ll_val1/ll_val2')

exec P_DYNAMICFORMULA('ll_val1-ll_val2')

exec P_DYNAMICFORMULA('ll_val1*ll_val2')

if i execute the procedure now, it will prompt me with a error.

*
ERROR at line 1:
ORA-00904: "LL_VAL2": invalid identifier
ORA-06512: at "SCOTT.P_DYNAMICFORMULA", line 8
ORA-06512: at line 1

So, i think you got the problem i have.

So, still looking for HELP.

[DaPi]

(I'm begining to feel I've set off down the wrong path . . . )

Try?
EXECUTE IMMEDIATE ('SELECT ' || REPLACE(ll_formula,'ll_val',':a') || ' FROM DUAL') USING ll_val1, ll_val2 INTO ll_result;
Good for two arguments.

[surajitmitra78]

Hi DaPi,

Thanks for your valueable input, i was able to solve the problem i posted.

Here is the code which made this happened:

CREATE OR REPLACE PROCEDURE P_DYNAMICFORMULA
( ll_formula char )
is
ll_val1 number(5) := 25;
ll_val2 number(5) := 50;
ll_result number(5);
begin
EXECUTE IMMEDIATE ('SELECT ' || REPLACE(ll_formula,'ll_val',':a') || ' FROM DUAL') INTO ll_result USING IN ll_val1, IN ll_val2;
dbms_output.put_line('result : ' || ll_result );
end P_TESTDYNAMICFORMULA;

SQL> exec P_DYNAMICFORMULA('ll_val1+ll_val2')
result : 75

PL/SQL procedure successfully completed.

SQL> exec P_DYNAMICFORMULA('ll_val1-ll_val2')
result : -25

PL/SQL procedure successfully completed.

SQL> exec P_DYNAMICFORMULA('ll_val1*ll_val2')
result : 1250

Thanks again...