1

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.

michael_heath
  • 5,262
  • 2
  • 12
  • 22
user9914491
  • 41
  • 2
  • `echo exit|sql username/password@host:port:SID @C:\scripts\statement.sql` ? – JosefZ Apr 19 '19 at 22:15
  • edit your statement.sql and have the last line be: exit; – Kris Rice Apr 20 '19 at 14:42
  • The last line of the sql statement is "exit;" and it still stops before the exit command in the batch file and returns to the SQL> prompt. The sql script is below: set sqlformat csv; set heading on; set feedback off; spool "C:\output\file.csv"; select *...; spool off; exit; – user9914491 Apr 22 '19 at 13:00

2 Answers2

1

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
$
Kris Rice
  • 3,300
  • 15
  • 33
0

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.

user9914491
  • 41
  • 2