CODEWITHSUNDEEP
oracle-databaseplsql
hajduk
hajduk

Reputation: 121

What is the safe way to find bind variables in a query

I want to check if a query has bind variables with specified names. As I have a procedure, which process queries. These queries can have bind variables. Dependend on a bind variable name I wish to bind different values. I can use for example INSTR method like below:

IF INSTR(mySELECT, ':n1') > 0 THEN 
    DBMS_SQL.BIND_VARIABLE (myCursorId, 'n1', n1);
END IF;
IF INSTR(mySELECT ,':n2') > 0 THEN
    DBMS_SQL.BIND_VARIABLE (myCursorId, 'n2', n2);
END IF;

But I think it is not the best way of doing it. Can someone give me a better one?

Upvotes: 2

Views: 684

Answers (2)

Chris Saxon
Chris Saxon

Reputation: 9875

DBMS_SQL.BIND_VARIABLE raises ORA-01006: bind variable does not exist if the bind is missing. So you can call the function and trap the exception if it's likely the bind variables are not present:

declare
  cur      number;
  sql_stmt varchar2(100);
  res      number;
  
  procedure bind ( bind_name varchar2, bind_value int ) as
    non_existent_bind exception;
    pragma exception_init ( non_existent_bind, -1006 );
  begin
    dbms_sql.bind_variable ( cur, bind_name, bind_value );
  exception when non_existent_bind then 
    dbms_output.put_line ( 'Missing bind ' || bind_name );
  end bind;
  
begin
  cur := dbms_sql.open_cursor;
  sql_stmt := q'!select ':this_bind_does_not_exist', :n1 from dual where 1 = :n2!';
  dbms_sql.parse ( cur, sql_stmt, dbms_sql.native );
  
  bind ( 'n1', 1 );
  bind ( 'n2', 2 );
  bind ( 'this_bind_does_not_exist', 3 );
  
  res := dbms_sql.execute ( cur );
  dbms_sql.close_cursor ( cur );
end;
/

Missing bind this_bind_does_not_exist

Using instr or similar is unsafe; a matching string could exist elsewhere in the statement, e.g. as a literal.

That said, the question suggests you have dynamic SQL that adds binds as necessary. Instead of trapping the exception you could keep track of which binds have been added, for example:

declare
  ...
  has_n1   boolean := false;
begin
    ...
  if n1 is not null then
    sql_stmt := sql_stmt || ' and c1 = :n1';
    has_n1   := true;
  end if;
  ...
  dbms_sql.parse ( cur, sql_stmt, dbms_sql.native );
  ...
  if has_n1 then
    dbms_sql.bind_variable ( cur, 'n1', n1 );
  end if;
  ...

Or always include the bind in the statement, with an always true comparison when you want to ignore it:

if n1 is not null then
  sql_stmt := sql_stmt || ' and c1 = :n1';
else
  sql_stmt := sql_stmt || ' and :n1 is null';
end if;
...
dbms_sql.parse ( cur, sql_stmt, dbms_sql.native );
...
dbms_sql.bind_variable ( cur, 'n1', n1 );

Upvotes: 2

MT0
MT0

Reputation: 168470

I want to check if a query has bind variables.

If you just want to check if the query has bind variables then execute it and catch the exception if there are variables that are not bound:

DECLARE
  NOT_ALL_VARIABLES_BOUND EXCEPTION;
  PRAGMA EXCEPTION_INIT(NOT_ALL_VARIABLES_BOUND, -1008);
BEGIN
  EXECUTE IMMEDIATE 'SELECT :n1 FROM DUAL';
  DBMS_OUTPUT.PUT_LINE('No bind variables');
EXCEPTION
  WHEN NOT_ALL_VARIABLES_BOUND THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Outputs:

ORA-01008: not all variables bound

and

DECLARE
  NOT_ALL_VARIABLES_BOUND EXCEPTION;
  PRAGMA EXCEPTION_INIT(NOT_ALL_VARIABLES_BOUND, -1008);
BEGIN
  EXECUTE IMMEDIATE 'SELECT '':n1'' FROM DUAL';
  DBMS_OUTPUT.PUT_LINE('No bind variables');
EXCEPTION
  WHEN NOT_ALL_VARIABLES_BOUND THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Outputs:

No bind variables

fiddle

Upvotes: 2

Related Questions