9 Replies Latest reply on Sep 27, 2017 2:14 PM by James Su

    Binding variable in dynamic sql

    KK23

      Hi All,

       

      We have requirement , below block i try to execute using dynamic sql by binding variable with like operator produces null result.

       

      Query

       

      Ananymous Block;

      DECLARE

        I_TBL_NAME VARCHAR2(100) := 'T_PBAMLGLOBUSSPRING';

        V_QUERY    VARCHAR2(4000) := '  SELECT MAX(SQL.SQL_ID)

            FROM V$SQL SQL, V$SESSION SES

           WHERE SQL.SQL_ID = SES.SQL_ID';

        V_SQLID    VARCHAR2(100);

      BEGIN

       

       

        V_QUERY := V_QUERY || '  AND SQL.PARSING_SCHEMA_NAME = SYS_CONTEXT(''userenv'', ''CURRENT_SCHEMA'')

             AND SES.SCHEMANAME = SYS_CONTEXT(''userenv'', ''CURRENT_SCHEMA'')

             AND SES.STATUS = ''ACTIVE''';

        V_QUERY := V_QUERY ||

                   ' AND UPPER(SQL.SQL_FULLTEXT) LIKE ''%''||:1||''%''';

        IF I_TBL_NAME IS NOT NULL THEN

          EXECUTE IMMEDIATE V_QUERY

            INTO V_SQLID

            USING I_TBL_NAME;

        END IF;

        DBMS_OUTPUT.PUT_LINE('sql id:' || V_SQLID);

      EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE(' ERROR  : ' || SQLERRM || 'ERROR CODE :' ||

                               SQLCODE);

       

      END;

       

      if i try to execute above block , it doesn't product the output , i suspect like operator with single quotes binding cause empty resultset. Kindly advise how can i bind the single quotes value.

       

        • 1. Re: Binding variable in dynamic sql
          KayK

          Hi,

          why do you want use a bind variable ?

          Try something like this

          ...
            V_QUERY := V_QUERY || '  AND UPPER(SQL.SQL_FULLTEXT) LIKE ''%' || I_TBL_NAME || '%''';
            --
            DBMS_OUTPUT.PUT_LINE (V_QUERY);
            IF I_TBL_NAME IS NOT NULL THEN
              EXECUTE IMMEDIATE V_QUERY
                INTO V_SQLID;
          ...
          

           

          regards
          Kay

          • 2. Re: Binding variable in dynamic sql
            KK23

            Thanks KayK, Its working

            • 3. Re: Binding variable in dynamic sql
              BEDE

              Really strange thing selecting MAX(SQL.SQL_ID). And this max(sql_id) may well belong to some session that has nothing to do with SYS_CONTEXT(''userenv'', ''CURRENT_SCHEMA'').

              I can't imagine why max(sql_id). I used to look for something where sql_text like some stuff. Or to look in v_$session_longops. But can't figure out what you want to do with max(sql_id).

              just run the select below:

              select max(t.sql_id) over(partition by t.schemaname) max_per_schema, max(t.sql_id) over (partition by 1) max_per_all, t.*

              from sys.v_$session t

               

              and so you may have some idea on what the data really is at a given moment.

              • 4. Re: Binding variable in dynamic sql
                Billy~Verreynne

                Bind variables are CRITICAL for performance.

                 

                Binding in PL/SQL is by position by default.

                 

                E.g.

                SQL> declare
                  2          sqlSelect       varchar2(100);
                  3          jobName         varchar2(10);
                  4          maxSal          integer;
                  5  begin
                  6          -- construct SQL with bind variables
                  7          sqlSelect := 'select max(sal) from emp where job = upper(:j)';
                  8
                  9          -- setup value for input bind variable
                 10          jobName := 'clerk';
                 11
                 12          -- execute SQL
                 13          execute immediate sqlSelect
                 14                  into maxSal     -- output variable for fetching SQL projection
                 15          using
                 16                  in jobName;     -- input variable(s)
                 17
                 18          -- trace output
                 19          dbms_output.put_line( 'sql> '||sqlSelect );
                 20          dbms_output.put_line( 'bind-in> :j = "'||jobName||'"' );
                 21          dbms_output.put_line( 'projection> '||maxSal );
                 22  end;
                 23  /
                sql> select max(sal) from emp where job = upper(:j)
                bind-in> :j = "clerk"
                projection> 1300
                

                 

                 

                Without bind variables, no shareable SQL cursors are created, and a lot of server CPU will be burnt on soft parsing that fails (through an ever increasing shared pool), forcing expensive hard parsing in turn.

                • 5. Re: Binding variable in dynamic sql
                  BluShadow

                  KayK wrote:

                   

                  Hi,

                  why do you want use a bind variable ?

                  Try something like this

                  1. ...
                  2. V_QUERY:=V_QUERY||'ANDUPPER(SQL.SQL_FULLTEXT)LIKE''%'||I_TBL_NAME||'%''';
                  3. --
                  4. DBMS_OUTPUT.PUT_LINE(V_QUERY);
                  5. IFI_TBL_NAMEISNOTNULLTHEN
                  6. EXECUTEIMMEDIATEV_QUERY
                  7. INTOV_SQLID;
                  8. ...

                   

                  regards
                  Kay

                   

                   

                  Kay, I'm surprised at you.  That's not good advice as that prevents the correct use of bind variables.

                   

                  The problem for the OP was the bad usage of quotes in their dynamic string (which they would have been able to tell if they output their query to see what it looked like)

                   

                  KK23 Billy's advice is spot on.

                  Also take a read of the following community document, to understand the benefits of using bind variables: PL/SQL 101 : Substitution vs. Bind Variables

                  • 6. Re: Binding variable in dynamic sql
                    KayK

                    BluShadow wrote:

                    Kay, I'm surprised at you. That's not good advice as that prevents the correct use of bind variables.

                    Full acknowledge.

                     

                    But i for myself wasn't able to this with a bind variable.

                     

                    A more simple procedure works with bind variable.

                    DECLARE
                      I_TBL_NAME VARCHAR2(100) := '%AKT_SEG_WORK%';
                      V_QUERY    VARCHAR2(4000);
                      V_SQLID    VARCHAR2(100);
                    BEGIN
                      V_QUERY := V_QUERY || 'SELECT count(*)';
                      V_QUERY := V_QUERY || '  FROM dba_tables';
                      V_QUERY := V_QUERY || ' WHERE table_name like :1';
                      --
                      DBMS_OUTPUT.PUT_LINE (V_QUERY);
                      IF I_TBL_NAME IS NOT NULL THEN
                        EXECUTE IMMEDIATE V_QUERY
                          INTO V_SQLID
                          USING I_TBL_NAME
                        ;
                      END IF;
                      --
                      DBMS_OUTPUT.PUT_LINE('sql id : ' || V_SQLID);
                      --
                    EXCEPTION
                      WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE('error code    : ' || SQLCODE );
                        DBMS_OUTPUT.PUT_LINE('error message : ' || SQLERRM );
                    END;
                    /
                    

                     

                    But with "UPPER(SQL.SQL_FULLTEXT) LIKE :1" or something like this i can't do it.

                     

                    I don't think that this procedure is part of the daily work and will not have thousands of executions per day.

                    If this is false then we have to think over the whole execute immediate construct.

                     

                    regards
                    Kay

                    btw good to know that i'm able to surprise you ;-)

                    • 7. Re: Binding variable in dynamic sql
                      Billy~Verreynne

                      The shared pool contains 3 cursors in this case

                      - the anonymous PL/SQL block executed for running the code that creates the dynamic SQL cursor

                      - the dynamic SQL cursor

                      - the query on the shared pool looking for the cursor in the shared pool

                       

                      SQL> alter system flush shared_pool;
                      
                      System altered.
                      
                      SQL>
                      SQL> declare
                        2          sqlSelect      varchar2(100);
                        3          tableFilter    varchar2(30);
                        4          tableCount      integer;
                        5  begin
                        6          -- construct SQL with bind variables
                        7          sqlSelect := 'select count(*) from dba_tables where table_name like :filter';
                        8
                        9          -- setup value for input bind variable
                      10          tableFilter := '%EMP%';
                      11
                      12          -- execute SQL
                      13          execute immediate sqlSelect
                      14                  into tableCount
                      15          using
                      16                  in tableFilter;
                      17
                      18          -- trace output
                      19          dbms_output.put_line( 'sql> '||sqlSelect );
                      20          dbms_output.put_line( 'bind-in> :j = "'||tableFilter||'"' );
                      21          dbms_output.put_line( 'projection> '||tableCount);
                      22  end;
                      23  /
                      sql> select count(*) from dba_tables where table_name like :filter
                      bind-in> :j = "%EMP%"
                      projection> 53
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL>
                      SQL> select sql_id, sql_text from v$sql where lower(sql_text) like '%dba_tables%';
                      
                      SQL_ID        SQL_TEXT
                      ------------- ----------------------------------------------------------------------------------------------------
                      9wdnb8syzq3vm select count(*) from dba_tables where table_name like :filter
                      baqyahvuda5px declare  sqlSelect varchar2(100);  tableFilter varchar2(30);  tableCount integer; begin  -- construc
                                    t SQL with bind variables  sqlSelect := 'select count(*) from dba_tables where table_name like :filt
                                    er';  -- setup value for input bind variable  tableFilter := '%EMP%';  -- execute SQL  execute imm
                                    ediate sqlSelect  into tableCount  using  in tableFilter;  -- trace output  dbms_output.put_line(
                                    'sql> '||sqlSelect );  dbms_output.put_line( 'bind-in> :j = "'||tableFilter||'"' );  dbms_output.pu
                                    t_line( 'projection> '||tableCount); end;
                      
                      942axbkpbmcgn select sql_id, sql_text from v$sql where lower(sql_text) like '%dba_tables%'
                      
                      SQL>
                      

                       

                      You will not see the cursor with the actual bind variable value "%EMP%", if that is what you are looking for.

                      • 8. Re: Binding variable in dynamic sql
                        KayK

                        You're right, without the join it works with the bind variable.

                          V_QUERY := V_QUERY || 'SELECT MAX(SQL.SQL_ID)';
                          V_QUERY := V_QUERY || '  FROM V$SQL SQL';
                          V_QUERY := V_QUERY || ' WHERE SQL.PARSING_SCHEMA_NAME = SYS_CONTEXT(''userenv'', ''CURRENT_SCHEMA'')';
                          V_QUERY := V_QUERY || '  AND UPPER(SQL.SQL_FULLTEXT) LIKE ''%'' || :1 || ''%''';
                        

                         

                        But without the join we also can do this, no need for an execute immediate:

                         

                        DECLARE
                          I_TBL_NAME VARCHAR2(100) := 'DBA_TABLES';
                          V_SQLID    VARCHAR2(100);
                          cursor c1 ( table_in in varchar) is
                                SELECT MAX(SQL.SQL_ID)
                                  FROM V$SQL SQL  -- , V$SESSION SES
                                 WHERE SQL.PARSING_SCHEMA_NAME = SYS_CONTEXT('userenv', 'CURRENT_SCHEMA')
                                   AND UPPER(SQL.SQL_FULLTEXT) LIKE '%' || table_in || '%';
                        BEGIN
                          --
                          DBMS_OUTPUT.PUT_LINE('table  : ' || I_TBL_NAME);
                          --
                          open  c1 (I_TBL_NAME );
                          fetch c1 into V_SQLID;
                          close c1;
                          --
                          DBMS_OUTPUT.PUT_LINE('sql id : ' || V_SQLID);
                          --
                        EXCEPTION
                          WHEN OTHERS THEN
                            DBMS_OUTPUT.PUT_LINE('error code    : ' || SQLCODE );
                            DBMS_OUTPUT.PUT_LINE('error message : ' || SQLERRM );
                        END;
                        /
                        
                        • 9. Re: Binding variable in dynamic sql
                          James Su

                          The problem of OP's code is not the bad usage of quotes, it's the current sql_id in the current session does not contain that particular table name.

                           

                          .....

                          FROM V$SQL SQL, V$SESSION SES

                               WHERE SQL.SQL_ID = SES.SQL_ID

                          ........

                           

                          Here the sql being executed is V_QUERY, and in the sql_text of V_QUERY you can not see substring 'PROD_TIMINGS', because it is a place holder ':1' in this case.

                           

                          If you change :1 to literal, then yes the table name is now contained in the sql text.