Re: Web application passing client info

From: Tanel Poder <tanel_at_poderc.com>
Date: Tue, 5 Jan 2010 22:26:12 +0800
Message-ID: <4602f23c1001050626w6b1c5b49r28df90fddf80c244_at_mail.gmail.com>

Hi George,

You need to search for end to end metrics in Oracle docs. The API is there for PL/SQL, OCI, JDBC and ODP.NET.

The Java example I normally use during my training and demos is this (see the bold section which binds end to end metrics to a connection:

---

// java -cp $ORACLE_HOME/jdbc/lib/ojdbc5.jar:. Client_id

import java.sql.*;
import oracle.jdbc.OracleConnection;

public class Client_id {

    public static void main(String[] args) throws InterruptedException {

        try {


            // get connection and statement

            DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
            Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:_at_localhost:1521:LIN11G", "system","oracle");
            Statement stmt = conn.createStatement();



            // set metrics for connection (will be sent to server the next

rountrip)
            String[] metrics = new
String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
            metrics[OracleConnection.END_TO_END_CLIENTID_INDEX]="Tanel
Poder:123";
*            ((OracleConnection)conn).setEndToEndMetrics(metrics,(short)0);
*


            // run your SQL code. the client identifier attribute is bundled

with this roundtrip and automatically sent to server with this request
            ResultSet rs = stmt.executeQuery("SELECT sid, username,

client_identifier FROM v$session WHERE type='USER' AND status='ACTIVE'");

// print output from v$session. here you should see this java
program's session with client identifier set

            System.out.printf("\n%4s %20s %30s\n", new Object[] {"SID", "USERNAME", "CLIENT_IDENTIFIER"});

System.out.println("--------------------------------------------------------");
            while (rs.next()) {
                System.out.printf("%4s %20s %30s\n", new Object[]

{Integer.toString(rs.getInt("sid")), rs.getString("username"), rs.getString("client_identifier")} );

            }

            Thread.sleep(10000);
        }
            catch (SQLException e) {
          e.printStackTrace();
        }

    }
}

---

In PL/SQL you can use just dbms_session.set_identifier('xyz') and dbms_session.clear_identifier

--
Tanel Poder
http://blog.tanelpoder.com


On Tue, Jan 5, 2010 at 10:00 PM, Rusnak, Al A CTR (USA) DeCA HQ CI <
george.rusnak.ctr_at_deca.mil> wrote:

>
> Good morning,
>
> We run applications that have users logging in from all over the world.
> My question is:
>  What  code (procedure calls, function calls) do we need to add to our
> applications that will pass the client information (name, etc.) to the
> database when the application makes a connection to the database. All
> connections come in as UPDATE_USER or SELECT_USER and have been granted
> appropriate permissions on the schema owner objects.
>
> We are currently using Oracle 10.2.0.4
>
> Documentation, examples…....
>
> I have not been able to find what I need to pass on . I hope I have
> provided enough information.
>
> Thank you and have a GOOD NEW YEAR !!!
>
> Al Rusnak
> Database Administrator
> Fort Lee, Virginia
> Email: george.rusnak.ctr_at_deca.mil
>
>


-- 
Tanel Poder
http://blog.tanelpoder.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 05 2010 - 08:26:12 CST