Obtaining the trace file name from JDBC when sql tracing is enabled

When turning on SQL trace in a JDBC thin program as follows I needed to get the actual trace file name at the time.

// turn on sql_trace
stmt = conn.createStatement();
stmt.execute("alter session set sql_trace=true");
System.out.println("\nSQL_TRACE=TRUE has been started \n");

All though not difficult I found this piece of SQL that I could run to determine the trace file being used by my program, which I executed from my JDBC program prior to turning on SQL tracing, so that at the end of the program I knew exactly what trace file I needed to analyze.

final String traceSQL =
"select c.value || '/' || d.instance_name || '_ora_' || " +
"to_char(a.spid, 'fm99999') || '.trc' " +
"from v$process a, v$session b, v$parameter c, v$instance d " +
"where a.addr = b.paddr " +
"and b.audsid = userenv('sessionid') " +
"and c.name = 'user_dump_dest'";

......

public void displayTraceFileName (Connection conn)
 throws SQLException
{
 Statement stmt = conn.createStatement();
 ResultSet rset = stmt.executeQuery(traceSQL);
 rset.next();

 String fileName = rset.getString(1);
 System.out.println("TRACE FILE NAME : " + fileName);
}

Example output:

TRACE FILE NAME : /home/oracle/oracle/product/10.2.0/db_1/admin/lnx102/udump/
lnx102_ora_8462.trc

Note: If your on a windows the SQL differs slightly, as shown below.

rem
rem User must have access to v$session, v$process,
rem v$paramater and v$instance views
rem

select c.value || '\ORA' || to_char(a.spid, 'fm00000') || '.trc'
from v$process a, v$session b, v$parameter c
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest';