2

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!

mrdenny
  • 27,074
  • 4
  • 40
  • 68
weiji
  • 268
  • 1
  • 3
  • 10
  • Note that this is *not* DOS. I'd edit your post if I had sufficient rep. – Joey Jul 13 '10 at 01: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 08: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 09: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

1 Answers1

0

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.

weiji
  • 268
  • 1
  • 3
  • 10