How to read environment variable using JDBC

[Ashwin Sridhar]

Hi,

Is it possible to set Sql environment variable using JDBC and also read back the same using JDBC.

Regards,
Ashwin

[Ashwin Sridhar]

I tried using executing the below,

 ResultSet rs = st.executeQuery("select :x from dual");  

x being my environment variable in Oracle Sql, but I got below exception.

java.sql.SQLException: ORA-01008: not all variables bound

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
	at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1272)
	at TestClass.main(TestClass.java:26)

Any ideas ?

[Ashwin Sridhar]

Hi ,

I tried searching if there is any dbms_system package to do this job, but I couldn't find one.

Anybody have tried such things earlier ?

Regards,
Ashwin

[Martin Vashko]

I'm not sure I understand what you want to do.

If you want to access environment variables on the local machine, you'd of course use standard Java methods for that (see java.lang.System#getenv(), for example).

Do you want to read environment variables set in the database server's operating system? I don't know of any officially supported way to do this in Oracle. Why do you need to do that?

[Wendy L Gibbons]

would your select statement run in sqlplus?

[Ashwin Sridhar]

Do you want to read environment variables set in the database server's operating system? I don't know of any officially supported way to do this in Oracle. Why do you need to do that

I want to access the bind variables that I set in sqlplus. I set my variable like this.

 define content = 'hello';

I would like to access this variable from java program. Is there any way to do so.

[Ashwin Sridhar]

would your select statement run in sqlplus?

Very much. it returned the value of the variable I had set.

define content = '10';

select &content from dual;

The above returns 10 when I run it in sqlplus.

I am trying to retreive the value of this variable from java.

[Martin Vashko]

This isn't an environment variable.

The define keyword is specific to (ie. hanled by) SQL*Plus, it is not part of Oracle's SQL syntax. It roughly corresponds to bind variables (that is, PreparedStatement).

[Ashwin Sridhar]

Martin,

So this can't be accessed by java ?

Regards,
Ashwin

[Martin Vashko]

No, it cannot be accessed from Java.

These variables are defined by SQL*Plus, and are valid only in the SQL*Plus session that defined them. They do not exist outside that environment.

Oracle generally supports named bind variables, but as far as I know there is no support for them in JDBC. It might be worth to verify this in JDBC documentation (sse our Oracle FAQ), but honestly I don't think it is worth the hassle.

[Ashwin Sridhar]

Thanks Martin.

I would verify it there and take a different approach to solve my problem instead of using bind variables.

[Martin Vashko]

What are you actually trying to achieve? If you just need a session-wide variable, perhaps you might use an application context (see the docs). As far as I know, these are optimized to perform roughly as well as bind variables. I don't have direct experience with them, though.

[Ashwin Sridhar]

What my primary motto is to have a variable defined globally and then access the value of it in my queries from java. Now i find that may not work, so I am trying to have the value in a static table and use them up in my queries from java.

I assume app contexts that you mentioned may not be accessible directly from jdbc, though could have the code in a procedure and call the procedure.

[Martin Vashko]

The application context in Oracle is accessible through stored procedures only, JDBC or not. True, the syntax is a bit clumsy. The global context can be shared among sessions, but when the database is restarted, it gets lost.

If a table fits your needs, perhaps the context is not what you need.

[Azahrudhin Mohammad]

Hi Ashwin,
Please try to implement the following code it will solve the problem.

	try
		{
        int[] refNumArr = {1,2,3,4};
		OracleConnection ora_con = null;
		Connection con = null;
        conn = getConnection();//please implement you code to get the connection 
        stmt = ora_con.prepareCall("{call SP_MYSP(?)}");

		WrappedConnection wc = (WrappedConnection) conn;
        ora_con = (OracleConnection) wc.getUnderlyingConnection(); 
		//You need to use the oracle connection
			
   		 //MY_ENV_VAR is the environment variable you have set in Oracle
         ArrayDescriptor arrayDescriptorNum = ArrayDescriptor.createDescriptor("MY_ENV_VAR", ora_con);
         refNumArray = new oracle.sql.ARRAY(arrayDescriptorNum,ora_con, refNumArr);

		stmt.setArray(1, refNumArray);
		stmt.execute();

		}
		catch (Exception e)
		{
				e.printStackTrace();
		}

