Microsoft

Oracle Tip: Automate Oracle utilities with Windows Script Host

Learn how to automate the Oracle import facility to allow the import of multiple files and provide the command-line syntax.

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.

Editor's Picks