2

Dear Techies,

Our application triggers queries like below very frequently.

select name,emp_id,prod_id,prod_name, .... from appuser.table where emp_id=:1 and prod_id=:2;

We usually spend ample amount of time finding the SQL_ID when we receive the problematic SQL_TEXT. We have an option in SQL*Plus for variables like emp_id, prodnum, etc.. as below

VARIABLE emp_id NUMBER; EXEC :emp_id := 101;

However, we have :1, :2 as bind variables name which can't be set before running the SQL as these are mere number (although treated as bind variables by Oracle). We can't ask Application vendor to rebuild all queries removing these numbered bind variables.

So, I was looking for any of the below option in SQL*Plus:

  1. How to declare/define such bind variables (:1, :2, etc...) before running the SQL?
  2. Can we bypass in any way the bind values and send this SQL to cursor cache in Oracle? Looks to be difficult but still wanted to give a try asking.
  3. Can we pass the values of these bind variables (:1, :2, etc..) during runtime as we do in Toad & SQL developer? This way we can track the correct SQL_ID from the cursor (v$sql).

I have been trying and searching for various options but didn't get specific to mine. Any help in this regard would be greatly appreciated. Any version of Oracle database which addresses this concern would be fine.

Mubasheer Ahmed
  • 71
  • 2
  • Can you add some information about why you want to find the bind variables? If you want to troubleshoot bad application logic, then tracing the session might be the best solution to finding all the variables. If you want to find the bind variables for slow SQL statements, then my answer [here](https://stackoverflow.com/a/34128582/409172) might help. – Jon Heller May 17 '20 at 04:39
  • The question is not clear. Are you asking for a way to prompt and inject the values into the SQLPLus script without having to manually amend it each time? Or are you asking for a way to check the bind variable values used each time the query is executed from the SQL History ? – TenG May 17 '20 at 10:55

0 Answers0