DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Executing a string containg calculation!!!!!!!!!!!

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    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 := prefix||numerator/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.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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; 04-15-2003 at 08:08 PM.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, for my purposes, I'm going to hard-code 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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Okay, for my purposes, I'm going to hard-code 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

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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))/d-20 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; 04-16-2003 at 09:09 AM.
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    What kind of result would you like to get when you have something like:
    100*(sum(a+b))/d-20

    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
  •  


Click Here to Expand Forum to Full Width