Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Example code in JDBC using "application context" in Oracle

Posted on 2004-08-17
9
Medium Priority
?
1,729 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 …
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 tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

636 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