[Martin Vashko]

Azahrudhin, perhaps I'm too tired today , but I fail to spot the connection to the original question. The code you've posted passes an array into a stored procedure. How is that connected to environment (or session wide) variables and how would such variables be accessed from an SQL query?

[Azahrudhin Mohammad]

Hi
You are correct I have implemented it for SP only , haven't tried for selecting the table but shouldn't be an issue.
CREATE TYPE myNumType AS VARRAY(10) OF NUMBER; is the syntax for creating the env variable in oracle.
How ever you can refer the below site for further information, If I find time I will try to write the code snippet.

http://docs.oracle.com/cd/B10500_01/java.920/a96654/oraarr.htm

[Martin Vashko]

Azahrudhin Mohammad wrote:CREATE TYPE myNumType AS VARRAY(10) OF NUMBER; is the syntax for creating the env variable in oracle.

No, it isn't. The CREATE TYPE doc says:

Use the CREATE TYPE statement to create the specification of an object type, a SQLJ object type, a named varying array (varray), a nested table type, or an incomplete object type.

Ashwin didn't specify (yet) which type of variable he needs to create. If it is a simple NUMBER, VARCHAR or DATE, there is no need for an array. Such a variable can be declared inside a PL/SQL package, which (if I understand correctly) is what you want to demonstrate here. That is a viable approach. However, accessing PL/SQL from SQL incurs a slight performance penalty due to context switches between SQL execution and PL/SQL execution. I believe (though I'm not 100% sure about that) that the application contexts I've mentioned above perform a bit better as they are accesses using the SYS_CONTEXT function, which - I believe - is an SQL function, not a PL/SQL function. If you use the variable many times, it might be important.

See also this thread on AskTom about various types of variables in an Oracle database.

[Ashwin Sridhar]

Hi Martin,

I tried using SYS_CONTEXT, that seems to be quiet useful. But I have one problem with that. When I run the procedure which sets the application context from sqlPlus, value is set to the variable in the context. When I try to read back the same from JDBC, I get null value. So I need to call procedure from jdbc and then do a SYS_CONTEXT from JDBC, which returns my value.

Any idea on how to make it global, i.e if I set value from sqlPlus, i should be in a postion to read back using JDBC

Regards,
Ashwin

[Ashwin Sridhar]

Everytime when I connect to database from java, a new session is created and hence context value is reset. I am figuring out better approaches to have my value across sessions.

[Martin Vashko]

You could use global application context, that one should keep the value across sessions. However, the global context is lost when the database is restarted, you'd have to check for this and set it anew. It is not unusual for application servers to live through database restarts, so - depending on your conditions and setup - you might have to check and handle this condition wherever the global context would be used.

In my previous post I've posted a link to AskTom thread which demonstrates how to use a table and a package to share the value across sessions and database restarts (that one would obviously live through DB restarts). Perhaps that would be a good solution for you. It would perform a little bit worse than the application context, but unless you use it in SQL statements affecting thousands of rows, you probably won't notice. (In my opinion, unless the Java code is written by someone well experienced in Oracle, it generally contains much worse performance problems than this one. )

[Ashwin Sridhar]

Hi Martin,

I created my context on a global scope. That works well across sessions. I did the following.

 create or replace context my_ctx using my_proc
ACCESSED GLOBALLY;
 

[Ashwin Sridhar]

Hi Martin,

I am re-opening this topic. When I used define variables, i could directly substitute the the value in my query and oracle would replace it with the value appropriately. Is there a way by which it could pick up and the value from context.

I am searching for something on this line.

I set my table name in the session_context and I am expecting it to pick the value from the context for my table name.

