Home » SQL & PL/SQL » Client Tools » Using Unix environment variables in login.sql (SQLPlus 10.2.0.4 HP-UX 11i v3)
Using Unix environment variables in login.sql [message #428721] Thu, 29 October 2009 11:36 Go to next message
hackett087
Messages: 5
Registered: October 2009
Location: Ireland
Junior Member
I would like to be able use a Unix environment variable in the SQLPlus login.sql file.

For example:
I would like to include a line similar to the one below in my login.sql file

DEFINE TEST = $ORACLE_SID

When SQLPlus starts and runs the login.sql file, I would like it to have set TEST to be equal to the value of the ORALCE_SID Unix environment variable

How can this be achieved?

Thanks.
Re: Using Unix environment variables in login.sql [message #428724 is a reply to message #428721] Thu, 29 October 2009 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
login.sql is a text file just like example.txt would be.
To accomplish what you desire, it needs to be created with desired contents

Good Luck!
Re: Using Unix environment variables in login.sql [message #428726 is a reply to message #428724] Thu, 29 October 2009 11:54 Go to previous messageGo to next message
hackett087
Messages: 5
Registered: October 2009
Location: Ireland
Junior Member
Thanks,

I know that it is just a text file. I just don't know what content I should put in the file to achieve my goal of defining a symbol that contains the value of a Unix environment variable.
Re: Using Unix environment variables in login.sql [message #428728 is a reply to message #428721] Thu, 29 October 2009 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this REALLY $ORACLE_SID you want or the instance name you are connected to?

Regards
Michel
Re: Using Unix environment variables in login.sql [message #428731 is a reply to message #428726] Thu, 29 October 2009 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
hackett087 wrote on Thu, 29 October 2009 09:54
Thanks,

I know that it is just a text file. I just don't know what content I should put in the file to achieve my goal of defining a symbol that contains the value of a Unix environment variable.


Neither OS shell nor sqlplus does variable substitution within any text file.

You could write a shell script which would construct login.sql so that it contains the desired value.

What problem are you really, really trying to solve?
Re: Using Unix environment variables in login.sql [message #428732 is a reply to message #428731] Thu, 29 October 2009 12:24 Go to previous messageGo to next message
hackett087
Messages: 5
Registered: October 2009
Location: Ireland
Junior Member
I'm upgrading a product at the moment. The upgrade process involves running many SQL scripts and reviewing the output. The scripts have been constructed so that the output is spooled to a directory. The directory is specified in a symbol which is hardcoded in the login.sql.

As a number of people will be running the upgrades in parallel, I don't want them to overwrite each others output, so I was hoped to set the output in the login.sql and have it point to an environment variable which I can easily set.

I have been looking at using dbms_system.get_env but I can't figure how to use it in login.sql.

I could use a shell script to update the login.sql but I was hoping to find a way to do it directly via the login.sql

Thanks,
Re: Using Unix environment variables in login.sql [message #428734 is a reply to message #428732] Thu, 29 October 2009 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>As a number of people will be running the upgrades in parallel,
spool file could be done to OS username specific subfolder.
spool file name could include Oracle Session ID
spool file name could include timestamp

multiple ways to prevent collision/overwriting
Re: Using Unix environment variables in login.sql [message #428735 is a reply to message #428732] Thu, 29 October 2009 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have been looking at using dbms_system.get_env but I can't figure how to use it in login.sql.

This returns the environment of the SERVER not the CLIENT.
You cannot access CLIENT environment from SQL or PL/SQL.

Answer my question:
Quote:
Is this REALLY $ORACLE_SID you want or the instance name you are connected to?


Regards
Michel
Re: Using Unix environment variables in login.sql [message #428743 is a reply to message #428735] Thu, 29 October 2009 15:18 Go to previous messageGo to next message
hackett087
Messages: 5
Registered: October 2009
Location: Ireland
Junior Member
As the users will be logging onto the Unix server that runs the different database instances, I had not made a hard and fast distinction between client and server.

In terms of $ORACLE_SID versus instance name, either would do. The users will all be logging into the Unix server under the same user account. They will be running scripts from the same directory. The only thing that will be different is the $ORACLE_HOME and $ORACLE_SID, so I was hoping to use one of these values to direct the spooled output to different folder.
Re: Using Unix environment variables in login.sql [message #428746 is a reply to message #428743] Thu, 29 October 2009 15:35 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
v102



INSTANCE_NAME = SID
Re: Using Unix environment variables in login.sql [message #428747 is a reply to message #428746] Thu, 29 October 2009 15:51 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option (SQL*Plus variable) :

select '&_CONNECT_IDENTIFIER' from dual;


Or just use &_CONNECT_IDENTIFIER as the variable directly when you need it.
Re: Using Unix environment variables in login.sql [message #428748 is a reply to message #428747] Thu, 29 October 2009 15:58 Go to previous message
hackett087
Messages: 5
Registered: October 2009
Location: Ireland
Junior Member
Thanks to all that helped.

Adding the following line to login.sql solved the problem:

DEFINE OUTPUT_DIR = "/upgrades/output/&_CONNECT_IDENTIFIER/"


Thanks again.
Previous Topic: Show all fetched rows?
Next Topic: variable vs set serveroutput on
Goto Forum:
  


Current Time: Tue Sep 27 21:12:28 CDT 2016

Total time taken to generate the page: 0.10362 seconds