Re: Help Needed: SQL*Plus Variables

DAGMARA_at_DELPHI.COM wrote:
>
> Help! My client is building a generic UNIX script that can be
> executed by non-UNIX users to extract data from ORACLE tables. They would like to
> pass ORACLE variables in the command line, but ORACLE hates it.
> User enters tables names into an ORACLE table read by a UNIX script. The
> UNIX script generates a unique ORACLE script for each table with a
> parameter for specific years as follows:
>
> >sqlplus _at_&table_sql_name &yr_parameter
>
> We found a klugy work-around:
> spool dummy
> >select '_at_&table_sql_name &yr_parameter' from dual;
> spool off
> _at_dummy
>
> ORACLE recognizes the variables and substitutes appropriately. Any
> suggestions?
>
> Dagmar Anne Bogan
>

I am a little confused by your examples. The first one looks like it'a run from the UNIX shell but there is no syntax for and oracle login/password. The second example appears to be run from within sqlplus.

Your original remark is that your customer is trying to run an Oracle (SQL*Plus) query from a UNIX script, feeding parameters to the query from the UNIX command line. Here's and example of a script that does this.

#!/bin/sh
sqlplus user/password _at_sql_script.sql $1 $2 table $3

This passes three values, entered at the command line, and one value from the shell script to the sql_script.sql script. Within the sql script these variables should be defined as &1 &2 &3 and &4, respective of their location in the calling sqlplus line.

The #!/bin/sh runs the script in Borne shell. I always start my scripts this way when they are to be used by others. It prevents aliases and other functionality in their shells from possibly interfearing with the script. Received on Thu Nov 09 1995 - 00:00:00 CET