
Executing a string containg calculation!!!!!!!!!!!
Hi,
We are writing a stored procedure in which a string is generated such as '10*4/2'. This string is stored in a memory variable.
How do I execute the string to get the result, in this case 200.
Please suggest.
Thanks
Ron
Here is a small example of the stored procedure
SQL> create or replace procedure ttt
2 is
3
4 prefix varchar2(100) := '100*';
5 numerator number := 4;
6 denominator number := 2;
7 temp varchar2(100);
8 temp1 varchar2(100);
9
10 begin
11
12 temp := prefixnumerator/denominator;
13
14 dbms_output.put_line(temp);
15
16
17 end;
18 /
Procedure created.
SQL> exec ttt
100*2
PL/SQL procedure successfully completed.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.

Well, after the initial confusion of trying to figure out how 10*4/2 yields 200, which the "10" is a typo and should have been 100, one is then left with the bewilderment of why you are using a varchar2 to do math when it would be soooo much easier to let numbers be numbers. Further, if the procedure is hard coded with the prefix, numerator and denominator, well, what's the point of the procedure? Do you want to have those three variables passed in and then have the procedure (or function) do the output you want? Inside what you have written, why can't you do the math? v_product := prefix * (numerator/denominator);
The only way your string is being generated is by your coding of it. I wouldn't say that is being generated as in a dynamic sense. Pass in the variables, then it is generated.
Last edited by stecal; 04152003 at 08:08 PM.

Okay, for my purposes, I'm going to hardcode a calculation, but here you go:
Code:
DECLARE
l_Result NUMBER;
l_SQL VARCHAR2(32767);
l_Calculation VARCHAR2( 4000) := '400/2*5';
BEGIN
l_SQL := '
SELECT
'l_Calculation'
FROM
DUAL ';
EXECUTE IMMEDIATE
l_SQL
INTO
l_Result;
DBMS_OUTPUT.PUT_LINE(l_Result);
END;
 Chris

Originally posted by chrisrlong
Okay, for my purposes, I'm going to hardcode a calculation, but here you go:
Code:
DECLARE
l_Result NUMBER;
l_SQL VARCHAR2(32767);
l_Calculation VARCHAR2( 4000) := '400/2*5';
BEGIN
l_SQL := '
SELECT
'l_Calculation'
FROM
DUAL ';
EXECUTE IMMEDIATE
l_SQL
INTO
l_Result;
DBMS_OUTPUT.PUT_LINE(l_Result);
END;
 Chris
Proving that if you really want to write bad code, you can!
Jeff Hunter

Originally posted by stecal
Well, after the initial confusion of trying to figure out how 10*4/2 yields 200, which the "10" is a typo and should have been 100, one is then left with the bewilderment of why you are using a varchar2 to do math when it would be soooo much easier to let numbers be numbers. Further, if the procedure is hard coded with the prefix, numerator and denominator, well, what's the point of the procedure? Do you want to have those three variables passed in and then have the procedure (or function) do the output you want? Inside what you have written, why can't you do the math? v_product := prefix * (numerator/denominator);
The only way your string is being generated is by your coding of it. I wouldn't say that is being generated as in a dynamic sense. Pass in the variables, then it is generated.
We are not hardcoding anything here. I just did that to have a simple example. We have a table with hundreds or formulas and these formulas are split into 4 parts.
Prefix,Numerator,denominator and suffix.
The prefix can be anything such as '100+', '100*', '1' , '20+' ....
The numerator can be anything such as '12','sum(a+b)','a*b', ....
The denominator can be anything such as '12','sum(a+b)','a*b', and so on.
and lastly the suffix can be anything such as '*100','20','+200' etc etc
OUr procedure goes and generates the formula in question and executes it to get the result.
The generated formula might look something like this
100*(sum(a+b))/d20 where prefix is 100*, numerator is sum(a+b), denominator is d and suffix is 20.
I hope now it make sense why I am trying to do it.
Thanks
Last edited by ronnie; 04162003 at 09:09 AM.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.

What kind of result would you like to get when you have something like:
100*(sum(a+b))/d20
What is a and b?
What do you expect from the sum() function?
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
