Solved

How to pass input variable to .sql file

Posted on 2006-11-05
3
762 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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

7 Experts available now in Live!

Get 1:1 Help Now