Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Help needed writing a shell script calling a sql inside

    Hi,

    I had a requirement to execute a long running sql query. But the sql query had some parameters to be passed in and at some places i need to press "Enter" . I want to use nohup command and call the .sql using shell script concept. How can i pass the parameters and run the nohup command. I am herewith attaching sql reference.


    Refer below link:

    hatchappsdba.*************/2008/01/how-to-run-rcv11isasql-type-of-files.html



    I want to keep the .sql in a shell script and run it through nohup command.

    Can you please let me know how can i keep the above .sql in a shell script and run through nohup command
    Last edited by kalyanz; 04-02-12 at 13:50. Reason: URL didnt show up in earlier post

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Your link doesn't work.
    Create a text file with all the input in it.
    Code:
    $nohup sqlplus my.sql <answers.txt &
    If that doesn't work, start reading the documentation for 'expect'

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Write a script

    inside the script
    DB_LOGIN=${USER}/${PASSWORD}@${INSTANCE}
    sqlplus -s $DB_LOGIN>> $LOGFILE <<EOF
    set time on
    set timing on
    set echo on
    set head off
    set numformat 999999999999999999990.00
    set scan on
    set feedback on
    set serveroutput on
    set linesize 1000
    set pages 0

    @PATH/query.sql $PARAMETER1 $PARAMETER2

    quit
    EOF


    Then while running the script you please prefix with nohup and suffix with '&'

    Thanks

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is a simple ksh shell script and sql script that i created as an example, as you will see below you can submit it to run in background:
    Code:
    #!/bin/ksh
    # info_export.sh
    # Purpose: Creates a export in a delimited flat file via the info_export.sql sql script and emails file.
    
    . $HOME/.oracleAccess   # File should contain: export ORACLE_ACCESS=user/pass@database
    LOG_FILE_PATH=$HOME/logs
    LOG_FILE=/info_export.log
    EXT_DIR=$HOME/tmp/
    ZIP_DIR=$HOME/tmp/
    BIN_DIR=$HOME/bin/
    EXT_FILE_1=_info_export.txt
    ZIP_FILE=
    SCRIPT_TO_RUN_1=info_export.sql
    SCHEMA="schema_name"
    colsep='chr(9)'
    
    
    # function: email_file is used to email file to recipients
    email_file ()
    {
    #set -x
    mutt -s "$SUBJECT" ${RECIPIENTS} ${ATTACHED_FILE} <<EMAIL 2>>${LOG_FILE_PATH}${LOG_FILE}
    ${BODY}
    EMAIL
    email_file_es=$?
    return
    }
    
    
    ###########################################################################################
    #    Main Shell Processing
    ###########################################################################################
    
    # If id not supplied, default to 12345
    if [[ -z $1 ]] then
         id=12345
    else
      id=$1
    fi
    
    print `date "+%Y-%m-%d-%H.%M.%S"` "*** info_export.sh started for Id-${id} ***" >> ${LOG_FILE_PATH}${LOG_FILE}
    
    # Create export file name.
    EXT_FILE_1="${id}${EXT_FILE_1}"
    
    # Run SQL script to generate TAB delimited export file
    print `date "+%Y-%m-%d-%H.%M.%S"` "*** START export for id_${id}" >> ${LOG_FILE_PATH}${LOG_FILE}
    sqlplus -S <<SQLSCRIPT >> ${LOG_FILE_PATH}${LOG_FILE} 2>&1
    $ORACLE_ACCESS
    @${BIN_DIR}${SCRIPT_TO_RUN_1} ${EXT_DIR}${EXT_FILE_1} $colsep $id;
    SQLSCRIPT
    if [[ $? = 1 ]]    then
        print `date "+%Y-%m-%d-%H.%M.%S"` "Error occured in info_export.sql , review above errors in log" >> ${LOG_FILE_PATH}${LOG_FILE}
      return
    fi
    
    # ZIP/EMAIL export file
    gzip -9vf ${EXT_DIR}${EXT_FILE_1} >> ${LOG_FILE_PATH}${LOG_FILE} 2>&1
    SUBJECT="Info export for Id - ${id}."
    BODY="Info export for id - ${id} is in attached ZIP file."
    ATTACHED_FILE=" -a ${EXT_DIR}${EXT_FILE_1}.gz"
    RECIPIENTS="info_export_distribution_list@your_host.com"
    email_file $SUBJECT $BODY $ATTACHED_FILE $RECIPIENTS
    if [[ $email_file_es = 1 ]]    then
        print `date "+%Y-%m-%d-%H.%M.%S"` "**Error - Email of info export file ${EXT_DIR}${EXT_FILE_1}.gz failed, review above errors." >> ${LOG_FILE_PATH}${LOG_FILE}
    fi
    
    print `date "+%Y-%m-%d-%H.%M.%S"` "*** info_export.sh ended for Id - ${id} ***" >> ${LOG_FILE_PATH}${LOG_FILE}
    Code:
    --     Script: info_export.sql
    --    Purpose: Create export file as a delimited flat file.
    -- Parameters: extract path/file name to use
    --             column separator
    --             Id
    
    repheader off
    repfooter off
    set newpage none
    set pagesize 0
    set linesize 100
    set trimspool on
    set feedback off
    set verify off
    set termout off
    
    set echo off
    set flush off
    
    spool &1
    
    whenever sqlerror exit 1
    
    select 'id' || &2 || 'name' || &2 || 'info_date'
     from  dual;
    
    select id   || &2 || name   || &2 || to_char( info_date, 'yyyy-mm-dd-hh24.mi.ss' )
     from  schema.table
    where  id  =  &3;
    
    spool off;
    
    exit 0
    You can submit the shell script as:
    Code:
    nohup info_export.sh >>path_to/logs/info_export.log 2>&1 &             ## with no parameters, defaults id to 12345
    nohup info_export.sh 99999 >>path_to/logs/info_export.log 2>&1 &       ## or you can supply the id
    And it will run the sql script in background.
    hth

Posting Permissions

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