Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

How to pass input variable to .sql file

Hi

I have a shell script that takes user inputs say StartDate,StartTime EndDate, EndTime. With in the shell script, I am connecting to oracle database and executing an .sql file which fetches data from a table and spool into a file.

Now my requirement is, I need to pass those user inputs StartDate,StartTime EndDate, EndTime as a criteria for the sql query to fetch only satisfied criteria.

I know, how to use sql statements directly with in a shall script. But the problem here is sql statement is in .sql file(not with in shell script)

Could you please help how amend following scripts to make it work?


Thank you.

------------------------------------------------------------------------------
1)Shell Script:

#!/bin/ksh
# Dynamic Activation for tracing the execution
if [ "$1" = "x" ]
then
        set -x
        shift
fi

program=`basename $0`
usage="Usage: $program <facility> <startDate(format="YYYYMMDD")> <endDate> <startTime(format="HH24MISS")> <endTime>"

if [ $# -ne 5 ]
then
        echo $usage
        exit 1
fi

EDA_FACILITY=$1
START_DATE=$2
END_DATE=$3
START_TIME=$4
END_TIME=$5

SQL_SYNCHRO_FILE=${SQL_TOOLS_DIR}/runSynchro.sql
sqlplus ${USERNAME}/${PASSWORD}@${SID} < ${SQL_SYNCHRO_FILE} >> ${LOG_FILE}
--------------------------------------------------------------------------------
2)runSynchro.sql
SET SERVEROUTPUT ON

DECLARE
returnValue NUMBER;
BEGIN

-- Now call the stored program from utils package
  returnValue := utils.fetchSynchroData(StartDate || StartTime, EndDate || EndTime);

-- Output the results
  IF returnValue = 0 THEN
        dbms_output.put_line(SubStr('GOOD-SYNCHRO returnValue = '||TO_CHAR(returnValue), 1, 255));
  ELSE
        dbms_output.put_line(SubStr('BAD-SYNCHRO returnValue = '||TO_CHAR(returnValue), 1, 255));
  END IF;

  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SubStr('BAD-SYNCHRO Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
END;
/

-------------------------------------------------------------------------------
0
whysuresh
Asked:
whysuresh
1 Solution
 
TintinCommented:
sqlplus ${USERNAME}/${PASSWORD}@${SID} < ${SQL_SYNCHRO_FILE} >> ${LOG_FILE}

should be

sqlplus ${USERNAME}/${PASSWORD}@${SID} >> ${LOG_FILE} <<EOF
start $SQL_SYNCHRO_FILE $START_DATE $END_DATE $START_TIME $END_TIME
exit
EOF
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now