running SQL plus from UNIX
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: running SQL plus from UNIX

  1. #1
    Join Date
    Mar 2002
    Posts
    17
    What I want to do is return the value from my query back to the calling UNIX ksh program.

    I run the command from my ksh script
    > runsql.ksh

    echo "Script starting"

    sqlplus ${OP_CONNECT_STRING}@${OP_ORA_INST} @sqls/sel_logical_dt.sql

    echo $date

    echo "Script ending"

    >

    In my sel_logical_dt.sql file I have

    select date from logical_date;

    I want to return this date back to the calling unix progam for display.




  2. #2
    Join Date
    Feb 2001
    Posts
    180
    Try something like this:
    sqlplus ${OP_CONNECT_STRING}@${OP_ORA_INST} @sqls/sel_logical_dt.sql > logical_dt.log

    rc=$?

    if [ $rc -eq 0 ]
    then
    ANSWER=`cat logical_dt.log`
    else
    # In this case there will be an error
    ANSWER=""
    fi
    echo $ANSWER

    ==============================
    Don't forget in your sql:
    set heading off
    set pagesize 0
    set feedback off
    set verify off
    set termout off
    set timing off
    set wrap on
    WHENEVER SQLERROR EXIT SQL.SQLCODE

    ==============================
    Without error-detection:
    ANSWER=`sqlplus ${OP_CONNECT_STRING}@${OP_ORA_INST} @sqls/sel_logical_dt.sql`



    Regards
    Ben de Boer

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    codejecucionproceso=`sqlplus -s << KOF
    ${METAUSER}/${METAPWD}@${METATNS}
    @$METASQL/insert_metadatos.sql "$1" "$2" "$3" "$4"
    exit 0;
    KOF`

    echo $codejecucionproceso

  4. #4
    Join Date
    Mar 2002
    Posts
    17
    That worked fine... The only problem with redirection is that the output file contains the verbiage when you connect and exit from oracle. However, I used the AWK command to strip out what I needed.

    Thanks,

    Que_card

Posting Permissions

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



Click Here to Expand Forum to Full Width