This discussion is archived
1 2 3 4 5 6 Previous Next 81 Replies Latest reply: Mar 12, 2013 9:22 AM by odie_63 Go to original post RSS
  • 45. Re: Dynamic SQL without using SQL
    Paul Horth Expert
    Currently Being Moderated
    APC wrote:
    I rather think "steve" is pulling our leg with that profile. If there's one thing all my years on the forums has taught me it's that people who put "guru" in their handles aren't.

    Cheers, APC
    You obviously didn't detect the invisible <sarcasm> markers in my post. :-)
  • 46. Re: Dynamic SQL without using SQL
    APC Oracle ACE
    Currently Being Moderated
    Paul  Horth wrote:
    You obviously didn't detect the invisible <sarcasm> markers in my post. :-)
    Actually I did, I was just playing it deadpan, straightman style.

    And that's the other thing all my years on the forums have taught me: humour is a tough thing to make work. That's why Nature gave us emoticons 8-)

    Cheers, APC

    Edited by: APC on Feb 21, 2013 8:58 AM

    Although it appears Jive doesn't give us all of them :-(
  • 47. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    It's not related to bad design.

    A database call should not be necessary to perform a simple (or complex) calculation, because that has unnecessary overheads, whereas a calculation only requires shuffling of bytes in memory which is much faster.
    The overlooked requirement (which is apparently addressed by OLAP_AW.EVAL_NUMBER) is to evaluate a formula held in a string, which could probably be widely used, instead of execute immediates, to save a lot of resource.
    Any calculation can be done as a simple assignment in PL/SQL, eg. l_result := 23*4+77/(76+56) with no call to the SQL engine, using negligible resource, but apparently not as contents of a variable.
    I vaguely remember something like indirect variable references or something doing it in Fortran or Pascal, old low level languages, so PL/SQL ought to have it!
    Like I said before bind variables were not possible without some very complex design, since the formulae could be in any form with any number of literals and operands strung together in a loop.
  • 48. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    970779 wrote:
    Any calculation can be done as a simple assignment in PL/SQL, eg. l_result := 23*4+77/(76+56) with no call to the SQL engine, using negligible resource, but apparently not as contents of a variable.
    That's exactly where the big difference is. You don't seem to get it.

    A static calculation coded in PL/SQL is parsed into an instruction tree when the unit is compiled. Then the PL/SQL engine already know what to do at runtime.
    When native compilation is used, the calculation may even be further optimized to directly leverage processor arithmetics and memory structures (registers).

    Of course, that's not possible when the expression is held in a string. There has to be a supplementary parse step.
  • 49. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    >
    It's not related to bad design.
    >
    Really? ...

    >
    A database call should not be necessary to perform a simple (or complex) calculation, because that has unnecessary overheads, whereas a calculation only requires shuffling of bytes in memory which is much faster.
    >
    Absolutely, and PL is perfectly capable of performing such simple or complex calculations without issuing any SQL statements.

    >
    The overlooked requirement (which is apparently addressed by OLAP_AW.EVAL_NUMBER) is to evaluate a formula held in a string, which could probably be widely used, instead of execute immediates, to save a lot of resource.
    >
    You see, there's the bad design. Why are formula being held in a string? A string is just a string... it could contain any text you want... so is a string the best place to store a formula? and why should Oracle cater for such a bizarre mis-design? In fact why don't we say that Oracle has a fundamental flaw, because they haven't provided a FORMULA datatype specially for formulae:
    DECLARE
      fm FORMULA := '2*(4+power(7,3))';
      nm NUMBER;
    BEGIN
      nm := fm;  -- implicit conversion from formula to it's resultant number
    END;
    ... and then while we're at it, why isn't there a special datatype already for... oooo... let's say... a persons details or some address details... as people use those things all the time in databases...
    DECLARE
      per PERSON := ('FRED', 'BLOGGS', date '1977-06-05');
      add ADDRESS := ('1234a', 'Main Street', 'Sometown', 'Somewhere County', 'AB12 3CD');
    BEGIN
    ..
    ..
    Any calculation can be done as a simple assignment in PL/SQL, eg. l_result := 23*4+77/(76+56) with no call to the SQL engine, using negligible resource, but apparently not as contents of a variable.
    I vaguely remember something like indirect variable references or something doing it in Fortran or Pascal, old low level languages, so PL/SQL ought to have it!
    >
    C has memory allocation calls (malloc) and pointers etc. so should PL/SQL ought to have those too? Your logic is flawed. Just because some old language had such a thing before doesn't make it necessary now.

    >
    Like I said before bind variables were not possible without some very complex design, since the formulae could be in any form with any number of literals and operands strung together in a loop.
    >
    So, what is the business case that requires different formula to be stored as strings on the database to be calculated at a later date (such that performance of the calculations will be imperative)?
  • 50. Re: Dynamic SQL without using SQL
    Paul Horth Expert
    Currently Being Moderated
    APC wrote:
    Paul  Horth wrote:
    You obviously didn't detect the invisible <sarcasm> markers in my post. :-)
    Actually I did, I was just playing it deadpan, straightman style.

    And that's the other thing all my years on the forums have taught me: humour is a tough thing to make work. That's why Nature gave us emoticons 8-)

    Cheers, APC

    Edited by: APC on Feb 21, 2013 8:58 AM

    Although it appears Jive doesn't give us all of them :-(
    LOL - I didn't see the <deadpan> markers!

    Agreed, humour is difficult and also cultural differences can make someone seem rude without them intending to be. That's why Nature gave us the delete key.
  • 51. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    From what I can glean from a colleague, the DB design in this area is to calculate a charge for a client who leases a photocopier.
    It counts the number of photocopies printed with a leased printer, in colour or BW, with different paper sizes, and several other variables that each have a different cost.
    Thus the calculation could be 667*.32*.7 + 788*.55... etc.
    I don't know how they get / and - in there but they do, maybe discount percentages or something.
    The original programs are from 10 years ago and the design seems very complex, but my task is just to get rid of the thousands of stts like these, not redesign the whole system, which would be a big job with testing.
    SELECT 0*1.5 + (84.54 -0) FROM DUAL
    SELECT 0*1.5 + .01 FROM DUAL
    SELECT 1*1.5 + (275.59 -1) FROM DUAL
  • 52. Re: Dynamic SQL without using SQL
    6363 Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    the meaning of "guru" has been used to cover anyone who acquires followers, especially by exploiting their naiveté, due to the inflationary use of the term in new religious movements.
    >
    Ahh. . . perhaps like those who acquire some number of, say, 'points' beyond some arbitrary and capricious level, say 2500? ;)
    Touche. I hadn't realized my ID had acquired that stupid guru tag if that's what you mean :-(
  • 53. Re: Dynamic SQL without using SQL
    Marwim Expert
    Currently Being Moderated
    to calculate a charge for a client who leases a photocopier.
    Does this mean that the string is an input from another system instead of passing variables that can be used in a procedure?
    Is it a performance issue when the SQL engine is called to calculate the charge?
  • 54. Re: Dynamic SQL without using SQL
    APC Oracle ACE
    Currently Being Moderated
    970779 wrote:
    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
    Analytic workspaces is part of OLAP which is a separaretly licensed product: [url http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CIHGDEEF]find out more. I would guess that error message means you don't have an OLAP license.

    Cheers, APC
  • 55. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    >
    From what I can glean from a colleague, the DB design in this area is to calculate a charge for a client who leases a photocopier.
    It counts the number of photocopies printed with a leased printer, in colour or BW, with different paper sizes, and several other variables that each have a different cost.
    Thus the calculation could be 667*.32*.7 + 788*.55... etc.
    I don't know how they get / and - in there but they do, maybe discount percentages or something.
    >

    As I was saying, the design is flawed. That business scenario is no justification for storing formulae as strings on the database, to calculate a clients charges. Databases are designed for relational data so something along the lines of the following would be more like what I'd expect to see of a client-order-charges type system (very rough example)...
    SQL> create table costs as
      2  select 1 as cost_id, 'Black and White Copies' as description, 0.32 as cost, 'Fixed' as cost_type from dual union all
      3  select 2, 'Colour Copies', 0.55, 'Fixed' from dual union all
      4  select 3, 'Lease per month', 10, 'Fixed' from dual union all
      5  select 4, 'Maintenance Call Out', 25, 'Fixed' from dual union all
      6  select 5, 'Special Discount 1', 10, 'Discount%' from dual union all
      7  select 6, 'Special Discount 2', 15, 'Fixed Discount' from dual union all
      8  select 7, 'Tax', 20, 'Tax%' from dual
      9  /
    
    Table created.
    
    SQL>
    SQL> create table customer as
      2  select 1 as cust_id, 'Joe Bloggs Accountants' as cust_name from dual union all
      3  select 2, 'Fred Smith Plumbers' from dual
      4  /
    
    Table created.
    
    SQL>
    SQL> create table customer_cost as
      2  select 1 as cust_id, 1 as cost_id, 1 as ord, 667 as quantity from dual union all
      3  select 1, 2, 2, 788 from dual union all
      4  select 1, 3, 3, 6 from dual union all
      5  select 1, 5, 4, 1 from dual union all
      6  select 1, 7, 5, 1 from dual union all
      7  select 2, 1, 1, 257 from dual union all
      8  select 2, 2, 2, 50 from dual union all
      9  select 2, 3, 3, 12 from dual union all
     10  select 2, 6, 4, 1 from dual union all
     11  select 2, 7, 5, 1 from dual
     12  /
    
    Table created.
    
    SQL>
    SQL> break on cust_id skip 1
    SQL>
    SQL> select cust_id, cust_name, description, cost, quantity, item_cost
      2        ,case when tax is not null then round(lag(running_total) over (partition by cust_id order by ord) * tax, 2)
      3         else running_total
      4         end as running_total
      5  from (
      6        select cc.ord
      7              ,c.cust_id
      8              ,c.cust_name
      9              ,cst.description
     10              ,cst.cost
     11              ,cc.quantity
     12              ,case when cost_type = 'Fixed' then to_char(cst.cost*quantity,'9,999,999.00')||' '
     13                    when cost_type = 'Fixed Discount' then to_char(-(cst.cost*quantity),'9,999,999.00')||' '
     14                    when cost_type = 'Discount%' then to_char(-(cst.cost*quantity),'S999999999999')||'%'
     15                    when cost_type = 'Tax%' then to_char(cst.cost*quantity,'S999999999999')||'%'
     16               end as item_cost
     17              ,case when cost_type like 'Fixed%' then sum(case when cost_type = 'Fixed' then cst.cost*quantity
     18                                                               when cost_type = 'Fixed Discount' then -(cst.cost*quantity)
     19                                                          else null end)
     20                                                      over (partition by c.cust_id order by cc.ord)
     21                    when cost_type = 'Discount%' then round(sum(case when cost_type = 'Fixed' then cst.cost*quantity
     22                                                                     when cost_type = 'Fixed Discount' then -(cst.cost*quantity)
     23                                                                else null end)
     24                                                            over (partition by c.cust_id order by cc.ord) * ((100-(cst.cost*quantity))/100),2)
     25               end as running_total
     26              ,case when cost_type = 'Tax%' then 1+(cst.cost*quantity/100) else null end tax
     27        from customer c
     28             join customer_cost cc on (cc.cust_id = c.cust_id)
     29             join costs cst on (cst.cost_id = cc.cost_id)
     30       )
     31  order by cust_id, ord
     32  /
    
       CUST_ID CUST_NAME              DESCRIPTION                  COST   QUANTITY ITEM_COST      RUNNING_TOTAL
    ---------- ---------------------- ---------------------- ---------- ---------- -------------- -------------
             1 Joe Bloggs Accountants Black and White Copies        .32        667        213.44         213.44
               Joe Bloggs Accountants Colour Copies                 .55        788        433.40         646.84
               Joe Bloggs Accountants Lease per month                10          6         60.00         706.84
               Joe Bloggs Accountants Special Discount 1             10          1           -10%        636.16
               Joe Bloggs Accountants Tax                            20          1           +20%        763.39
    
             2 Fred Smith Plumbers    Black and White Copies        .32        257         82.24          82.24
               Fred Smith Plumbers    Colour Copies                 .55         50         27.50         109.74
               Fred Smith Plumbers    Lease per month                10         12        120.00         229.74
               Fred Smith Plumbers    Special Discount 2             15          1        -15.00         214.74
               Fred Smith Plumbers    Tax                            20          1           +20%        257.69
    
    
    10 rows selected.
    
    SQL>
    The original programs are from 10 years ago and the design seems very complex, but my task is just to get rid of the thousands of stts like these, not redesign the whole system, which would be a big job with testing.
    >
    The design seems complex probably because it was 'designed' by someone who didn't know how to design relational databases, or thought they were being clever by trying to introduce some 'generic' functionality, by allowing formulae to be stored in the database rather than the actual values and relationships. Pity you can't redesign it.
  • 56. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Nono I guess I didn't explain properly.
    Each literal (numeric amount) and each operand is a separate field in the database - formula strings are not stored, but derived, strung together in a loop stt.
    So the cost at the previous value of a counter is strung together and subtracted from the current value, each value comprised of numerous different variables like paper sizes and colour multiplied by price and quantity.
    So it seems to be quite normalized and modular.
  • 57. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    Nono I guess I didn't explain properly.
    Each literal (numeric amount) and each operand is a separate field in the database - formula strings are not stored, but derived, strung together in a loop stt.
    So the cost at the previous value of a counter is strung together and subtracted from the current value, each value comprised of numerous different variables like paper sizes and colour multiplied by price and quantity.
    So it seems to be quite normalized and modular.
    Now I'm confused. So the formulae aren't stored as strings on the database, but the code is creating them from data on the database? In that case why is it building them up as strings rather than just performaing the calculations directly on the data on the database based on the values and operands that are stored there (similar to the example I gave above)? That's what analytical functions provide in SQL.

    I think if you want to explain it properly, you're going to have to supply an example of some data for us.
  • 58. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Not sure, it's too complex for me to delve into in my limited time. Sorry haven't found how to put courier in the HTML tags yet!
    NAME                 DESCRIPTION                    FORMULA_TEXT
    -------------------- ------------------------------ ------------------------------------------------
    (F) L2_A3 Color      A3 Color                       :Total-(:BIL+:BIS+:FCS)
    (F) L1_A4 b/w        A4 b/w production click        ((:Total-(:BIL+:BIS+:FCS))*8)+(:BIL*2)+:BIS+(:FCS*4)
    (F) L1_A4 b/w        A4 b/w production click        :Total
    (F) L2_A3 b/w        A3 b/w                         :Tot-:Tli
    (F) L1_A4 b/w        A4 b/w production click        :TLi+((:Tot-:TLi)*2)
    (F) L2_A4 color      A4 color                       :Tot-(:FSt+:Sort)
    (F) L1_A4 color      A4 color production click      :FSt+:Tot
    (F) L2_A4 color      A4 color                       :Tot-(:FSt+:Sort)
    (F) L1_A4 color      A4 color production click      :FSt+:Tot
    (F) L1_A4 b/w        A4 b/w production click        :A4+(:A3*2)
    (F) L2_A3 b/w        A3 b/w                         :Total-:A4
    (F) L1_A4 b/w        A4 b/w production click        :A4+((:Total-:A4)*2)
    (F) L2_A3 b/w        A3 b/w                         :Total-:A4
    (F) L1_A4 b/w        A4 b/w production click        :A4+((:Total-:A4)*2)
    (F) L1_A4 b/w        A4 b/w production click        :Total
    (F) L1_A4 b/w        A4 b/w production click        :Total
    (F) L2_A4 color      A4 color                       :Total-(:FarveSt+:Sort)
    (F) L1_A4 color      A4 color production click      :FarveSt+:Total1
    (F) L2_A4 color      A4 color                       :Total-(:Farve+:Sort)
    Edited by: BluShadow on 21-Feb-2013 13:57
    Just add {noformat}
    {noformat} tags before and after the code as described in the FAQ: {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 59. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    Well, that's not really enough information for us to go on, though at least we can see the formula are stored on the database, although we don't have the values or logic to see how it fits together.

    Wish you luck with your requirement, looks like you're going to need it.

Legend

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