Passing sqlplus return codes to the cscript ws-host

introduction

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.

Windows task scheduler result codes

not that often but quite steady, during the process of configuring or monitoring the execution of tasks with the windows scheduler, one is faced with cryptic error codes in the Last Result column of the schedulers frontend. although the web spreads the rumour that a complete list of shell error codes may exist somewhere at msdn, there is another, web-independent way to decrypt the error codes into comprehensible explanations.

it’s just the net command expanded by the helpmsg switch that does the job. called in such a way, net helpmsg expects an error code input in decimal notation like this:

c:\>net helpmsg 143
The system cannot join or substitute a drive to or for a directory on the same drive.

the decimal value stems, in turn, from the hexadecimal value, 0x8f, as being printed in the Last Result column. most probably, 0x8f or 143, depict a situation, where a task cannot mount a given disk resource. in most cases, however, an error code of 0x1 just points to a defective script under the hood of a task.

by the way, there also is a rudimental logging available with the schedulers frontend. just click Advanced -> View Log in the menue.

have fun

download.oracle.com not available – use edelivery.oracle.com

already noticed? download.oracle.com has been down for around 2 days now, as of june 10, 13:30 hours. never expected something like this may happen anyway such that i was urgendly waiting for good news, but …

i contacted hello oracle in germany and these guys linked me to another source for oracle downloads that i merely suspected to be a post office for cd or better dvd shipping. but i was wrong. edelivery.oracle.com does offer another possibility for electronic delivery of oracle install packages.

me, i was in need of downloading 11gR2 win x64. here is how it goes: