| Using Unix environment variables [message #509060] |
Wed, 25 May 2011 14:28  |
 |
voldelmar
Messages: 1
Registered: May 2011
|
Junior Member |
|
|
An application uses one user as a container of objects(this user is stripped of ALL privileges), and other users that can access objects in this unique schema depending on their privileges.
There can be multiple installations of the application in one database, therefore many such schemes. An environment variable APP_UNIQUE_SCHEMA points to a current unique schema.
So instead of typing
select * from my_unique_schema.my_table I want to set a current schema for sqlplus session in glogin.sql.
It would have been nice if I could have entered in glogon.sql the following line
alter session set current_schema=$APP_UNIQUE_SCHEMA;
BUT it does not work. sqlplus does not resolve the reference to an environment variable. I cannot figure out how to get the value of an environment variable in sqlplus. It's gotta quite a common need. Does anyone have a solution for this?
|
|
|
|
|
|
| Re: Using Unix environment variables [message #509063 is a reply to message #509060] |
Wed, 25 May 2011 15:00  |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
How about this? I'm working on Windows, but I'm sure you can do the same thing with Unix:
c:\users\john\home>type setschema.sql
alter session set current_schema=&1;
c:\users\john\home>set targetschema=SCOTT
c:\users\john\home>sqlplus jon/jon @setschema.sql %targetschema%
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 25 20:57:23 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
old 1: alter session set current_schema=&1
new 1: alter session set current_schema=SCOTT
Session altered.
orcl> select sys_context('userenv','current_user') from dual;
SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
JON
orcl> select sys_context('userenv','current_schema') from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SCOTT
orcl>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
