This discussion is archived
1 2 3 4 6 Previous Next 81 Replies Latest reply: Mar 12, 2013 9:22 AM by odie_63 Go to original post RSS
  • 15. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    Justin Cave wrote:
    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.
    You're right Justin, it's a lot more code than the dynamic SQL method, however you're presuming too much about the efficiency.
    See the link above.
  • 16. Re: Dynamic SQL without using SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    odie_63 wrote:

    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/
    Nice one Odie. I just may borrow that package in future. Thanks. :-)
  • 17. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    odie_63 wrote:
    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/
    Very nice Odie, takes me back to my Uni coursework... recursing syntax checker, reverse-polish notation, shunting yard algorithm.... all stuff we did back then. The one we did was a little different, as the lecturers had defined a small programming language of their own we had to implement and it actually covered 3 modules (and 3 lecturers) of the course... User Interface Design (to create an interface to load, edit, save code, and to run it in with output window (extra marks for multi window environment - bearing in mind this was written in Modula-2 for DOS)), Programming (to ensure we coded in Modula 2, correctly modularising our code, using proper data constructs etc.) and Language Processing (to turn their languages syntax diagrams into a recursive parser, syntax checker, and code interpreter). The result was a nice little application where you could write your code (with cut, copy, paste and all the basic editor abilities), save it to files and re-load it, then run it as an interpreted language to see the output produced. The language they defined included loops and conditional code, as well as variable declaration, and mathematical formula interpretation with operator order of precedence etc. I've probably still got it at home somewhere archived from my floppy disks onto CD I should think.

    Ahhh, the days when they taught programming at a proper level from the basics upwards... so many nowadays don't seem to get that background. :)
  • 18. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    "it's a lot more code than the dynamic SQL method, however you're presuming too much about the efficiency."

    I agree, any database call should be slower than a calculation in memory, which should be instantaneous.
  • 19. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    "it's a lot more code than the dynamic SQL method, however you're presuming too much about the efficiency."

    I agree, any database call should be slower than a calculation in memory, which should be instantaneous.
    Now you're presuming too much about the quantum physics of computing. Nothing will be instantaneous.

    The issue you're concerning yourself with is often referred to as context switching, the time it takes for the PL engine to intereact with SQL engine and back again, and yes that is often seen as a bottleneck. However, if you are going to be processing many rows of data from a database table, then rather than get each one using SQL and process each one within PL/SQL (avoiding further calls back to SQL to do the actual evaluation), if you could implement the evaluation within SQL itself, then you could send the request as one whole thing to SQL in one go, reducing context switching.
  • 20. Re: Dynamic SQL without using SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    970779 wrote:

    I agree, any database call should be slower than a calculation in memory, which should be instantaneous.
    You need to clarify that statement - a database call is fully executed in memory. A database call is not slower than for example calling the Windows server management process to determine the status of a service. Or making a call to write to the Windows event log.

    Database calls are not slow and not something to avoid. The issue is how the client implements and uses such calls. What the client tasks the database to do with the call. And the actual database processing that happens as a result of that call.

    Memory is fast. And the database exploits memory to its full advantage - usually better than what most app developers can.
  • 21. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Blushadow:
    "Now you're presuming too much about the quantum physics of computing. Nothing will be instantaneous.

    The issue you're concerning yourself with is often referred to as context switching, the time it takes for the PL engine to intereact with SQL engine and back again, and yes that is often seen as a bottleneck. However, if you are going to be processing many rows of data from a database table, then rather than get each one using SQL and process each one within PL/SQL (avoiding further calls back to SQL to do the actual evaluation), if you could implement the evaluation within SQL itself, then you could send the request as one whole thing to SQL in one go, reducing context switching."

    Of course I'm not speaking literally when I say 'instantaneous', but a simple calculation should be a lot faster than a hand held calculator could do the same, and it should be virtually instantaneous, and certainly faster than a DB call which has overheads.
    Calculating is the most basic function of computers!

    Regarding the proposal to use the original DB call to do the calculation at the same time, yes sounds good, but the original call is a cursor loop that gets however many lines of a formula exist, and they are strung together into a long string filled with literals and operands. This is done thousands of times so the shared pool fills up with unique statements.
    Because the final formulas vary a lot it's not even possible to substitute bind variables. These are some examples:

    SELECT 99.49*1.5 + 304.59 FROM DUAL
    SELECT 99.29 + 64.55 FROM DUAL
    SELECT 992.18-199.71 FROM DUAL
    SELECT 8.7*2 + (516.25 -8.7) FROM DUAL

    I think a package to parse it might be best, but it has to recognize the hierarchy of operands and parentheses.
  • 22. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    Blushadow:
    "Now you're presuming too much about the quantum physics of computing. Nothing will be instantaneous.

    The issue you're concerning yourself with is often referred to as context switching, the time it takes for the PL engine to intereact with SQL engine and back again, and yes that is often seen as a bottleneck. However, if you are going to be processing many rows of data from a database table, then rather than get each one using SQL and process each one within PL/SQL (avoiding further calls back to SQL to do the actual evaluation), if you could implement the evaluation within SQL itself, then you could send the request as one whole thing to SQL in one go, reducing context switching."

    Of course I'm not speaking literally when I say 'instantaneous', but a simple calculation should be a lot faster than a hand held calculator could do the same, and it should be virtually instantaneous, and certainly faster than a DB call which has overheads.
    Calculating is the most basic function of computers!
    Depends on your level of abstraction. The most basic function of computers (if we stay away from the elecrons moving around circuitry) is to manipulate bytes, words, double words etc. of bits (depending on the bit size of the processor). Basic calculations are done by shifting bits left and right in the bytes and using the carry flag. Floating point calculations involve more work and thus a floating point calcuator (ah the days when PC's went from 386SX's to include the optional floating point unit, and then the 486 came along with it's DX ability built in). As for implementing 'functions' such as "power", "square root", "sin", "cos" and "tan" etc. they're really really advanced processing and certainly not basic at all.
    Regarding the proposal to use the original DB call to do the calculation at the same time, yes sounds good, but the original call is a cursor loop that gets however many lines of a formula exist, and they are strung together into a long string filled with literals and operands. This is done thousands of times so the shared pool fills up with unique statements.
    Because the final formulas vary a lot it's not even possible to substitute bind variables. These are some examples:

    SELECT 99.49*1.5 + 304.59 FROM DUAL
    SELECT 99.29 + 64.55 FROM DUAL
    SELECT 992.18-199.71 FROM DUAL
    SELECT 8.7*2 + (516.25 -8.7) FROM DUAL

    I think a package to parse it might be best, but it has to recognize the hierarchy of operands and parentheses.
    Or you could use XMLTABLE if it suits...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (SELECT 1 as id, '99.49*1.5 + 304.59' as fm FROM DUAL union all
      2             SELECT 2, '99.29 + 64.55' FROM DUAL union all
      3             SELECT 3, '992.18-199.71' FROM DUAL union all
      4             SELECT 4, '8.7*2 + (516.25 -8.7)' FROM DUAL
      5            )
      6  --
      7  --  end of test data
      8  --
      9  select id, fm, result
     10  from   t
     11*       ,xmltable(fm columns result number path '.')
    SQL> /
    
            ID FM                        RESULT
    ---------- --------------------- ----------
             1 99.49*1.5 + 304.59       453.825
             2 99.29 + 64.55             163.84
             3 992.18-199.71             792.47
             4 8.7*2 + (516.25 -8.7)     524.95
    One query, lots of results. :)

    it has a few differences though such as divide needs to be catered for to make it Xquery compatible...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (SELECT 1 as id, '99.49*1.5 + 304.59' as fm FROM DUAL union all
      2             SELECT 2, '99.29 + 64.55' FROM DUAL union all
      3             SELECT 3, '992.18-199.71' FROM DUAL union all
      4             SELECT 4, '8.7*2 + (516.25 -8.7)' FROM DUAL union all
      5             SELECT 3, '123.5/2' FROM DUAL
      6            )
      7  --
      8  --  end of test data
      9  --
     10  select id, fm, result
     11  from   t
     12*       ,xmltable(fm columns result number path '.')
    SQL> /
    ERROR:
    ORA-19112: error raised during evaluation:
    XVM-01020: [XPTY0020] The path step context item is not a node
    1   123.5/2
    -  ^
    
    
    
    no rows selected
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (SELECT 1 as id, '99.49*1.5 + 304.59' as fm FROM DUAL union all
      2             SELECT 2, '99.29 + 64.55' FROM DUAL union all
      3             SELECT 3, '992.18-199.71' FROM DUAL union all
      4             SELECT 4, '8.7*2 + (516.25 -8.7)' FROM DUAL union all
      5             SELECT 3, '123.5/2' FROM DUAL
      6            )
      7  --
      8  --  end of test data
      9  --
     10  select id, fm, result
     11  from   t
     12*       ,xmltable(replace(fm,'/',' div ') columns result number path '.')
    SQL> /
    
            ID FM                        RESULT
    ---------- --------------------- ----------
             1 99.49*1.5 + 304.59       453.825
             2 99.29 + 64.55             163.84
             3 992.18-199.71             792.47
             4 8.7*2 + (516.25 -8.7)     524.95
             3 123.5/2                    61.75
  • 23. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    970779 wrote:
    I think a package to parse it might be best, but it has to recognize the hierarchy of operands and parentheses.
    Did you see my examples of few posts above?
  • 24. Re: Dynamic SQL without using SQL
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    If you have the package: dbms_aw installed, see:
    http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_app_dbms_aw001.htm#OLADM964

    you can simply do:
    declare
    V_RESULT number;
    V_FORMULA varchar2(200) := '5 * 50 + 200';
    
    begin
    V_RESULT := DBMS_aw.eval_number(V_FORMULA);
    DBMS_OUTPUT.PUT_LINE(V_FORMULA || ' = ' || V_RESULT);
    end;
    
    -- result
    5 * 50 + 200 = 450
    Regards,

    Peter
  • 25. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Wow this looks like what I was looking for!!

    But alas... maybe this is 10G?

    Wrote file afiedt.buf

    1 declare
    2 V_RESULT number;
    3 V_FORMULA varchar2(200) := '5 * 50 + 200';
    4 begin
    5 V_RESULT := DBMS_aw.eval_number(V_FORMULA);
    6 DBMS_OUTPUT.PUT_LINE(V_FORMULA || ' = ' || V_RESULT);
    7* end;
    8 /
    declare
    *
    ERROR at line 1:
    ORA-37002: Oracle OLAP failed to initialize, please contact Oracle Support.
    ORA-33262: (DBERR01) Analytic workspace EXPRESS does not exist.
    ORA-06512: at "SYS.DBMS_AW", line 39
    ORA-06512: at "SYS.DBMS_AW", line 66
    ORA-06512: at line 5


    SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 19 14:21:16 2013

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 - Production
  • 26. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    No it does exist:

    SQL> desc DBMS_aw
    PROCEDURE ADVISE_CUBE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    ...
    FUNCTION EVAL_NUMBER RETURNS NUMBER
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD VARCHAR2 IN
    ...
  • 27. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Yes thanks Odie I saw your posts.
    I'll try to work it out if this AW thing doesn't work.
    Your link leads to long web page, and I didn't quite work out which bit is your package...?
  • 28. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Peter do you know how I can use this DBMS_AW package?
    It is installed but I get an error:

    declare
    *
    ERROR at line 1:
    ORA-37002: Oracle OLAP failed to initialize, please contact Oracle Support.
    ORA-33262: (DBERR01) Analytic workspace EXPRESS does not exist.
    ORA-06512: at "SYS.DBMS_AW", line 39
    ORA-06512: at "SYS.DBMS_AW", line 66
    ORA-06512: at line 5
  • 29. Re: Dynamic SQL without using SQL
    rp0428 Guru
    Currently Being Moderated
    Have you reviewed the doc for your Oracle version?
    http://docs.oracle.com/cd/B10500_01/olap.920/a95298/toc.htm

    That earlier package version has a very limited function set and you do things differently
    http://docs.oracle.com/cd/B10500_01/olap.920/a95295/dbms_aw2.htm#80564

    See the examples for the functions listed.
1 2 3 4 6 Previous Next

Legend

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