CODEWITHSUNDEEP
batch-filecmdsqlcl
user9914491
user9914491

Reputation: 41

Batch file stops after SQL statement is completed and doesn't continue to exit

I'm running a simple batch file to execute a SQL statement.

The batch file:

cd /D C:\sqlcl\bin

sql username/password@host:port:SID @C:\scripts\statement.sql

exit

The SQL statement runs perfectly. It spools the results of the query to a CSV file and returns to the SQL> prompt.

The issue is the batch file doesn't go to the next line to exit SQL and the CMD window. No matter what command I test, the batch file does not go to the 3rd line.

Is there something I need to add to *.sql file to force it to end? Am I missing something with using exit? I've tried QUIT, HOST to return to CMD, ECHO to print something but not matter the command, it never goes to that line but does return to the SQL prompt.

Upvotes: 1

Views: 1311

Answers (2)

user9914491
user9914491

Reputation: 41

An interesting issue was causing this problem.

In the original SQL file I had exported from SQL Developer, I had not included "exit" at the end. I opened it in Notepad and added "exit". Every time, I looked at the SQL file, I opened it in Notepad and "exit" was there.

I just opened the SQL file in Notepad+ and "exit" was NOT there. I added it and the batch file now works fine.

I have no idea why this issue happens.

Upvotes: 0

Kris Rice
Kris Rice

Reputation: 3410

Adjust your .sql file to add an exit at the end such as this.

set feedback off
set head off
set sqlformat csv
spool emp.csv
select * from emp;
exit

Run as you normally are

$ sql klrice/klrice @spool

resulting file is a csv as expected.

$ more emp.csv
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20,5555555555554444
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30,4929043445510803
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30,6011823599867990
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20,375055626849864
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30,4929727676353442
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30,5262511577814781
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10,6011983140249807
7788,"SCOTT","ANALYST",7566,09-DEC-82,3000,,20,343764091280047
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10,5186144047197497
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30,6011331487563093
7876,"ADAMS","CLERK",7788,12-JAN-83,1100,,20,378775397941460
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30,4916225758678451
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20,378355660338882
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10,34567
$

Upvotes: 1

Related Questions