Re: Passing O/S variables to SQLPLUS

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.
>
Received on Tue Jan 30 1996 - 00:00:00 CET