Search

Loading...
Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 22 May 2012

How to run multiple SQL file from DOS batch

To run multiple SQLPlus queries as part of a DOS batch file place all the queries in a separate SQL file.
Type the query simply on notepad and save it as .sql file
For Example:
SELECT EMPLOYEE_NUMBER, PAYSLIP_NUMBER,  TOTAL_ALW FROM HRMS.PAY_PAYMENT_MASTER;
@D:\ss\QUERY1.SQL
SELECT *  FROM HRMS.PAY_EMPLOYEE_PERSONAL_INFO;
@D:\ss\QUERY2.SQL
SELECT EMPLOYEE_NUMBER, LOAN_CODE,  DOCUMENT_NUMBER,LOAN_AMOUNT,INSTALLMENT  FROM HRMS.PAY_LOAN_TRANS;
@D:\ss\QUERY3.SQL
SELECT EMPLOYEE_NUMBER, LATIN_NAME, PAY_MODE  FROM HRMS.PAYROLL_MAIN_FILE;
@D:\ss\QUERY4.SQL
Then save the path of all query file into a single .sql file
@D:\ss\QUERY1.SQL
@D:\ss\QUERY2.SQL
@D:\ss\QUERY3.SQL
@D:\ss\QUERY4.SQL
Save the file as @D:\ss\RUN_ALL_QUERY.SQL
Finally create a batch file and use this file in your batch file.
For Example: TestBAT.bat
@Echo Off
sqlplus [login details] @D:\ss\RUN_ALL_QUERY.SQL
EXIT













To Create Batch File:
To create a batch file just open the notepad, type your command and save it with the name and extension .BAT. Before hitting save we need make sure that Windows doesn’t stick the standard “.txt” ending on the end of our new file. To do this, change the filetype from “.txt” to “all files” as shown in the screenshot below. That’s it – hit save and close the file.
ECHO OFF – BAT writers typically put this at the beginning of their files. It means that the program won’t show the command that you told it to run while it’s running – it’ll just run the command. I’d recommend that after you run this test program, you try removing this line from your code to see what happens.
PAUSE - This outputs the “press any key to continue” message that you’ve seen all too many times. It’s
helpful because it pauses the BAT file execution until the user tells it to go again. If you don’t put this in your program, everything will speed by and end before you can see it. People typically put this in BAT files to give the user a chance to review the material on the screen before continuing.

0 comments:

Post a Comment