Skip to Main Content
  • Questions
  • ora-01008, what is the bind variable's name?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Egop.

Asked: July 05, 2016 - 5:30 am UTC

Last updated: July 05, 2016 - 4:54 pm UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Good time of day, Tom!

I run several SQL via DBMS_sql package. Each of that SQL has a set of bind variables.
Is there any feature to get a list of variables' names for given SQL?

For instance.
I wonder to get a list of ':v_name',':p_result' for the next statement:

select *
from dual
where :p_result = :v_name

With respect!

and Chris said...

v$sql_bind_capture has the name and position of the bind variables in your SQL. It also samples the values you pass when running the statement (provided statistics_level is typical or all):

var x number;
var y number; 

select /* binds */* from dual
where  1 = :x
and    2 = :y;

select s.sql_text, b.name, b.position, b.value_string from v$sql_bind_capture b
join   v$sql s
on     s.sql_id = b.sql_id
where  s.sql_text like '%binds%'
and    s.sql_text not like '%not this%';

SQL_TEXT                                                   NAME  POSITION  VALUE_STRING  
select /* binds */* from dual where  1 = :x and    2 = :y  :X    1         NULL          
select /* binds */* from dual where  1 = :x and    2 = :y  :Y    2         NULL


http://docs.oracle.com/database/121/REFRN/GUID-D353F4BE-5943-4F5B-A99B-BC9505E9579C.htm#REFRN30310

Though if you're using DBMS_SQL and you've missed some variables, the statement won't appear in v$sql_bind_capture:

declare
  cur pls_integer;
  rws pls_integer;
  stmt varchar2(100) := 'select /* not_bound */* from dual where 1 = :x and 2 = :y';
begin
  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, stmt, dbms_sql.native);
  dbms_sql.bind_variable(cur, ':x', 1);
  rws := dbms_sql.execute(cur);
  dbms_sql.close_cursor(cur);
end;
/

ORA-01008: not all variables bound
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at line 9

select s.sql_text, b.name, b.position, b.value_string from v$sql_bind_capture b
join   v$sql s
on     s.sql_id = b.sql_id
where  s.sql_text like '%not_bound%'
and    s.sql_text not like '%not this%';

no rows selected


Surely you can inspect the code to find what all the bind variables are?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It's expected

A reader, July 05, 2016 - 2:03 pm UTC

Thank you for your answer.
As I expected there is no feature to get a list of variables' names for an SQL text.
It's a pity. And it's incomprehensibly.
I believe the list exists inside DBMS_sql after parsing SQL text but Oracle does not provide access to it.
Chris Saxon
July 05, 2016 - 4:54 pm UTC

Well after parsing it'll be in v$sql:

declare
  cur pls_integer;
  rws pls_integer;
  stmt varchar2(100) := 'select /* blahblah */* from dual where 1 = :x';
begin
  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, stmt, dbms_sql.native);
  dbms_sql.bind_variable(cur, ':x', 1);
 
end;
/

select s.sql_text from v$sql s
where  s.sql_text like '%blahblah%'
and    s.sql_text not like '%not this%'
and    s.sql_text not like 'declare%';

SQL_TEXT                                       
select /* blahblah */* from dual where 1 = :x  


You can then inspect it manually to find the binds. But I'll say again - you're using DBMS_SQL, so it's your code. Surely you can look at it to see what the binds are?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library