marketting

kalyanz · #1 (**permalink**) 03-30-12, 18:59

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

kitaman · #2 (**permalink**) 03-30-12, 20:43

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'

sujit_Kashyap · #3 (**permalink**) 05-22-12, 02:40

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

spacebar · #4 (**permalink**) 05-23-12, 17:23

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

marketting

Wednesday, May 30, 2012

Help needed writing

Internet Services

Web Development

Digital Marketing

Consumer Tech

No comments:

Post a Comment