1 2 3 4 5 6 Previous Next 81 Replies Latest reply: Mar 12, 2013 11:22 AM by odie_63 Go to original post RSS
      • 75. Re: Dynamic SQL without using SQL
        user13328581
        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
          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
            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
              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
                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
                  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
                    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