SQL*Magic : Super*SQL
|SQL*MagicSuper*SQLCase StudiesResourcesTechnology ChoicesSQL StandardLinks

Free Resources

Passing Multiple Parameters to a PL/SQL Script

When developing stand-alone utility scripts using Oracle's PL/SQL it is often convenient to pass the script parameters on the command line. However, for a true utility, the number of arguments can become a problem, and keeping track of the meaning of parameters becomes difficult. Worst of all, there is no way to "overload" a script as might be done in Java (through the definition of a different constructor) or ksh or JavaScript (because null arguments are allowed and can be accomodated in the script code). For example, if a PL/SQL script has been defined to have 3 arguments, it MUST always have three arguments. Forgetting an argument will cause the script to prompt the user for the missing argument, which will cause immediate failure if the script is run in batch mode.

The URL_PARM function has been developed which will parse out parameters from a URL encoded string. A simple example of such a string would be


The function allows the PL/SQL to obtain values for the parameters through statements such as

parameter3 := URL_PARM(string,'parameter3');

This keeps the script code very clean and allows overloading of the script so that it can perform different functions depending upon the values it was passed.

This script also makes it very easy to build database intensive CGI applications. A CGI program will receive all form parameters in a URL encoded string. Therefore, by simply passing the parameter string on to a PL/SQL script directly from ksh or Perl, all of the application functionality can be built in PL/SQL, which has great database manipulation capabilities. Examples demonstrating these capabilities will be added to this page in the near future.

Authentication of Oracle Forms Users

Providing authentication for users of an Oracle form can be a frustrating task. The standard methodology would be to have new user accounts (schemas) set up for each individual that needed access to the form.

One problem with that approach is that Oracle schemas have to be added, changed, or deleted by DBAs. Database objects, such as tables, views, or synonyms will be owned by some generic application user (schema), and real users will have to be granted specific permission on these objects to make the form work. Although the process can be simplified through the use of roles, it is still fairly ugly.

To the end user, this type of authentication represents just one more user ID/password combination to write down on the yellow sticky note attached to a computer monitor.

The APACHE_AUTH function has been written which will allow an Oracle form to use the authentication provided by an Apache web server. Since Apache itself can be configured to use NT Domain authentication, LDAP, or even more robust SecurID authentication, this approach can be used to provide a truly unified authentication environment.

The function provided converts the user ID and password provided into the encrypted format required to test authentication against the Apache server. This authentication script can then be passed to a batch telnet session using the HOST built-in as described in the comments of the function.

Setting up User-Level Security in MS Access

Implementing user level security in MS Access is quite a strange and difficult process. There is a lot of information on this topic available at different web sites, including Microsoft"s, but there did not seem to be a clear and concise summary anywhere. This documentation was created in an attempt to clarify a few of the issues.

Building Java/JDBC programs under Mac OS 9.x

Because pre-OS X versions of Mac OS didn't have a CLI, it was impossible to build Java programs using the javac tool, as is the case on virtually every other platform. This tutorial illustrates how to work around this shortcoming for Mac OS 9.x (most of this tutorial applies to other platforms as well).

Everything You Wanted to Know About JDBC

On October 23, 2003, we gave a presentation to the Calgary Oracle Users Group regarding JDBC -- its pros, cons, and everything in between.

Download the PDF version of the presentation here .

JAR files, the CLASSPATH, and the main-class Specification

Setting the CLASSPATH, creating jar files, and adding MANIFEST entries are some of the most confusing aspects of delivering Java applications. This White Paper summarizes the issues and provides simple examples to help you produce the results you want.

Launching a Java program using a Windows desktop icon

In many cases it would be convenient to launch a Java program using a Windows desktop icon. There are several ways to do this, but most involve changes to file associations that would have to be made on every client computer accessing the program. This document describes how you can configure a program on a shared network drive so that all users can launch it by double-clicking on a Windows icon.

Working with unsigned integers in Java

Working with unsigned integers in a Java program is a bit tricky. This is because all primitive types in Java are signed, and default conversions of byte arrays will promote the values to signed integers, usually producing the wrong result. The UtilUnsigned utility class allows you to easily convert complete or partial byte arrays to the java primitives short,int, or long.