Solved

How to pass input variable to .sql file

Posted on 2006-11-05
3
767 Views
Last Modified: 2013-12-26
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
Comment
Question by:whysuresh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 48

Accepted Solution

by:
Tintin earned 50 total points
ID: 17883722
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

Featured Post

Percona Monitoring and Management and Grafana

Proactive monitoring is vital to a highly-available environment. We have a quick start guide on Experts Exchange for Grafana users.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
In this post we will learn different types of Android Layout and some basics of an Android App.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question