Solved

How to pass input variable to .sql file

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

920 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

15 Experts available now in Live!

Get 1:1 Help Now