Re: Oracle 10g, SQLPLUS, unix, passing argument question

On Feb 1, 2:26 am, zw <MPC8..._at_gmail.com> wrote:
> Hi
>
> Could someone give 2 simple examples using sqlplus command line on
> 1 - how to pass unix shell argument/variables to a sql file, which
> will use the arguments (eg $1, $2, etc) to execute sql statements.

Sometimes I just unthinkingly use unix tools to do it the literally bad way (but it creates a spool file that shows exactly what happened):

awk ' BEGIN {FS=","} {

        if ( first_time == -1 ) {
            print "prompt part " $1 " future standard to " $8
            print "update product_warehouse"
            print "set future_std = " $8
            print "where warehouse = \047" $3 "\047"
            print "and part_code = \047" $1 "\047"
            print "and company_code = \0471\047"
            print "/"
        }
        else {
            first_time = -1
            print "set echo off"
            print "set pages 0"
            print "set colsep ,"
            print "set verify off"
            print "set lines 132"
            print "set trimout on"
            print "set trimspool on"
            print "set head off"
            print "set tab off"
            print "set define off"
            print "spool futurecost"
        }
      } ' < futurecost.csv > futurecost.sql

awk doesn't care where the data comes from, everything including command line is considered a file with arguments. I give this example because for me it is common for people to say "load this spreadsheet into the database." Then I politely throw the errors back at them.

Other times I do stuff like this:

echo "$lschemapassword
set colsep |
set verify off
set echo off
set feedback off
set pages 0
set heading off
set termout off
set linesize 100
insert into product_master (select * from $schema.product_master) ;
" | sqlplus -s > /dev/null

And also Sybrand's ways, depending on my mood, the requirements, deadlines, whether it's a one-timer, etc. I've noticed the onetimer' s tend to get reused often.

> 2 - how to retrieve variable values in sql file and pass to unix shell
> variables
>
> Are there books out there that show how to use unix/linux shell
> variables with sqlplus scripts ?
>
> Thanks

Also see faq: http://www.orafaq.com/faq/how_does_one_select_a_value_from_a_table_into_a_unix_variable

jg

--
@home.com is bogus.
http://www.ucsbalum.com/alum_dir_plus/notable/business.html