This discussion is archived
1 2 3 6 Previous Next 81 Replies Latest reply: Mar 12, 2013 9:22 AM by odie_63 RSS

Dynamic SQL without using SQL

Templestowe Newbie
Currently Being Moderated
I have a variable that contains a formula, eg.
V_FORMULA varchar2(200) := '5 * 50 + 200';

I want to assign the result of the formula into another variable, without using a DB call with SQL.

eg.
V_RESULT number;

V_RESULT := DBMS_surprise_package(V_FORMULA);

I want V_RESULT to be 450 after the statement is executed.

Is that possible?? Is there such a package in PLSQL?

I think the Forms NAME_IN package did something similar.
  • 1. Re: Dynamic SQL without using SQL
    sb92075 Guru
    Currently Being Moderated
    970779 wrote:
    I have a variable that contains a formula, eg.
    V_FORMULA varchar2(200) := '5 * 50 + 200';

    I want to assign the result of the formula into another variable, without using a DB call with SQL.

    eg.
    V_RESULT number;

    V_RESULT := DBMS_surprise_package(V_FORMULA);

    I want V_RESULT to be 450 after the statement is executed.

    Is that possible?? Is there such a package in PLSQL?
    no, but you are free to write such a package.
  • 2. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Surely it's possible, seems so simple!
    I wouldn't know where to start to write one though.
  • 3. Re: Dynamic SQL without using SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    You can certainly write one. The function you write, though, will need to use dynamic SQL
    SQL> create or replace function compute_formula( p_formula in varchar2 )
      2    return number
      3  is
      4    l_ret number;
      5  begin
      6    execute immediate 'select ' || p_formula || ' from dual'
      7       into l_ret;
      8    return l_ret;
      9  end;
     10  /
    
    Function created.
    
    SQL> declare
      2    l_formula varchar2(200) := '5 * 50 + 200';
      3    l_result  number;
      4  begin
      5    l_result := compute_formula( l_formula );
      6    dbms_output.put_line( l_result );
      7  end;
      8  /
    450
    
    PL/SQL procedure successfully completed.
    Justin
  • 4. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Yes it's already in dynamic SQL but it shouldn't need DB access I think.
    I bet you can do it easily in C...
  • 5. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    Surely it's possible, seems so simple!
    It might seem simple, but there's more to it than that. You're talking of providing a single string of characters and have something that parses that string to evaluate the expression contained within it and return the result of the expression. SQL has such a parser built into it if you use dynamic SQL to turn the string into a part of the SQL rather than an isolated string. You could also do dynamic PL/SQL code as well if you wanted:
    SQL> set serverout on
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    l_formula varchar2(200) := '5 * 50 + 200';
      3    l_result  number;
      4  begin
      5    execute immediate 'begin :result := '||l_formula||'; end;' using out l_result;
      6    dbms_output.put_line(l_result);
      7* end;
    SQL> /
    450
    
    PL/SQL procedure successfully completed.
    ... though I'd seriously advise against dynamic PL/SQL code.

    Other than that, you'll have to write your own expression parser. If you've done a course on language syntax diagrams etc. you should find it easy enough to write on. I did one at university as part of our course (along with writing the graphical code editor, and compiler).
  • 6. Re: Dynamic SQL without using SQL
    rp0428 Guru
    Currently Being Moderated
    >
    I have a variable that contains a formula, eg.
    V_FORMULA varchar2(200) := '5 * 50 + 200';

    I want to assign the result of the formula into another variable, without using a DB call with SQL.

    eg.
    V_RESULT number;

    V_RESULT := DBMS_surprise_package(V_FORMULA);

    I want V_RESULT to be 450 after the statement is executed.

    Is that possible?? Is there such a package in PLSQL?
    >
    No - you need to use SQL.
  • 7. Re: Dynamic SQL without using SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    Then I'm confused.

    You can't do anything in PL/SQL without a PL/SQL environment. The vast majority of the PL/SQL environments out there run in an Oracle database. The remainder run Oracle Forms. Based on your first post, it doesn't sound like you're using Forms. Thus, the only other place that you could be using PL/SQL would be in a database. Which implies that you have to connect to the database.

    Of course, nothing stops Oracle from releasing a standalone PL/SQL compiler/ interpreter that would run without a database. There just isn't a whole lot of demand for such a thing (though Brynn Llewellyn if you're reading, I'm totally in favor of it!). There are already any number of compilers and interpreters for different languages (Java/ C/ etc) that produce desktop applications.

    Justin
  • 8. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Yes Justin of course it's connected to a database.

    It would simply be more efficient to do the calculation without a database call, if that were possible, which it apparently isn't.

    Better to do:
    A:= 21 + 3;
    than
    select 21 + 3 into A from dual;
  • 9. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    I guess I'll just have to rewrite it using execute immediate with bind variables to stop the shared pool getting filled up with thousands of these statements, since none of you have a non-db solution.
  • 10. Re: Dynamic SQL without using SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    970779 wrote:
    Yes Justin of course it's connected to a database.

    It would simply be more efficient to do the calculation without a database call, if that were possible, which it apparently isn't.

    Better to do:
    A:= 21 + 3;
    than
    select 21 + 3 into A from dual;
    What does a "database call" mean to you? Calling a PL/SQL function is a database call to me. Executing any operation in PL/SQL is a database call.

    Of course, if you really, really wanted to, you could parse the string and do the computation without using SQL. It would be slower that making a single SQL call. You'd need to implement a bunch of logic that Oracle has already provided (you'd need to identify all the operators that you want to support, you'd need to code the order of operations rules, etc.). It's doable. But it's way more code and far less efficient than a simple EXECUTE IMMEDIATE.
    I guess I'll just have to rewrite it using execute immediate with bind variables to stop the shared pool getting filled up with thousands of these statements, since none of you have a non-db solution.
    Why are you going to be doing this thousands of times? That implies that you probably have an underlying data model problem that should be resolved.

    Justin
  • 11. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    I guess I'll just have to rewrite it using execute immediate with bind variables to stop the shared pool getting filled up with thousands of these statements, since none of you have a non-db solution.
    Write your own if the expressions are simple enough...
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    l_formula varchar2(200) := '5 * 50 + 200';
      3    l_result  number := 0;
      4    tok       varchar2(100);
      5    op        varchar2(100);
      6    function get_token(str in out varchar2) return varchar2 is
      7    begin
      8      tok := trim(regexp_substr(str,'^[0-9]+|[^0-9]+'));
      9      str := trim(regexp_replace(str,'^([0-9]+|[^0-9]+)'));
     10      return tok;
     11    end;
     12  begin
     13    loop
     14      tok := get_token(l_formula);
     15      exit when tok is null;
     16      if tok in ('*','+','-','/') then
     17        op := tok;
     18      else
     19        case op when '*' then l_result := l_result * to_number(tok);
     20                when '+' then l_result := l_result + to_number(tok);
     21                when '-' then l_result := l_result - to_number(tok);
     22                when '/' then l_result := l_result / to_number(tok);
     23        else l_result := to_number(tok);
     24        end case;
     25      end if;
     26    end loop;
     27    dbms_output.put_line(l_result);
     28* end;
    SQL> /
    450
    
    PL/SQL procedure successfully completed.
    :D
  • 12. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    The problem is that this:
    EXECUTE IMMEDIATE 'SELECT '|| g_counters(idx).formula_text || ' FROM DUAL'
    INTO g_counters(idx).new_adcv_val;
    is in a loop, and the loop runs hundreds of times, so it creates new statements in the shared pool each time like this:
    SELECT .25*1.5 + 43.35 FROM DUAL

    So I'll change it to use bind variables.

    What would that be...?
    ...
    sql_stt varchar2(1000) := 'SELECT :1 INTO :2 FROM DUAL';
    begin
    EXECUTE IMMEDIATE sql_stt USING g_counters(idx).formula_text, g_counters(idx).new_adcv_val;
    ...
  • 13. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Wow nice one Blushadow.
    I guess an exec immed with using will be simpler..
  • 14. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    BluShadow wrote:
    Write your own if the expressions are simple enough...
    I've recently built a PL/SQL RPN calculator that does exactly that, with the abilities to call functions too :

    http://odieweblog.wordpress.com/2013/02/03/plsql-rpn-calculator/
    SQL> select rpn_util.eval(
      2           rpn_util.parse('3*(1+2)^3')
      3         ) as result
      4  from dual;
     
        RESULT
    ----------
            81
     
    SQL> 
    SQL> select rpn_util.eval(
      2           rpn_util.parse('V1*(V2+V3)^V4')
      3         , kv_table(
      4             kv_obj('V1',3)
      5           , kv_obj('V2',1)
      6           , kv_obj('V3',2)
      7           , kv_obj('V4',3)
      8           )
      9         ) as result
     10  from dual;
     
        RESULT
    ----------
            81
     
    Edited by: odie_63 on 12 févr. 2013 18:04 - added examples
1 2 3 6 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points