Is it possible to set Sql environment variable using JDBC and also read back the same using JDBC.
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?
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.
I would like to access this variable from java program. Is there any way to do so.
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.
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.
If a table fits your needs, perhaps the context is not what you need.
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.
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.
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
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. )
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.
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:
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).
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.
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.
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.
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.
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.)
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
From Test3 , When I execute below, I get Table or View Doesn't exist.
V_test contains value as test1.
When I have the table emp in Test3 and execute the same, everything works fine.
When I dont use Pl-Sql block and execute them on SQL, everything works fine.
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
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?
A few points anyway:
1) Please verify that a grant is not missing. Issuein 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?