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
  • 60. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Oh I see it's the literal /{code/}, I thought you were referring to some unspecified html tag code.
    thanks

    Edited by: 970779 on Feb 21, 2013 6:08 AM
  • 61. Re: Dynamic SQL without using SQL
    padders Pro
    Currently Being Moderated
    Well...
    :Total-(:BIL+:BIS+:FCS)
    ...is not the same as a literal sum, the difference being it can be parsed once and executed many times binding different values of :Total, :BIL, :BIS and :FCS.

    As long as there are not too many distinct versions of the formula this could be relatively efficient.
  • 62. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Funny, someone tagged 5 responses as Helpful and one as Correct... I wonder which answers?
    How does this forum work, I guess people log on as another user and tag their own answers so they get more points, like scoring points in World of Warcraft. kewl
  • 63. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    Funny, someone tagged 5 responses as Helpful and one as Correct... I wonder which answers?
    How does this forum work, I guess people log on as another user and tag their own answers so they get more points, like scoring points in World of Warcraft. kewl
    No. What you're seeing is the message on your own question telling you that there is 1 correct and 5 helpful answers available to be awarded... but you haven't awarded them yet. Once they're awarded you would see yellow stars against the helpful answers and a green start against the correct answer (and remember that once they're awarded they cannot be taken away again)

    Occasionally we do get someone trying to be clever and signing up with another account to ask basic questions so they can answer them themselves and award themselves points, but typically this is spotted by moderators and regular members and such people have their accounts terminated. The biggest way to notice these is the newbies awarding points to other fairly new members who are posting answers that are obviously not good or correct answers.
  • 64. Re: Dynamic SQL without using SQL
    padders Pro
    Currently Being Moderated
    Ah thanks Blu, I did wonder how that worked.

    If someone really was 'win-trading' or otherwise exploiting the forum to improve their rating they are taking a spectacular risk in front of their peers. Whatever next? Botting?

    Regarding your question, did you actually find a solution?
  • 65. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Actually I'm back to square one.

    I installed the parser someone created and called it as a function in the loop that was using the Execute Immediate to generate countless dynamic SQLs with literals which filled the Shared Pool.
    But just now I've discovered it's much slower!
    2:03 compared to 0:05 Elapsed.

    So I stand corrected, database calls can be faster than mere calculations.

    Bind variables would be good, but the formulae are generated in a loop reading from a table, with resulting formule like eg. 321*5+3-3213*765 or 989/2-4324*7656+8788-653 with no fixed order or number of literals and operands.
  • 66. Re: Dynamic SQL without using SQL
    padders Pro
    Currently Being Moderated
    the formulae are generated in a loop reading from a table...with no fixed order or number of literals and operands
    Then why did you post the formula column before clearly containing placeholders for values?
    :Total-(:BIL+:BIS+:FCS)
  • 67. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Not sure what you mean by POST - but there could be any number of operators (+-/*) interspersed with any number of literals (321, 453, 4322345),
    so a bind variable statement can't be used.
    Bind variables can't be used for operators.
  • 68. Re: Dynamic SQL without using SQL
    padders Pro
    Currently Being Moderated
    Not sure what you mean by POST
    See your 'post' at 21-Feb-2013 05:50 in this thread.
    interspersed with any number of literals
    The post I am referring to above clearly contains placeholders, not literals.
    so a bind variable statement can't be used.
    Not necessarily. Your previous post contains both repetition of formulae and a limited number of formulae. Think outside the box.
    Bind variables can't be used for operators
    Correct, but a.) where did I say or otherwise imply that they could and b.) why are you telling me how dynamic SQL works?
  • 69. Re: Dynamic SQL without using SQL
    Hoek Guru
    Currently Being Moderated
    Very interesting thread.
    Maybe....*maybe*....I would run a test with CURSOR_SHARING=FORCE for this one, just to find out if that would make a difference. Only for that specific session, ofcourse...
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5180609822543
  • 70. Re: Dynamic SQL without using SQL
    padders Pro
    Currently Being Moderated
    Agree the OP raises a really good question but personally I don't think he has gone on to explain his requirements very well.
  • 71. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Yes there are bind variables in the numerous formulae stored in the database, which are replaced in a loop in the program by literals, so maybe I could juggle that around so the bind variables are retained and the loop instead strings together a USING clause.
  • 72. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    The OP? Other Person? Me?
    Could you be a little less familiar, we haven't even met!
  • 73. Re: Dynamic SQL without using SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    OP is a standard abbreviation for "original poster". If you set a handle for yourself rather than the numeric 970779, people would probably use that.

    Justin
  • 74. Re: Dynamic SQL without using SQL
    Hoek Guru
    Currently Being Moderated
    Hence my remark regarding cursor_sharing.
    You could just set it to FORCE (Oracle will then more or less 'auto-bind', you'll see :SYS_B0 etc. variables appear in the execution plans if you trace/tkprof that session ) for the session that does the processing, and see if it performs better.
    It should result in (much) less parses and/or flooding of your shared pool.
    You can test it quickly and easily:
    SQL> sho parameter sharing
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing                       string      EXACT
    SQL> alter session set cursor_sharing='FORCE';
    
    Session altered.
    
    SQL> run_your_program_here...trace it or measure timing and compare duration
    
    SQL> alter session set cursor_sharing='EXACT';
    
    Session altered.
    
    SQL> 

Legend

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