?
Solved

Example code in JDBC using "application context" in Oracle

Posted on 2004-08-17
9
Medium Priority
?
1,701 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 1260 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month12 days, 9 hours left to enroll

777 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