Re: Passing O/S variables to SQLPLUS

Chuck Hamilton (chuckh_at_ix.netcom.com) 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.

try this:

        export usrnam=xyz

and in your sql script,

        define tbl_owner = &&USRNAM

Don't know if that works, however, I know this does. call the sql script like:

        sqlplus>_at_sql_script.sql "$usrnam"

and in your sql script,

        define tbl_name = &&1 Received on Fri Jan 26 1996 - 00:00:00 CET