Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    7

    Question Unanswered: Pass unix variable to sqlplus

    Hi wrote a unix program that has a parameter startdate and I want to pass the startdate parameter to my sql statement see below:

    #!/bin/ksh
    homedir=/export/home/pcard
    startdate=2
    export homedir startdate
    EMAIL=blahblah@blah.com
    CHARGE_CODE=$homedir/scripts/CHARGE_CODE.txt

    unalias rm
    cd $homedir/scripts
    sqlplus /nolog @$homedir/sql/pcardacl.sql
    if [ -s $CHARGE_CODE ]
    then
    echo "Sucess"
    mailx -s "test" $EMAIL < CHARGE_CODE.txt
    # rm $CHARGE_CODE
    else
    echo "Fail"
    echo "fail" | mailx -s "fail" blahblah@blah.com
    fi

    The script above then calls this sql pcardacl.sql script:

    @$homedir/sql/connection.sql
    Set echo off
    Set heading off
    Set line 280
    SPOOL $homedir/scripts/CHARGE_CODE.txt
    select $startdate from dual;
    SPOOL OFF
    exit

    When I run this is does not work. It's not recognizing the $startdate unix variable. I tried using &startdate instead but it asks me to type in something and I don't want that. Sorry I am not too saavy at unix programming and any help would be much appreciated!

    Thanks!!!

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I don't know sqlplus but try this

    sqlplus /nolog <<EOF
    @$homedir/sql/connection.sql
    Set echo off
    Set heading off
    Set line 280
    SPOOL $homedir/scripts/CHARGE_CODE.txt
    select $startdate from dual;
    SPOOL OFF
    exit
    EOF

  3. #3
    Join Date
    Apr 2007
    Posts
    7
    Thank you so much. It worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •