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
  • 75. Re: Dynamic SQL without using SQL
    user13328581 Explorer
    Currently Being Moderated
    Never seen this before...but quite nice to utilize a mathematical calculation and assignment in such form

    Edited by: user13328581 on Mar 12, 2013 8:05 AM
  • 76. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    It seemed a good idea to leave the bind variables in the formulae instead of replacing them with literals, but alas I can't have a dynamic USING clause, to handle the varying number of variables.

    (eg

    L_USING varchar2;
    ...
    for i in 1..100 loop
    if instr(formula_text, bind_variable(i)) then
    l_using := l_using || value(i) || ',';
    end if;
    end loop;

    EXECUTE IMMEDIATE 'SELECT '|| formula_text || ' FROM DUAL'
    INTO new_adcv_val
    USING l_using;

    )
  • 77. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    970779 wrote:
    but alas I can't have a dynamic USING clause, to handle the varying number of variables.
    This is where DBMS_SQL comes in. You can bind a varying number of variables with it.

    There's a use case in my blog article I already posted at the beginning of this thread.

    http://odieweblog.wordpress.com/2013/02/03/plsql-rpn-calculator/

    See section "3. Benchmarking" / "b. Using a dynamic PL/SQL block with bind variables"
  • 78. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Thanks Hoek I tested your suggestion and it works a charm!
    What a simple solution, but how can I put an ALTER SESSION in a package run by a concurrent request?
  • 79. Re: Dynamic SQL without using SQL
    Hoek Guru
    Currently Being Moderated
    970779 wrote:
    Thanks Hoek I tested your suggestion and it works a charm!
    I was afraid it would work (ran into the same problems a decade ago)... ;)
    Keep in mind that this suggestion does not fix the real problem (using concats in Dynamic SQL), it is merely a workaround.
    Do not, never ever, change the parameter value to FORCE at instance/database level, but only for that specific session.
    What a simple solution, but how can I put an ALTER SESSION in a package run by a concurrent request?
    You can use ALTER SESSION in an EXECUTE IMMEDIATE statement as well, if you need to do it from PL/SQL.
    SQL> begin
      2    execute immediate 'alter session set cursor_sharing=''FORCE''';
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
  • 80. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Great thanks.
    I was wading through Odie's dbms_sql.bind_variable and dbms_sql.parse solution but I don't find it clear and I'm not sure what those procedures do,
    so I think your idea is easiest, thanks.
  • 81. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    Glad you've find a solution (well, "workaround") with the minimum investment from your part.

    However, from a more general point of view :
    I was wading through Odie's dbms_sql.bind_variable and dbms_sql.parse solution but I don't find it clear and I'm not sure what those procedures do
    Not clear?
    That's the basic of SQL processing in the database : OPEN / PARSE / BIND / EXECUTE / CLOSE
    If you already know about bind variables and their benefits, you should be able to understand the concept.
    If not, there's always the documentation.
1 2 3 4 5 6 Previous Next

Legend

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