Find Bind Variable from SQL ID

set lines 500
col VALUE_STRING for a50
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='&SQL_ID';


SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address =
  (SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))
ORDER BY piece;



select * from table
( dbms_xplan.display_cursor ('sql_id',child_cursor#, 'ADVANCED'));



Run the below query which takes the sql_id as the input parameter and will give the output with replaced bind variable values.

set serveroutput on;
DECLARE
   v_fulltext   CLOB;
   v_sql_id     VARCHAR2 (100);

   CURSOR c1( v_sql_id varchar2)
   IS
      SELECT decode(substr(NAME,1,4),':SYS',replace(name,':',':"')||'"' ,NAME ) NAME, POSITION, datatype_string,nvl(VALUE_STRING,'NULL') value_string
    FROM v$sql_bind_capture
       WHERE sql_id = v_sql_id;
BEGIN

  v_sql_id:= '&sql_id';

   SELECT sql_fulltext
     INTO v_fulltext
     FROM v$sql
    WHERE sql_id =v_sql_id  AND ROWNUM = 1;

   FOR rec IN c1(v_sql_id)
   LOOP
      IF substr(rec.datatype_string,1,8) = 'VARCHAR2'
      THEN
     SELECT REPLACE (v_fulltext,
             rec.NAME,
             '''' || rec.value_string || ''''
            )
       INTO v_fulltext
       FROM DUAL;
      END IF;

      IF rec.datatype_string = 'NUMBER'
      THEN
     SELECT REPLACE (v_fulltext, rec.NAME, rec.value_string)
       INTO v_fulltext
       FROM DUAL;
      END IF;

   END LOOP;
   DBMS_OUTPUT.PUT_LINE(v_fulltext);

   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN DBMS_OUTPUT.PUT_LINE('NO SQL FOUND FOR THE SQL ID');

END;
/