Re: Passing O/S variables to SQLPLUS

In article <1996Jan30.014604.24225_at_nosc.mil>, Louise Miller <miller_at_louise.ucsd.edu> writes
>Is this what you tried?
>
>_at_sqlplus myscriptname $USRNAM
>
>(in myscriptname.sql put )
>
>SELECT 'drop table '||owner||'.'||table_name||';'
> FROM sys.dba_tables
> WHERE owner = &1;
>
>Are you sure the export part worked? Run the script with
>$USRNAM defined explicitly in the shell script to make sure.
>
>Louise Miller
>PS Any time someone answers a question by starting out with the
>phrase "All you have to do is....." they didn't understand the
>problem.
>
>
>
>chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:
>>I have a unix environment variable that I want to pass to a SQLPLUS
>>script. How do I do it. If I try to include $VARNAME in the script, it
>>only gets the variable's name, not it's value. For example in a shell
>>script I assign a user name to a variable like this:
>>
>>export $USRNAM=xyz
>>
>>In the sqlplus script I want to use that variable in a select like
>>this:
>>
>>define tbl_owner = $USRNAM
>>SELECT 'drop table '||owner||'.'||table_name||';'
>> FROM sys.dba_tables
>> WHERE owner = '&&tbl_owner';
>>
>>But *after* substitution, the WHERE clause contains the variable name,
>>not it's value.
>>
>>I've also tried running the sql script as "_at_script $USRNAM" and
>>changing the referce from &&tbl_owner to &1, but that does exactly the
>>same thing. It passes the variable's name, not it's value.
>>
>>--
>>Chuck Hamilton
>>chuckh_at_ix.netcom.com
>>
>>If at first you don't succeed, sky-diving isn't for you.
>>
>
>

The other alternative is to write it as a shell script as:

sqlplus username/password <<!!!

SELECT 'drop table ' || owner|| '.' || table_name || ';'   FROM sys.dba_tables
 WHERE owner = '$USERNAME';

exit -- exit Sql*Plus
!!! -- end input from file

This will allow you to directly reference exported shell variables.

Hope this helps.

Clive Bostock

Senior Consulant
KPMG (Health Systems)

All opions expressed are those of my
own and not my employer. Received on Sun Feb 04 1996 - 00:00:00 CET