In this Document
  Goal
  Solution

Applies to:

Goal

This sample illustrates how to set V$SESSION properties upon connection using the JDBC Thin driver.
The sample queries the table to confirm that the property has been set.

Note:  The JDBC driver cannot correctly retrieve the values of some V$SESSION properties on its own.  Specifically, the driver exhibits the following behavior:

• When querying the TERMINAL field of the V$SESSION table using the JDBC Thin driver, the value returned is "unknown".
• When querying the PROCESS field of the V$SESSION table using the JDBC Thin driver, the value returned is "1234".  The driver returns the process ID as "1234" by default, since obtaining a process ID is not possible using the JDBC Thin driver.

Therefore, for an appropriate terminal name or process ID to be returned to the JDBC Thin driver, these properties need to be populated using the method described below.

This specified example sets and queries the PROGRAM field of the V$SESSION table; the TERMINAL or PROCESS fields of this table can be set from this example by uncommenting the appropriate lines of code.

This same syntax can be used to set any field in the V$SESSION table.

Solution

1. Create a new java class "SessionProps.java" with following contents:
   
   /*
 * This sample shows how to set V$SESSION values upon
 * connection using the JDBC Thin drdiver.  This sample
 * specifically illustrates setting the PROGRAM property of the
 * V$SESSION table.  The sample then queries the PROGRAM
 * field to verify that the field has been set as specified.
 *
 * To test setting the TERMINAL or PROCESS properties of the
 * V$SESSION table, uncomment out the appropriate lines as
 * shown below.
 *
 * The same syntax can be used to set any field in the V$SESSION
 * table.
 *
 */

// Import the java.sql package
import java.sql.*;

class SessionProps
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    java.util.Properties props = new java.util.Properties();

    // Leave uncommented the appropriate line for the property you want to set
    // props.put("v$session.terminal", "My PC!");
    // props.put("v$session.process", "7789"); 
    props.put("v$session.program", "My program!");

    // Connect to the database using driver manager
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:thin:scott/tiger@server:port:sid",props);

    // Connect to database using a datasource.
    // OracleDataSource ods = new OracleDataSource(); 
    // ods.setURL("jdbc:oracle:thin:scott/tiger@server:port:sid"); 
    // ods.setConnectionProperties(props); 
    // Connection conn = ods.getConnection();  


    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Leave uncommented the appropriate line for the property you want to query
    // ResultSet rset = stmt.executeQuery ("select TERMINAL from v$session");
    // ResultSet rset = stmt.executeQuery ("select PROCESS from v$session"); 
    ResultSet rset = stmt.executeQuery ("select program from v$session");

    // Iterate through the result
    while (rset.next ())
      System.out.println (rset.getString (1));
  }
}

   
   Replace the values for server, port and sid in the connection string and when necessary username/password.
   
   
2. Compile the class with the JDBC driver on the classpath.
   
   javac -cp=.:ojdbc14.jar SessionProps.java

   Remark: When on windows replace ":" with ";".
   
   
3. Run the class:
   
   java -cp=.:ojdbc14.jar SessionProps