oracle-sqldeveloperbind-variables

Bind variables in SQL Developer - data type

I'm trying to use bind variables in SQL Developer. The problem is that I can't set the variable to type NUMBER - it's type is always set to VARCHAR2(32):

create view test1v as select 1 as abc from dual;
VARIABLE X NUMBER;
EXEC :X := 1;
explain plan for SELECT /*TOTO7*/ * FROM test1v where rownum = :X;

enter image description here

I'm using SQL Developer 17.4.0.355

Solution

  • It is type number - at least if you run a real statement, rather than just explaining one. As a quick check, if you do:

    EXEC :X := 'X';

    then you'll get "ORA-06502: PL/SQL: numeric or value error: character to number conversion error" as expected.

    The confusion, I think, is that you're assuming the :X in the explained query is making use of the local bind variable you're declared. But you can use :1 or :Z for the explain plan and it will still work and show the same information.

    When you explain first, the bind value isn't captured and it doesn't know the bind type, so it defaults to varchar2:

    explain plan for SELECT /*ABC1*/ * FROM test1v where rownum = :X;

Explained.

select sbc.datatype_string, sbc.was_captured, sbc.value_string
from gv$sql s
join gv$sql_bind_capture sbc on sbc.sql_id = s.sql_id
where s.sql_text like '%ABC1%';

DATATYPE_STRING WAS_CAPTURED VALUE_STRING
--------------- ------------ ------------
VARCHAR2(32)    NO

    If you actually run the query, rather than just explaining it, the bind value is now captured - both versions appear:

    SELECT /*ABC1*/ * FROM test1v where rownum = :X;

       ABC
----------
         1

select sbc.datatype_string, sbc.was_captured, sbc.value_string
from gv$sql s
join gv$sql_bind_capture sbc on sbc.sql_id = s.sql_id
where s.sql_text like '%ABC1%';

DATATYPE_STRING WAS_CAPTURED VALUE_STRING
--------------- ------------ ------------
VARCHAR2(32)    NO                       
NUMBER          YES          1

    I suspect that you ran a query normally, then added a comment to be able to find its SQL ID and bind info; but in doing so you made it a different query, which was parsed separately, and the bind wasn't captured at that point. But you see the same thing if you run the query first - the number version is captured first but the explain plan version still gets a varchar2 entry.