Solved

Example code in JDBC using "application context" in Oracle

Posted on 2004-08-17
9
1,683 Views
Last Modified: 2008-01-09
Hi

I am looking for a sample JDBC code in Oracle 8i to use "application context" to pass certain client information. I could find many examples using SYS_CONTEXT and implementing security policies in PL/SQL, but could not find one using JDBC.

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#8113
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759

-Srinivas
0
Comment
Question by:sbolisetty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11821975
look at the package dbms_application_info

I have frequently used this to set application context values.  Example (from Oracle Applications):

BEGIN
      dbms_application_info.set_client_info(33);
END;


This is just a session-specific value.


0
 

Author Comment

by:sbolisetty
ID: 11822237
From a PL/SQL block that is how it can be done. From a JDBC connection - especially using connection pool with in an application server - how do I go about passing this information from application server's session back to the database?

-Srinivas
0
 
LVL 7

Accepted Solution

by:
bvanderveen earned 315 total points
ID: 11822530
Use a CallableStatement to call dbms_application_info.  Use set_client to set the value, get_client to check it.  With a Connection named conn:
 
   CallableStatement pCall = null;

   try{
         Class.forName("oracle.jdbc.driver.OracleDriver");
          conn = DriverManager.getConnection("jdbc:oracle:thin:@dbserver:1521:ORCL","scott","tiger");
         
          pCall = conn.prepareCall("{call dbms_application_info(?)}");
          pCall.setInt(1,33);  //set the value of the parameter above...
          pCall.execute();
    } catch(SQLException e){
         System.out.println("You have done something wrong");
    }

 
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:sbolisetty
ID: 11822960
This seem to be just the session-id. Is there any way to pass other information? For example, in my application I need to pass at least two fields - User who is Logged in and Module-Name the user is working on. This is needed to enforce security policies as dictated by the existing oracle structures.

-Srinivas
0
 

Author Comment

by:sbolisetty
ID: 11823245
Looked up on the DB_Application_Info a bit. The set_module and set_client_info procedures should be good enough for me to pass this information to the oracle backend. From the looks of it, this needs to be done before every call to the database. Is this the way Applicatio Context is used in Oracle? Is there any better way to do this from Java Application Server point of view? Thanks for your help.

-Srinivas
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11823411
These are session-based.  You don't need to set before each call to the db, just per session.  When you open a jdbc connection, you have a session.  Set application-level info there, and it will persist throughout the session.  Next connection, they will need to be re-established.

If you are in an environment (HTML, for instance) where the connection is not persistent, you need to stash the values in the page, and use them each time you connect.  Use the get functions to test the value during development.

One other "gotcha"  if you use connection pooling, users may be sharing these values.  Not good, so test around this, or see if your app can skip the pooling.
0
 

Author Comment

by:sbolisetty
ID: 11823579
Do you know of any easier way to approach this problem in the case of HTML-Application using connection-pooling? We do all this and trying to attack this problem from this one application perspective. Thank you.

-Srinivas
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11823698
Simple way: Hold the value you want in a hidden field on your page and reset it each time.  

Are you using JSP, PLSQL server pages, ?  9iAS, WebShere?

You might look at the session object, or use a temporary table to hold values as well.  Don't know your architechture, so don't know.  If using a framework such as JSP, Struts, etc.  look at the documentation on the session object.
0
 

Author Comment

by:sbolisetty
ID: 11824222
It is not the problem with session object. We do keep session object based information and caching on the app server itself. But we have this need to send the user and module names to the oracle backend which will filter out the data based on the security policies set inside the database. The solution above may work if we simply call the dbms_application_info stored procedures before doing any thing with in the connection object. That is, we can get the connection from the pool - then call these two stored procedures and then continue to do what we need to in the application. But this seems a bit cludge and wondering if and how this is solved by others. Thanks.

-Srinivas
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question