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.
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.
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.
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, 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?
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.
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.
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
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. )
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).
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.
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.
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.)
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?
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. 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?