there lately was an article, discussing a certain aspect of managing oracle on windows machines with shell scripting, called Redirecting dbv output to a file. this article is going to dive deeper into the scripting matter, especially contemplating an approach to control the execution of sqlplus-scripts, supervised by logic being embedded in the csript/jscript-interpreter of the windows scripting host (wsh).
sqlplus and scripting on windows – wsh and alternatives
the main problem of using sqlplus to feed the database with code and/or data is its inability to incorporate logic control. this is most notably true iff you have to manage more than one script, probably even branching or staging. sqlplus has variables, assignments, a lot of print formatting and so on but misses any concept of if-then-else and friends. you are obviously impelled to find some other, enclosing runtime environment to express your execution flow plans (and exception escapes ;-).
scripting on windows using the msdos-batch command set was always a nightmare, wsh did a lot better on 2003- and xp-systems and now we are told to have powershell (msh formely) with vista and 2008, also see Next sysdba credentials syntax for impdp with Windows PowerShell. i do not want to examine any pro/con of this evolution nor do i want to introduce alternatives in its facets on windows. to me, having a free choice, i would always and anywhere employ python. it does the daily tasks in an amazingly smart fashion, offering an easily adaptable solution for nearly every duty. however, customer admins may have another perspective or another affectation or may just dislike python.
to that end, iff you are looking for a reliable friend that will help you on with programming your sqlplus script execution on a multitude of windows environments, wsh will not be the worst choice. not to be unmentioned though, wsh development has been discontinued by microsoft. it is to be replaced by powershell somewhere in the future. the most widespread wsh version counts 5.6, with a bugfix update to 5.7 for the vista- and 2008-systems.
running wsh via cscript and jscript
running a first line of code with wsh may take you on a journey through the flexibility and also complexity of the underlying concept first. you have wscript and cscript, you may program in vbs and jscript as well as other languages, you may package your code in wsf-files, you have to switch on debugging in some way and so forth. ok, no need to become desperate, the following code snippets being introduced in its call order will give you just enough fuel to grasp the general idea.
the top-most file, install.bat, is just a simple batch file. it starts another shell by executing cscript with a control file and a hint, that no banner output is requested. note that cscript is the console-oriented interpreter with wsh, wscript on the contrary is bound to communicate by popping up windows all the time any output is generated.
cmd /c cscript .\install.wsf //Nologo
install.wsf, the next in line, is sort of code scaffolding encoded in xml. it defines a package (<package/>) of code that may contain a number of jobs (<job/>) that in turn may contain another number of scripts (<script/>) in different languages. please note the debug parameter with the <job> tag. it will provide exception information. you may also adhere to the convention not to place the script code directly into the <script> tag. this comes in very handy because the interpreter will, on errors, always return a correct error line number (otherwise you need to figure this out by yourself ;-).
<?xml version="1.0" standalone="yes"?> <package> <job id="install"> <?job debug="true"?> <runtime> <description>description</description> </runtime> <script language="JScript" src="install.js"/> </job> </package>
var stdout = WScript.StdOut; var shell = WScript.CreateObject("WScript.Shell"); var retval = "null"; var db_link = "mydb"; var account_pwd = "account"; var log_file = "log.log"; stdout.WriteLine("> retval: " + retval); stdout.WriteBlankLines(1); retval = shell.run("sqlplus /nolog @.\\install.sql " + db_link + " " + account_pwd + " " + log_file, 1, 1); stdout.WriteLine("> retval: " + retval); stdout.WriteBlankLines(1); stdout.WriteLine("> ------------------------------ done ...");
finally, install.sql, takes us home to the matter of this article. it does some parameter abstraction, spooling and connection establishment. then the whenever keyword defines what exception codes will have to be returned on operation system or sql errors. to anticipate some findings below, any of this errors will return a number. such a way, the crackbrained, outcommented select following will throw a sql exception as exit code 942 and not ora-00942 or something like that. the next line declares a variable that will accept assignments in the scope of the sql script or, even, within an anonymous block of pl/sql, as shown. then, onwards, we may print the newly assigned value, pause the script execution to have a chance to actually see the output, stop spooling and eventually exit the script by value (ever implemented a shell process call from java? the same procedure in other colors).
------------------------------------------------- -- parameters: -- &1 - remote tns name -- &2 - account pwd -- &3 - log file ------------------------------------------------- define tns_name = &1 define account_pwd = &2 define log_file = &3 spool &log_file connect account/&account_pwd@&tns_name whenever oserror exit -1 whenever sqlerror exit sql.sqlcode -- select abc from xyz; variable retval number begin :retval := 54; end; / print :retval prompt prompt '> ------------------------------ done ...' pause '> press enter to exit' spool off exit :retval
that’s it, a working infrastructure. but, for the curious guys, one may ask what does exit return as a default? does exit manage to return string expressions with/out whitespace? will it return negative and large numbers? here come the answers:
- default value: 0 for ok, 1 for error
- strings: nope, gives the default error code 1 iff tried
- negative numbers: yes, applicable for user defined exceptions iff you like to
- large numbers: yes, up to 231-1, the default error code for larger numbers otherwise
this is an example of the output for sqlplus and …
SQL*Plus: Release 22.214.171.124.0 Production on Mo Jun 28 22:39:37 2010 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connect durchgef³hrt. PL/SQL-Prozedur erfolgreich abgeschlossen. RETVAL ---------- 2147483647 '> ------------------------------ done ...' '> press enter to exit'
… for the console shell.
D:\temp\sql-exit>install D:\temp\sql-exit>cmd /c cscript .\install.wsf Microsoft (R) Windows Script Host, Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. Alle Rechte vorbehalten. > retval: null > retval: 2147483647 > ------------------------------ done ...