Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003

    Unanswered: SQL Scriptsto run from Shell

    Hi Friends

    Till recently we were getting partners information in flat file and we were loading them in our database. The steps were

    1) There was script 1 which used to create temp table in which data was loaded from flat file using SQL*Loader.

    2) Script 2 ->This temp table was used to create further temp tables which were having records which were to be actually inserted/updated in main tables.

    3) Script 3-> Main database tables were updated /inserted through this script.

    Now I have to create a Shell Script which would perform all the steps for the user.(Even loading table from flat file has to be done by shell)

    How should I start. I am new to Shell Programming. What things I have to take care of? And what concepts I need to use.

  2. #2
    Join Date
    Jul 2003
    do you need to ftp to get the file?

    determine what directory all your files will be located in.
    if you know nothing about shell scripting I would suggest
    attempting to write something SMALL to educate yourself
    (like a shell script to select a few rows from a table and email the contents
    to yourself).

    the steps you list are the steps you need to do in order.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003

    No need for FTP

    Hi Duck

    We are assuming that all the files will be located in the same folder.
    The control file , scripts , flat file all in same directory.

    Its not that i dont know Shell. I have written some basic level shells and i am
    familiar with syntax. So Plz help me out.

  4. #4
    Join Date
    Jul 2003
    example shell script:
    PHP Code:
    # here you might set up your oracle environment
    # should look similar to your .profile env setup
    # sample path setup
    export PATH=/usr/local/bin:$PATH

    export ORACLE_SID
    export ORAENV_ASK
    export LOAD_JOB
    =(path to all my files)

    # change directory to where all my files reside
    cd $LOAD_JOB

    # call file to create temp table
    sqlplus user/password@sid @create_temp_table.sql

    # call file to load table
    sqlldr user/password@sid control=load_temp.ctl

    # step 2 script create further temp tables 
    sqlplus user/password@sid  @step2_script.sql

    # step 3 script to populate db tables
    sqlplus user/password@sid  @step3_script.sql

    # done
    # save file as
    # make sure to modify file for execution privs
    # chmod 777 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003

    Problem. Solution needed urgently

    Hi Duck

    Thanx for the help you have already provided . That way the script is running perfectly. But Now the Problem is bit complex. Let me Explain.

    Actually here we have a shell function SQL_03 which makes connection with the database. 3 Parameters are given to SQL_03

    1) SQL Statement to execute
    2) Reference of Message File
    3) Reference of Output file.

    Prior to SQL_03 enviroment file is being exceuted which bring connect string in enviroment variable OQBCHCON.

    In all the cases where the SQL_03 is called first parameter is "Execute <Procedure Name>" . Now I am supposed to use this function to execute mine sql scripts. I have tried normal @<script-name> but that is not working.

    I am giving you the SQL_03 function. Kindly have a look what I am tring is possible or not. If yes what is the solution. Kindly have a look. solution is needed urgently.The returned error code is 20. Message and Reference file exists properly and there is no problems in that.

    SQL_03 Function

    function SQL_03
    if [ ! -z $DEBUG ]; then
    set -x
    echo "am here"
    USAGE="${FUNC_NM} <any SQL statement> <message output file> <data output file>"

    # Check if the message output file exists as this is a key output file

    if [[ ! -f ${2} || ! -f ${3} ]]; then
    echo "006076"
    echo "msg_file_exception"
    echo "$FUNC_NM failed with RC = $RC"
    echo "Usage = ${USAGE}"
    echo "Message/Output files \"${2}\" or \"${3}\" are not regular files/do not exist"
    return $RC

    # Otherwise, continue processing



    if [ $# -ne ${NO_ARGS} ]; then
    echo "006073" > $MSGFILE
    echo "msg_erroneous_data" >> $MSGFILE
    echo "$FUNC_NM failed with RC = $RC" >> $MSGFILE
    echo "Usage = ${USAGE}" >> $MSGFILE
    echo "Bad arguments, expected ${NO_ARGS}, received ${#}" >> $MSGFILE
    echo "Input arguments = $* " >> $MSGFILE
    return $RC

    env | grep OQHOME > /dev/null
    if [ $? -eq 0 ]; then

    # Run the input SQL statement

    sqlplus -s <<EOF >${TMPFILE}
    set head off echo off feed off pages 0 term off serverout on linesize ${MAX_LINES_SIZE} wrap off verify off trimspool on
    exec dbms_output.enable(999999);
    whenever SQLerror exit 20
    whenever OSerror exit 20
    spool ${DATAFILE}
    spool off

    if [ $SQLRC -gt 0 ]; then # Check for Oracle errors e.g. userid/password failure
    echo "000001" > $MSGFILE
    echo "msg_oracle_error" >> $MSGFILE
    echo "$FUNC_NM failed with RC = $RC" >> $MSGFILE
    echo "Oracle failure prior to executing the specified SQL statement" >> $MSGFILE
    cat ${TMPFILE} >> $MSGFILE
    FIELD1=`head -1 ${DATAFILE} | cut -c 1,2`
    if [ "$FIELD1" = "00" ]; then
    head -2 ${DATAFILE} > $MSGFILE # copy first 2 rows to message file
    tail +3 ${DATAFILE} > $OUTFILE # copy remaining rows to data file
    echo "000000" > $MSGFILE # sql statement was raw sql so spool output to datafile
    echo "dummy message text" >> $MSGFILE
    cat ${DATAFILE} > $OUTFILE

    rm -f ${TMPFILE} ${DATAFILE}

    return ${RC}

  6. #6
    Join Date
    Jul 2003
    basically you are calling a shell script from a shell script (if I am not mistaken).

    try this within YOUR shell script:
    /(path where sql_03 resides)/SQL_03 (name of sql-file to run) (name of mesg file) (name of log file)

    basically, it looks like all they want you to do is put your sql statement into a file.
    the shell script will run it for you.

    Let's say sql_03 resides here: $HOME/scripts/sql_03
    1) SQL Statement to execute (let's call this run_sql.sql)
    2) Reference of Message File (messages.log)
    3) Reference of Output file. (output.log)

    your line in your script would be:
    $HOME/scripts/SQL_03 run_sql.sql messages.log output.log

    note: you might need to put the full paths on there for each file
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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