14-1. Running a PL/SQL Procedure on the Web
Problem
You'd like to make your PL/SQL procedures accessible to users in a web browser via the Oracle Application Server.
Solution
To run a PL/SQL procedure on the Web, you must first configure a Data Access Descriptor (DAD) within the Oracle Application Server to define the connection information required between mod_plsql within the Oracle Application Server and the Oracle database that holds the PL/SQL procedures you wish to run. In this example the mod_plsql configuration file dads.conf (located in [Oracle_Home]Apache modplsqlconf) is edited to define the DAD.
<Location /DAD_NAME>
SetHandler pls_handler
Order deny,allow
Deny from all
Allow from localhost node1.mycompany.com node2.mycompany.com
AllowOverride None
PlsqlDatabaseUsername ORACLE_SCHEMA_NAME
PlsqlDatabasePassword PASSWORD
PlsqlDatabaseConnectString TNS_ENTRY
PlsqlSessionStateManagement StatelessWithResetPackageState
PlsqlMaxRequestsPerSession 1000
PlsqlFetchBufferSize 128
PlsqlCGIEnvironmentList QUERY_STRING
PlsqlErrorStyle DebugStyle
</Location>
You may repeat the <Location> data for additional DADs as required; perhaps one DAD for every major application. You must restart the Oracle Application Server for changes to the DAD configuration file to take effect.
How It Works
To verify that your DAD is configured properly and will run your PL/SQL code, log into the Oracle database defined in your DAD. The Oracle database account is defined in the PlsqlDatabaseUsername, PlsqlDatabasePassword and PlsqlDatabaseConnectString statements. Next, compile the following test procedure.
create or replace procedure test as
begin
htp.p ('Hello World!'),
end;
Finally, point your web browser to http://node_name/DAD_NAME/test. Where node_name is the name of the machine where the Oracle Application Server is installed and DAD_NAME is the name assigned your DAD in the <Location> tag within the mod_plsql configuration file and test is the name of the PL/SQL procedure create for this test. Your browser should respond with the text “Hello World!”
The <Location> tag within the dads.conf file defines the equivalent of a virtual directory within Apache. When a request reaches the Oracle iAS Apache web server containing the location name defined in the DAD, the PL/SQL package or procedure specified in the remaining portion of the URL is executed. For example, if the URL is http://node.my_company.com/plsqlcgi/employee.rpt, plsqlcgi is the DAD_NAME, then employee is the package name and rpt is the procedure name. Calls to the PLSQL Web Toolkit within the employee.rpt procedure send output directly to the client's web browser.
The SetHandler directive invokes mod_plsql within Apache to handle requests for the virtual path defined by the <Location> tag. This directive is required to run PL/SQL packages and procedures through the Apache web server.
The next three directives restrict access to the virtual path to the nodes specified on the Allow from line. To allow access from any web browser in the world, replace these three directives with the following two.
Order allow,denyAllow from all
The PlsqlDatabase directives define the connection information mod_plsql needs to log into the database. If the PlsqlDatabasePassword directive is supplied, Apache will automatically log into the database when requests from web clients are processed. The TNS_ENTRY is used to complete the login information. If the PlsqlDatabasePassword directive is omitted, the Web browser prompts the user for a username and password. The username entered by the user must exist in the database specified by the TNS_ENTRY name and the user must have execute privileges to the requested procedure. The procedure must be accessible to the ORACLE_SCHEMA_NAME specified in PlsqlDatabaseUsername. In other words, the schema must own the procedure or, if owned by another schema, it must have execute privileges to the procedure.