CODEWITHSUNDEEP
batch-filecommand-promptsqlplus
Ian Fafard
Ian Fafard

Reputation: 51

Batch file execution issue (CALL does NOT work)

Ive been going insane researching for a couple days on why my batch file is not executing properly. When I literally copy and paste the code in command prompt, it works FINE, but executing the batch file only runs one line and then STOPS.

Here is the code within the batch file:

    sqlplus -s user/password
    set colsep '    '
    set linesize 32000
    set echo off
    set feedback off
    set trimspool on
    set newpage 0
    set pagesize 0
    spool c:\temp\suggmoves.txt
    select * from suggestive_moves;
    spool off
    quit
    exit

The only solutions I find are using CALL but I am not calling another batch file and that did not work anyways.

Upvotes: 0

Views: 332

Answers (2)

Sylvain Bugat
Sylvain Bugat

Reputation: 8174

You have to redirect the input to execute your commands by the sqlplus instead it's interpreted as shell command and don't do anything in sqlplus. I think sqlplus wait for any input which stopped the script execution until sqlplus is stopped/killed/exited.

To input commands in your script directly as sqlplus command you can use <<input redirection until a word is found (In this modified code it's END):

sqlplus -s user/password <<END
set colsep '    '
set linesize 32000
set echo off
set feedback off
set trimspool on
set newpage 0
set pagesize 0
spool c:\temp\suggmoves.txt
select * from suggestive_moves;
spool off
quit
exit
END

END word must by at the beginning of the line (no indentation).

Sqlplus have a batch mode with a sql command script as input instead of using direct command input redirection.

Upvotes: 1

Michael Edenfield
Michael Edenfield

Reputation: 28338

That is not a batch file. That is an SQLPlus script.

The reason it works from a command line is because SQLPlus is presenting you it's own command prompt. Everything from set colsep down to quit needs to be typed into SQLPlus's prompt. The batch file you wrote is typing them into a Windows command prompt.

To do what you want, put the SQLPlus commands into a separate file, and use the @ option to SQLPlus:

sqlplus -s user/password @sqlscript.sql

Upvotes: 2

Related Questions