Variable / Literal Cursor Replacement PL / SQl?

Question

I often have to debug cursors in Oracle PL / SQL. My problem is that in the end I get some high-dimensional strings with large cursors like 50+ variables and constants. I am looking for a way to get a statement version where constants and variables are replaced with their literals. If I want to find out why the cursor does not show a record / line, I have to finish replacing these variables / literals for 30 minutes before I can run the selection and comment on some of the statements to find out what happened.

So, if I have something like

CURSOR cFunnyCursor (
  v1 NUMBER,
  v2 NUMBER
) IS
SELECT * FROM TABLE
WHERE  col1  = v1
AND    col2 != v2
AND    col3  = CONSTANT;

I need a SELECT as follows:

SELECT * FROM TABLE
WHERE  col1  = 123
AND    col2 != 5324
AND    col3  = 'ValueXyz';

/ SELECT , SQL, 30 ? ( -, , , ).

Answer 1

, , - sql , : . Toad, , , . , , , .

:.

SELECT * FROM TABLE
WHERE  col1  = v1
AND    col2 != v2
AND    col3  = CONSTANT;

SELECT * FROM TABLE
WHERE  col1  = :v1
AND    col2 != :v2
AND    col3  = :CONSTANT;

Answer 2

, GV $ SQL_BIND_CAPTURE. Oracle bind , .

:

create or replace function get_sql_with_literals(p_sql_id varchar2) return clob authid current_user is
/*
    Purpose: Generate a SQL statement with literals, based on values in GV$SQL_BIND_CAPTURE.
        This can be helpful for queries with hundreds of bind variables (or cursor sharing),
        and you don't want to spend minutes manually typing each variable.
*/
    v_sql_text clob;
    v_names sys.odcivarchar2list;
    v_values sys.odcivarchar2list;
begin
    --Get the SQL_ID and text.
    --(Use dynamic SQL to simplify privileges.  Your user must have access to GV$ views,
    -- but you don't need to have them directly granted to your user, role access is fine.)
    execute immediate
    q'[
        select sql_fulltext
        from gv$sql
        --There may be multiple rows, for clusters or child cursors.
        --Can't use distinct with CLOB SQL_FULLTEXT, but since the values will be the same
        --we can pick any one of the rows.
        where sql_id = :p_sql_id
            and rownum = 1
    ]'
    into v_sql_text
    using p_sql_id;

    --Get bind data.
    execute immediate
    q'[
        select
            name,
            --Convert to literals that can  be plugged in.
            case
                when datatype_string like 'NUMBER%' then nvl(value_string, 'NULL')
                when datatype_string like 'VARCHAR%' then '''' || value_string || ''''
                when datatype_string like 'DATE%' then 'to_date('''||value_string||''', ''MM/DD/YYYY HH24:MI:SS'')'
                --TODO: Add more types here
            end value
        from
        (
            select
                datatype_string,
                --If CURSOR_SHARING=FORCE, literals are replaced with bind variables and use a different format.
                --The name is stored as :SYS_B_01, but the actual string will be :"SYS_B_01".
                case
                    when name like ':SYS_%' then ':"' || substr(name, 2) || '"'
                    else name
                end name,
                position,
                value_string,
                --If there are multiple bind values captured, only get the latest set.
                row_number() over (partition by name order by last_captured desc, address) last_when_1
            from gv$sql_bind_capture
            where sql_id = :p_sql_id
        )
        where last_when_1 = 1
        --Match longest names first to avoid matching substrings.
        --For example, we don't want ":b1" to be matched to ":b10".
        order by length(name) desc, position
    ]'
    bulk collect into v_names, v_values
    using p_sql_id;

    --Loop through the binds and replace them.
    for i in 1 .. v_names.count loop
        v_sql_text := replace(v_sql_text, v_names(i), v_values(i));
    end loop;

    --Return the SQL.
    return v_sql_text;
end;
/

:

Oracle . , . , , .

alter system flush shared_pool;

SQL_ID. , , SQL.

select *
from gv$sql
where lower(sql_fulltext) like lower('%unique_string%')
    and sql_fulltext not like '%quine%';

, SQL , . , SQL . . , -, PL/Scope , , . , IDE .

select get_sql_with_literals(p_sql_id => '65xzbdjubzdqz') sql
from dual;

:

, . , . , , .

drop table test1 purge;
create table test1(col1 number, col2 varchar2(100), col3 date);

create or replace procedure test_procedure is
    C_Constant constant date := date '2000-01-01';
    v_output1 number;
    v_output2 varchar2(100);
    v_output3 date;

    CURSOR cFunnyCursor (
      v1 NUMBER,
      v2 VARCHAR2
    ) IS
    SELECT /*+ unique_string_1 */ * FROM TEST1
    WHERE  col1  = v1
    AND    col2 != v2
    AND    col3  = C_CONSTANT;
begin
    open cFunnyCursor(3, 'asdf');
    fetch cFunnyCursor into v_output1, v_output2, v_output3;
    close cFunnyCursor;
end;
/

begin
    test_procedure;
end;
/

select *
from gv$sql
where lower(sql_fulltext) like lower('%unique_string%')
    and sql_fulltext not like '%quine%';

:

select get_sql_with_literals(p_sql_id => '65xzbdjubzdqz') sql
from dual;

SQL
---
SELECT /*+ unique_string_1 */ * FROM TEST1 WHERE COL1 = 3 AND COL2 != 'asdf' AND COL3 = to_date('01/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS') 

Answer 3

, Dynamic SQL . , .
, .

DECLARE
vString  VARCHAR2 (32000);
vResult  sys_refcursor;
BEGIN
vString := 
     'SELECT * FROM table   
       WHERE col1 = '|| v1|| ' 
         AND col2 != '|| v2|| ' 
         AND col3 = '|| v;

OPEN vResult FOR vString;

DBMS_OUTPUT.put_line (vString);
END;

, . Cursor Dynamic SQL.

Answer 4

SYS_REFCURSOR, SYS_REFCURSOR .

If you run this snippet in Toad, you will be asked to define the: out variable in the pop-up window: just select Direction: OUT / Type: CURSOR, and the dataset will be displayed on the Datasheet tab.

declare
  l_refcur   sys_refcursor;
  v1         varchar2(4) := 'v1'; 
  v2         varchar2(4) := 'v2'; 
  c_constant varchar2(4) := 'X';
begin
  open l_refcur for
    SELECT * FROM dual
     WHERE dummy  = c_CONSTANT;
  :out := l_refcur;
end;

Other SQL IDEs must also support this feature.