select * from (sys_context('CONTEXT_NAME','tableName');

I know above query wont work, but I am searching something on this lines.

[Martin Vashko]

You're right, that's not going to work.

A little background might help you understand the issue: when Oracle sees a statement which is identical (character-by-character) to another statement, it generally reuses it. Some other things play a role too, such as which user is executing the statement and what the current schema is (table "audit" in one schema may be different from table "audit" in another, or, even if it is the same table, one user could have privileges to select from it, while another not). So if Oracle can actually reuse the statement, it won't parse it anew, but will use the access plan it has created before. Parsing statements and creating execution plans is expensive, and this is why Oracle strives to avoid it. Now, if a table name could be specified by a variable (or an application context, it is the same in this example), then different tables would definitely mean different plans, even different columns in these tables. Changing the variable might mean the statement needs to be reparsed, which Oracle does not support.

So, are there any workarounds?

1) You can execute the statement dynamically. In a stored procedure, you could use the execute immediate statement. Beware that this statement can expose you to SQL injection issues even for stored procedure code, though, so definitely use DBMS_ASSERT package or ALL_TABLES view to verify the value of the context is an identifier or an existing table. Yes, it does impose an overhead, but it is unavoidable, if you don't want to expose your application to serious SQL injection threats.

2) If you run the statements from Java, you should determine the value of the context and just create and execute the correct statement. You still need to sanitize the table name thus obtained. (The DB contexts seem a bit superfluous now and do not seem to bring anything useful in this scenario).

3) If you want to use the context to select one of a few pre-existing tables that have the same format and differ just in name, you might use the following trick:
create or replace view merged_tables as select column1, column2 column3 [...] from table1 where sys_context('CONTEXT_NAME','tableName')='TABLE1' union all select column1, column2 column3 [...] from table2 where sys_context('CONTEXT_NAME','tableName')='TABLE2' union all select column1, column2 column3 [...] from table3 where sys_context('CONTEXT_NAME','tableName')='TABLE3' /* etc */ ;
You'd then just issue select * from merged_tables and you'd get rows matching the table whose name is stored in the context (beware that the table name is case-sensitive in this scenario). Furthermore, this code is not subject to SQL injection, even if someone manages to slip SQL code into the application context, it won't be interpreted. And, though you should carefully test it yourself, I believe this solution would perform reasonably well.

Edit: if you add a new table, you'll have to update this view, of course. With the first two solutions, it will start to work at the time the new table is added. I still believe that the third approach is the right one, if it fits your needs (that is, if the tables you choose from have the same structure).

[Ashwin Sridhar]

Hi Martin,

Thanks a ton for your time.

I had planned to use the first approach where we could use Execute Immediate. But my original select query is a big string and I need to replace the sys_context value in number of places in the generic query.

Thats the reason why I am still looking out for better options.

The third option is interesting, but If my generic query changes, then I need to make a big change again.

Second option and First option are similar but just differ in place where we implement.

[Martin Vashko]

Yes, the first two options are similar. I'm just not sure using a DB application context brings some benefit at all in the second scenario - to the contrary, it has to be read from the database before constructing the statement, necessitating yet another DB call.

Ashwin Sridhar wrote:The third option is interesting, but If my generic query changes, then I need to make a big change again.

If it was me, I'd look for ways to automate this, such as to create a Java method to generate the text of the query based on some template or other information you certainly have in some form. It might even be a stored procedure that would automatically recreate the view. A stored procedure could be made to recreate the view based just on the list of tables, it could even get a list of common columns from Oracle's data dictionary views.

Edit: and you're welcome.

[Ashwin Sridhar]

Hi Martin,

The value I have in SYS_Context is schema names. So ill be executing the generic query in different schemas based on sys_context.

Now I feel I have a better approach. Instead of modifying the schema name in generic query, I could probably connect to the schema and execute the query.

Have my JPA connect to the schema and execute the generic query.

Regards,
Ashwin

[Martin Vashko]

