Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a DOS batch file which invokes sqlplus, which executes some basic SQL contained in another .sql file, and I want the last part of it to return a value back to the dos batch file. However, while there are many examples via Google on how to do this using a Unix shell, the closest I get for DOS batch files is something like this:

SELECT
  MAX(magicnumber)
INTO
  :ret_val
FROM
  ABCD.EFGH

exit :ret_val

However, this does not work for me - sqlplus just gives me a usage message for EXIT.

If I do "exit 15", for instance, the DOS batch file correctly reports the return code (using errorlevel), so that part is okay.

Is there some syntax thing I am missing out on? I should note that I am very new to SQL stuff so it might be some very obvious thing I'm not seeing... Thanks!

share|improve this question
    
Note that this is not DOS. I'd edit your post if I had sufficient rep. –  Joey Jul 13 '10 at 1:05
    
Apologies if this was unclear - the DOS batch file invokes sqlplus, which executes SQL contained in another .sql file, the basic logic of which is presented here. –  weiji Jul 13 '10 at 8:13
    
weji: It's not a DOS batch file unless you happen to run it in command.com. cmd.exe uses a superficially similar syntax but is much more powerful with many extensions. They are not the same and somewhere a kitten dies every time someone calls Windows batch files DOS batch files. –  Joey Jul 13 '10 at 9:31
    
Thanks, I did not know that. I can understand why you would feel that way - I get annoyed when people say "a Windows command prompt is like a linux terminal" - they also have a superficial similarity but a Unix shell is much more powerful. We all see kittens getting sacrificed somewhere. –  weiji Jul 13 '10 at 18:31
add comment

1 Answer 1

up vote 0 down vote accepted

A sort-of workaround is to simply have it print out the result, and then capture that output the way the unix shells do. User "shoblock" on dbforums.com provides this example here:

http://www.dbforums.com/oracle/1034420-how-return-value-pl-sql-script.html

In essence, the bat file has:

FOR /F "usebackq delims=!" %%i IN (`sqlplus -s u/p@db @t`) DO set xresult=%%i
echo %xresult%

For now I'm going with this.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.