I am using an application that submits SQL queries to the Oracle database. These queries can have hundreds of bind variables in them. I want to be able to see the SQL that is running with the bind variables substituted.

I have used this query to get the SQL currently running:

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
SID,
MACHINE,
REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
V$SQLtext_with_newlines SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1,sql.piece;


I get back rows containing the SQL running at that time but it has bind variables it, so I don't know exactly what is running. Is there a way to see the values of the binds variables? or better still the SQL after the substitution takes place.

example of output:

SELECT s0.fiscal_period_long_at_sta_3637 AS fiscal_period_long_a
t_sta_3637, s0.facility_name AS facility_name, coalesce(sum( s0.
count_1), :1 , null) AS count_1 FROM (SELECT t0.FISCAL_PERIOD_LO
NG AS fiscal_period_long_at_sta_3637, s1.facility_name AS facili
ty_name, s1.count_1 AS count_1 FROM DENODO.DIM_DATE t0 JOIN ( SE
LECT s1.facility_name, s1.zcalc_start_date_id AS start_date_id,
count( s1.num_of_encntrs) AS count_1 FROM (SELECT coalesce(t3.FA
CILITY_NAME_AT_ED, :2 , null) AS facility_name, CASE WHEN t4.ZCA
LC_START_DT_TM_PT IS NULL THEN :3 WHEN to_char(t4.ZCALC_START_D
T_TM_PT,:4 ,:5 ) > :6 THEN :7 ELSE cast(to_char(t4.ZCALC_STAR
T_DT_TM_PT,:8 ,:9 ) as DECIMAL(10)) END AS zcalc_start_date_id,
:10 AS num_of_encntrs, :11 AS src_system_cd FROM (((((((((((((
DENODO.MV_ENCNTR_BASE t1 JOIN DENODO.MV_ENCNTR_LAST_ALIAS t2 ON
((t1.ENCNTR_ID = t2.ENCNTR_ID) )) LEFT JOIN DENODO.MV_ED_ENCNTR
t3 ON ((t1.ENCNTR_ID = t3.ENCNTR_ID) )) LEFT JOIN DENODO.MV_ENCN
TR_ADD_DT_TM_PT t4 ON ((t1.ENCNTR_ID = t4.ENCNTR_ID) )) LEFT JOI
N (SELECT t5.ARRIVAL_MODE_CD_SRC AS arrival_mode_cd_src FROM DEN
ODO.DIM_ARRIVAL_MODE t5 WHERE t5.SRC_SYSTEM_CD = :12 ) s2 ON ((C
ASE WHEN t1.ADMIT_MODE_CD > :13 THEN cast(t1.ADMIT_MODE_CD as V
ARCHAR2(4000)) ELSE :14 END = s2.arrival_mode_cd_src) )) LEFT
JOIN DENODO.MV_LOC_HIST_ED_NU_AT_ADMIT t6 ON ((t1.ENCNTR_ID = t6
.ENCNTR_ID) )) LEFT JOIN DENODO.MV_ENCNTR_LOC_HIST_AT_POST_ED t7
ON ((t1.ENCNTR_ID = t7.ENCNTR_ID) )) LEFT JOIN DENODO.MV_ENCNTR
_PRSN_HIST_AT_ED t8 ON ((t1.ENCNTR_ID = t8.ENCNTR_ID) )) LEFT JO
IN (SELECT s3.encntr_id AS encntr_id FROM (SELECT t9.ENCNTR_ID A
S encntr_id, dense_rank() OVER ( PARTITION BY t9.ENCNTR_ID ORDER
BY t9.ORIG_ORDER_DT_TM ASC, t9.STATUS_DT_TM ASC, t9.ORDER_ID AS
C ) AS first_consultation_seq FROM V500.ORDERS t9 JOIN DENODO.DI
M_SERVICE t10 ON ((cast(t9.CATALOG_CD as VARCHAR2(4000)) = t10.S
ERVICE_CD) ) WHERE t9.CATALOG_TYPE_CD = :15 AND t9.ORDER_STATUS
_CD NOT IN (:16 , :17 ) AND t9.ACTIVE_IND = :18 AND t10.SITE IN
(:19 , :20 )) s3 WHERE s3.first_consultation_seq = :21 ) s4 ON
((t1.ENCNTR_ID = s4.encntr_id) )) LEFT JOIN (SELECT s8.encntr_i
d AS encntr_id FROM (SELECT s7.encntr_id AS encntr_id, s7.zcalc_
first_ed_loc_cdu_seq AS zcalc_first_ed_loc_cdu_seq FROM (SELECT
t11.ENCNTR_ID AS encntr_id, CASE WHEN (t11.ZCALC_IS_OUTSIDE_RELE
VANT_9998 = :22 AND s6.is_loc_cdu = :23 ) THEN dense_rank() OVE
R ( PARTITION BY t11.ENCNTR_ID, t11.ZCALC_IS_OUTSIDE_RELEVANT_99
98, s6.is_loc_cdu ORDER BY t11.BEG_EFFECTIVE_DT_TM_UTC ASC ) E
ND AS zcalc_first_ed_loc_cdu_seq FROM DENODO.MV_ED_LOC_ROOM t11
LEFT JOIN (SELECT t12.ENCNTR_LOC_HIST_ID AS encntr_loc_hist_id,
coalesce(s5.is_loc_cdu, :24 , null) AS is_loc_cdu FROM DENODO.MV
_ED_LOC_ROOM t12 LEFT JOIN (SELECT t13.LOC_CD AS loc_cd, CASE WH
EN t13.IS_LOC_CDU = :25 THEN :26 ELSE :27 END AS is_loc_cdu,
t13.BEGIN_EFFECTIVE_DT_TM AS begin_effective_dt_tm, t13.END_EFF
ECTIVE_DT_TM AS end_effective_dt_tm FROM DENODO.DIM_LOC_AREA_TYP
E t13 WHERE t13.SRC_SYSTEM_CD IN (:28 , :29 )) s5 ON ((t12.LOC_R
OOM_CD = s5.loc_cd AND t12.BEG_EFFECTIVE_DT_TM_PT BETWEEN s5.beg
in_effective_dt_tm AND s5.end_effective_dt_tm) )) s6 ON ((t11.EN
CNTR_LOC_HIST_ID = s6.encntr_loc_hist_id) )) s7 ) s8 WHERE s8.z
calc_first_ed_loc_cdu_seq = :30 ) s9 ON ((t1.ENCNTR_ID = s9.encn
tr_id) )) LEFT JOIN (SELECT s11.encntr_id AS encntr_id, s11.loc_
room_id AS loc_room_id, s11.zcalc_first_ed_loc_date_id AS zcalc_
first_ed_loc_date_id FROM (SELECT s10.encntr_id AS encntr_id, s1
0.loc_room_id AS loc_room_id, CASE WHEN s10.zcalc_first_ed_loc_s
eq = :31 THEN s10.beg_effective_date_id END AS zcalc_first_ed
_loc_date_id, s10.zcalc_first_ed_loc_seq AS zcalc_first_ed_loc_s
eq FROM (SELECT t14.ENCNTR_ID AS encntr_id, t14.LOC_ROOM_ID AS l
oc_room_id, CASE WHEN (t14.ZCALC_IS_OUTSIDE_RELEVANT_9998 = :32
AND t14.IS_ED_LOC_ROOM = :33 ) THEN dense_rank() OVER ( PARTITI
ON BY t14.ENCNTR_ID, t14.ZCALC_IS_OUTSIDE_RELEVANT_9998, t14.IS_
ED_LOC_ROOM ORDER BY t14.BEG_EFFECTIVE_DT_TM_UTC ASC ) END AS
zcalc_first_ed_loc_seq, CASE WHEN t14.BEG_EFFECTIVE_DT_TM_PT IS
NULL THEN :34 WHEN to_char(t14.BEG_EFFECTIVE_DT_TM_PT,:35 ,:36
) > :37 THEN :38 ELSE cast(to_char(t14.BEG_EFFECTIVE_DT_TM_PT
,:39 ,:40 ) as DECIMAL(10)) END AS beg_effective_date_id FROM DE
NODO.MV_ED_LOC_ROOM t14) s10 ) s11 WHERE s11.zcalc_first_ed_loc
_seq = :41 ) s12 ON ((t1.ENCNTR_ID = s12.encntr_id) )) LEFT JOIN
DENODO.DIM_LOC_ROOM t15 ON ((coalesce(s12.loc_room_id, :42 , nu
ll) = t15.LOC_ROOM_ID) )) LEFT JOIN DENODO.DIM_LOC_COST_CENTER t
16 ON ((t15.NURSING_UNIT_ID = t16.NURSING_UNIT_ID AND coalesce(s
12.zcalc_first_ed_loc_date_id, :43 , null) BETWEEN t16.LOC_COST_
CENTER_START_DATE_ID AND t16.LOC_COST_CENTER_END_DATE_ID) )) LEF
T JOIN DENODO.DIM_LOC_COST_CENTER t17 ON ((coalesce(t7.FIRST_NUR
SING_UNIT_ID_POST_ED, :44 , null) = t17.NURSING_UNIT_ID AND CASE
WHEN trunc(from_tz(cast(t7.BEG_EFFECTIVE_DT_TM_UTC as timestamp
),'UTC') at time zone 'Canada/Pacific',:45 ) IS NULL THEN :46 W
HEN to_char(trunc(from_tz(cast(t7.BEG_EFFECTIVE_DT_TM_UTC as tim
estamp),'UTC') at time zone 'Canada/Pacific',:47 ),:48 ,:49 ) >
:50 THEN :51 ELSE cast(to_char(trunc(from_tz(cast(t7.BEG_EFFE
CTIVE_DT_TM_UTC as timestamp),'UTC') at time zone 'Canada/Pacifi
c',:52 ),:53 ,:54 ) as DECIMAL(10)) END BETWEEN t17.LOC_COST_CEN
TER_START_DATE_ID AND t17.LOC_COST_CENTER_END_DATE_ID) )) LEFT J
OIN DENODO.DIM_LOC_COST_CENTER t18 ON ((coalesce(t6.ED_NURSING_U
NIT_ID_AT_ORD_1402, :55 , null) = t18.NURSING_UNIT_ID AND CASE W
HEN t4.FIRST_ORDER_TO_ADMIT_DT_TM_PT IS NULL THEN :56 WHEN to_c
har(t4.FIRST_ORDER_TO_ADMIT_DT_TM_PT,:57 ,:58 ) > :59 THEN :60
ELSE cast(to_char(t4.FIRST_ORDER_TO_ADMIT_DT_TM_PT,:61 ,:62 )
as DECIMAL(10)) END BETWEEN t18.LOC_COST_CENTER_START_DATE_ID AN
D t18.LOC_COST_CENTER_END_DATE_ID) ) WHERE :63 = :64 AND :65
= :66 AND CASE WHEN (t1.DISCH_DISP_CD = :67 AND (t1.ADMIT_DT_T
M_UTC + :68 /(24*60)) = t1.DISCH_DT_TM_UTC) THEN :69 ELSE :70
END = :71 AND t2.ZCALC_IS_TEST_PATIENT_MRN = :72 AND t2.ZCALC
_IS_MISSING_ENCNTR_NUM = :73 AND coalesce(t3.ZCALC_IS_ED, :74 ,
null) = :75 ) s1 GROUP BY s1.facility_name, s1.zcalc_start_date
_id) s1 ON ((t0.DATE_ID = s1.start_date_id) ) WHERE t0.FISCAL_PE
RIOD_LONG >= :76 ) s0 GROUP BY s0.fiscal_period_long_at_sta_3637
, s0.facility_name ORDER BY NLSSORT(fiscal_period_long_at_sta_36
37, 'NLS_SORT = binary') ASC, facility_name ASC