Ohhhhhhh!! Pity you haven't mentioned "schema" earlier. But, actually, I should have asked at the time tables were introduced.

All you need isalter session set current_schema=xxxxxSo simple.

Perhaps the JPA does the same behind the curtain.

[Ashwin Sridhar]

Hi Martin,

This is exactly what I was looking for. It worked well. Thanks for your time.

But I encountered a strange behaviour, it would be great if you explain it.

When I try to alter schema and insert into a table, if the table doesn't exist in original schema, I get table or view doesn't , even though the table exists in the changed schema.

If I have the table in the original schema and alter to a new schema, things work fine.

Any idea, why this behaviour happens.

[Martin Vashko]

That is strange. I use this trick many years, and haven't noticed any problems with it. You'll have to describe in more details what are you trying to do, and preferably post the code too.

You've mentioned JPA before. This is just a guess, but if you're using JPA (or another framework) and change the current schema without the framework "knowing" about it, I believe this could lead to problems you're describing. (I don't even know whether the ORM frameworks generally support changing the schema in Oracle, I don't know them. If the problem turns out to be with the framework, I probably won't be able to help much.)

[Ashwin Sridhar]

Hi Martin,

I am not using JPA, just trying it against DB. Following is the Scenerios where I get this behavior.

I have three schemas Test1, Test2 , Test3. Test1 & Test2 have tables Emp but Test3 doesn't

Case1 :

From Test3 , When I execute below, I get Table or View Doesn't exist.

declare
v_test varchar2(50);
v_query varchar2(200);
begin
v_test := sys_context('USER_ENV','ContextVar');
v_query:= 'alter session set current_schema = ' || v_test;
execute immediate v_query;
dbms_output.put_line(v_test);
dbms_output.put_line(v_query);
end;
/

V_test contains value as test1.

Case2 :

When I have the table emp in Test3 and execute the same, everything works fine.

Case3;

When I dont use Pl-Sql block and execute them on SQL, everything works fine.

Case4:

Also Everything works fine, I split into 2 pl-sql blocks, first alters session and second pl-sql block contains just insert statement.


Any thoughts on this behaviour

[Martin Vashko]

I've done a few tests on my box and I really don't understand what's happening to you
I cannot imagine how the code in your last post could give Table or View Doesn't exist, since you don't try to access a table in it.

I tried putting non-existent or malformed schema name into alter session set current_schema, but never got Table or View Doesn't exist.

Could you post again the full PL/SQL block that was giving you the error and its full output, ideally by activating spooling in SQL*plus and copying/pasting the result?

[Ashwin Sridhar]

I cannot imagine how the code in your last post could give Table or View Doesn't exist, since you don't try to access a table in it.

I had missed the insert statement while posting the pl-sql block here. The updated pl-sql block is

declare
v_test varchar2(50);
v_query varchar2(200);
begin
v_test := sys_context('USER_ENV','ContextVar');
v_query:= 'alter session set current_schema = ' || v_test;
execute immediate v_query;
insert into emp values ('FROM TEST3 to TEST1');
dbms_output.put_line(v_test);
dbms_output.put_line(v_query);
end;
/

Thank you.

[Martin Vashko]

I got lost on this issue. I don't have a coherent idea of what works and what does not. If you could you create a script (to be executed in SQL*plus by SYSTEM) which would create the users, tables, granted privileges and then did the tests, I believe it would benefit you greatly...

A few points anyway:

1) Please verify that a grant is not missing. Issueselect owner, table_name from all_tables where table_name='EMP'in the same session as the failing PL/SQL block to see which EMP tables your current user can actually access.

2) PL/SQL is generally compiled and then executed. I can easily imagine that changing current schema inside PL/SQL block will affect its future executions, but not the current one. However, I don't really know the inner mechanics so well and also I couldn't reproduce it myself. I'm on 11g R2, so perhaps other versions can behave differently.

3) Most importantly: when it "worked fine" - did you have a look which schema.table the row was actually inserted to?