This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.


With each new Windows OS release, the command prompt gets further
away from the user. Oracle still ships most of its main server
utilities as command-line utility programs, so it’s becoming
increasingly difficult to perform simple tasks.

However, the newer
versions of Windows have a facility called Windows Script Host that
allows scripts to be run (in JScript or Visual Basic Script) to
automate such tasks. Some other database environments rely on this
scripting host to perform many tasks that aren’t available yet to
Oracle users in the Windows environment.

There are many resources that teach Windows
Script Host. For this article, I’ll limit myself to a specific
example: automating the Oracle import facility to allow the import
of multiple files and provide the command-line syntax.

To execute the import program, I need to create
a COM object using the name “WScript.Shell”. For each command-line
argument to the script, I simply construct the command line with
the given argument and execute it with the WScript.Shell method Run.

‘ VBScript (dropper.vbs)
Set WshShell = WScript.CreateObject(“WScript.Shell”)
For Each arg in WScript.Arguments
    ‘ Note “imp” needs single quotes around
filenames with spaces
    CmdString = “imp userid=scott/tiger
file='” & arg & “‘ rows=y ignore=y”
    ReturnCode =
WshShell.Run(CmdString,1,True)
Next

// Jscript (dropper.js)
var WshShell = WScript.CreateObject(“Wscript.Shell”);
for (i=0;i<WScript.Arguments.Length;i++)
{
    // Note “imp” needs single quotes around
filenames with spaces
    var arg = WScript.Arguments.Item(i);
    var CmdString = “imp userid=scott/tiger
file='” + arg + “‘ rows=y
 ignore=y”;
    var ReturnCode =
WshShell.Run(CmdString,1,true);
}

While this script, on the surface, seems to
only give slightly improved functionality–the ability to list
multiple files without having to enter username, password, or
command-line arguments–there is more functionality available. In
Windows, when you select an object and drop it on top of something
executable, like a VBScript or JScript file, the file will be
executed with the selected filenames as command-line arguments.
Therefore, you can select an Oracle export file and drop it on top
of the script with your mouse, and the script will automatically
start running “imp” on each of those files. This means that the
script can act as a small program that supports drag-and-drop
importing into a database. This will be much more friendly to users
who are used to the Windows environment rather than the command
prompt.

You could extend this script further to, say,
check the extension of the file and execute “imp” on “.dmp” files,
or “sqlplus” on “.sql” files with the same user account. You might
also want to write scripts that access the database to load files
into Blob or XML repositories automatically, or that hide the
username and password somewhere secure. Finally, you can modify the
Run command to hide the
console window that pops up when this is run, or to run each import
in parallel rather than one at a time.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.