Home » SQL & PL/SQL » Client Tools » Using Unix environment variables
Using Unix environment variables [message #509060] Wed, 25 May 2011 14:28 Go to next message
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 #509061 is a reply to message #509060] Wed, 25 May 2011 14:32 Go to previous messageGo to next message
BlackSwan
Messages: 25849
Registered: January 2009
Location: SoCal
Senior Member
>It's gotta quite a common need.
No, not really.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

solution is OS dependent, but we don't know your since you ignore Posting Guidelines.
Sad
Re: Using Unix environment variables [message #509063 is a reply to message #509060] Wed, 25 May 2011 15:00 Go to previous message
John Watson
Messages: 7255
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>

Previous Topic: DataSnap WebBroker Application, Oracle 11g
Next Topic: HOW TO GET RID OF FIRST EMPTY LINE of the spooled file
Goto Forum:
  


Current Time: Sun Feb 18 21:33:25 CST 2018

Total time taken to generate the page: 0.06457 seconds