Solved

How to pass input variable to .sql file

Posted on 2006-11-05
3
764 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
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Folder Comparison 12 54
Use of condition with 'serial' in ansible 2 58
"Black Box" Testing of Control System Software 2 49
Work with App store 7 51
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now