Is it possible to bind/check if bind variable occurs in the query?-CodePudding

I have some queries:

SELECT col1 FROM TABLE1;
SELECT col1 FROM TABLE1 where t1_id = :num1;
SELECT col1 FROM TABLE1 where t1_date = :date1;

I'm getting one query from a table and process it with dbms_sql.native. So as you see sometimes there is a bind variable, sometimes not. I wonder if it is possible to have a "general handling" for binding variables. Something like:

IF :num1 occur in the query THEN 
    DBMS_SQL.BIND_VARIABLE (nCursorId, ':num1', 12); 
END IF;
IF :date occur in the query THEN 
    DBMS_SQL.BIND_VARIABLE (nCursorId, ':date1', to_date('03032022', 'ddmmyyyy')); 
END IF;

The snippets are simplified. I can for example parse a query and use regexp to find it out. But I wonder if there is a better solution for that.

CodePudding user response：

Unfortunately - No.

For reference.

Native dynamic SQL (EXECUTE IMMEDIATE) is an alternative to DBMS_SQL that lets you place dynamic SQL statements, PL/SQL blocks, and PL/SQL procedure and function calls directly into PL/SQL blocks. In most situations, native dynamic SQL is easier to use and performs better than DBMS_SQL. However, native dynamic SQL itself has certain limitations, such as there being no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs). Also, there are some tasks that can only be performed using DBMS_SQL.