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

hajduk 0
Viewed 32

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?

plsql oracle
1 Answers

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

MT00
Related