6 Replies Latest reply on Mar 7, 2018 10:56 AM by vansul

    Replace binds

    3134376

      Hi,

       

      I was searching google and found few scripts for my purpose but these are not working properly in sql developer and I am not good with sql * plus. Sample scripts are attached here.@

       

      I want a query where I can pass a sql_id and in turn it will fetch bind variable values and provide me a executable statement replacing bind with actual bind values.

       

      Kindly advise how I can modify these scripts to see actual statement to execute using a query only.

        • 1. Re: Replace binds
          3134376

          How I can attach file here, I am not able to find attach option ??

          • 2. Re: Replace binds
            John Thorton

            3134376 wrote:

             

            How I can attach file here, I am not able to find attach option ??

            Just paste the SQL inline.

             

            How does the dumb code know from where to fetch the correct bind values?

             

            How do I ask a question on the forums?

            • 3. Re: Replace binds
              Frank Kulash

              Hi,

              3134376 wrote:

               

              ...

              I want a query where I can pass a sql_id and in turn it will fetch bind variable values and provide me a executable statement replacing bind with actual bind values.

              ...

              I'm not sure what you mean by "replacing bind with actual bind values".  I suppose it's a SQL Developer feature, so I'll move this thread to the SQL Developer Forum.

               

              Post your scripts (or short, simplified versions that have the same problem) right in this forum.  Not everyone can or will open attachments.

              • 4. Re: Replace binds
                3134376

                -------------------------------------------------------------------------------------------------------

                --

                -- File name:   build_bind_vars.sql

                --

                -- Purpose:     Build SQL*Plus test script with variable definitions

                --

                -- Author:      Jack Augustin and Kerry Osborne

                --

                -- Description: This script creates a file which can be executed in SQL*Plus. It creates bind variables,

                --              sets the bind variables to the values stored in V$SQL_PLAN.OTHER_XML, and then executes

                --              the statement. The sql_id is used for the file name and is also placed in the statement

                --              as a comment. Note that numeric bind variable names are not permited in SQL*Plus, so if

                --              the statement has numberic bind variable names, they have an 'N' prepended to them. Also

                --              note that CHAR variables are converted to VARCHAR2.

                --

                -- Usage:       This scripts prompts for two values.

                --

                --              sql_id:   this is the sql_id of the statement you want to duplicate

                --

                --              child_no: this is the child cursor number from v$sql

                --                        (the default is 0 second)

                --

                -- http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/

                -------------------------------------------------------------------------------------------------------

                set sqlblanklines on

                set trimspool on

                set trimout on

                set feedback off;

                set linesize 255;

                set pagesize 50000;

                set timing off;

                set head off

                --

                accept sql_id char prompt "Enter SQL ID ==> "

                accept child_no char prompt "Enter Child Number ==> " default 0

                var isdigits number

                var bind_count number

                var   cn     number;

                col sql_fulltext for a140 word_wrap

                --

                --

                spool D:\Scripts\spool\&&sql_id.sql

                begin

                 

                 

                --

                -- Check for Bind Variables

                --

                select count(*) into :bind_count

                from

                V$SQL_BIND_CAPTURE

                where sql_id = '&&sql_id';

                 

                 

                --

                --Check for numeric bind variable names

                --

                if :bind_count > 0 then

                select case regexp_substr(replace(name,':',''),'[[:digit:]]') when replace(name,':','') then 1 end into :isdigits

                from

                V$SQL_BIND_CAPTURE

                where

                sql_id='&&sql_id'

                and child_number = &&child_no

                and rownum < 2;

                end if;

                end;

                /

                --

                -- Create variable statements

                --

                select

                case when :bind_count > 0 then

                   'variable ' ||

                   case :isdigits when 1 then replace(name,':','N') else substr(name,2,30) end || ' ' ||

                   replace(datatype_string,'CHAR(','VARCHAR2(')

                else null end txt

                from

                V$SQL_BIND_CAPTURE

                where

                sql_id='&&sql_id'

                and child_number = &&child_no;

                --

                -- Set variable values from V$SQL_PLAN

                --

                select case when :bind_count > 0 then 'begin' else '-- No Bind Variables' end txt from dual;

                select

                case when :bind_count > 0 then

                   case :isdigits when 1 then replace(bind_name,':',':N') else bind_name end ||

                   ' := ' ||

                --   case when bind_type = 1 then '''' when bind_type = 12 then 'to_date(''' else null end ||

                --   case when bind_type = 1 then display_raw(bind_data,'VARCHAR2')

                --        when bind_type = 2 then display_raw(bind_data,'NUMBER')

                --        when bind_type = 2 then

                --           to_char(dbms_stats.convert_raw_value(bind_data, :cn))

                --        when bind_type = 12 then display_raw(bind_data,'DATE')

                               case when bind_type =  1 then UTL_RAW.CAST_TO_VARCHAR2(bind_data)

                                    when bind_type =  2 then TO_CHAR(UTL_RAW.CAST_TO_NUMBER(bind_data))

                                    when bind_type = 12 then TO_CHAR(TO_DATE(TO_CHAR(TO_NUMBER(SUBSTR(CAST(bind_data AS VARCHAR2(30)),  1, 2), 'xx') - 100, 'FM00')  ||

                                                                             TO_CHAR(MOD(TO_NUMBER(SUBSTR(CAST(bind_data AS VARCHAR2(30)), 3, 2), 'xx'), 100), 'FM00') ||

                                                                             TO_CHAR(TO_NUMBER(SUBSTR(CAST(bind_data AS VARCHAR2(30)),  5, 2), 'xx'), 'FM00') ||

                                                                             TO_CHAR(TO_NUMBER(SUBSTR(CAST(bind_data AS VARCHAR2(30)),  7, 2), 'xx'), 'FM00'),

                                                                             'YYYYMMDD'),

                                                                     'DD-MON-YYYY')

                   else bind_data end ||

                   case when bind_type = 1 then '''' when bind_type = 12 then ''')' else null end ||

                   ';' || '-- '||bind_type

                else null end txt

                from (

                select

                extractvalue(value(d), '/bind/@nam') as bind_name,

                extractvalue(value(d), '/bind/@dty') as bind_type,

                extractvalue(value(d), '/bind') as bind_data

                from

                xmltable('/*/*/bind'

                passing (

                select

                xmltype(other_xml) as xmlval

                from

                v$sql_plan

                where

                sql_id like nvl('&&sql_id',sql_id)

                and child_number = &&child_no

                and other_xml is not null

                )

                ) d

                )

                ;

                select case when :bind_count > 0 then 'end;' else null end txt from dual;

                select case when :bind_count > 0 then '/' else null end txt from dual;

                --

                -- Generate statement

                --

                select regexp_replace(sql_fulltext,'(select |SELECT )','select /* test &&sql_id */ ',1,1) sql_fulltext from (

                select case :isdigits when 1 then replace(sql_fulltext,':',':N') else sql_fulltext end ||';' sql_fulltext

                from v$sqlarea

                where sql_id = '&&sql_id');

                spool off;

                -- ed &&sql_id\.sql

                undef sql_id

                undef child_no

                set feedback on;

                set head on

                • 5. Re: Replace binds
                  Gaz in Oz

                  <random number> wrote>

                   

                  Hi,

                   

                  I was searching google and found few scripts for my purpose but these are not working properly in sql developer and I am not good with sql * plus. Sample scripts are attached here.@

                   

                  I want a query where I can pass a sql_id and in turn it will fetch bind variable values and provide me a executable statement replacing bind with actual bind values.

                   

                  Kindly advise how I can modify these scripts to see actual statement to execute using a query only.

                  The script you posted contains a header that implies it was written to be run in sqlplus.

                  I would suggest learning how to use sqlplus, it is actually very simple to use.

                  An alternative to sqlplus is sqlcl, a commnad utility, like sqlplus, with more features.

                  • 6. Re: Replace binds
                    vansul

                    select count(*) into :bind_count

                    from

                    V$SQL_BIND_CAPTURE

                    where sql_id = '&&sql_id';