7 Understanding Modules

7.34.1.1 Using Perl to Access the Database

The following section contains information about using Perl to access the database. Perl scripts access databases using the DBI/DBD driver for Oracle. The DBI/DBD driver is part of Oracle Database. It calls Oracle Callable Interface (OCI) to access the databases.

DBI must be enabled in httpd.conf for DBI to function. To do this, perform the following steps:

1. Edit httpd.conf using a text editor.

2. Search for "PerlModule Apache::DBI".

3. Uncomment the line "PerlModule Apache::DBI".

4. Restart Oracle HTTP Server using the following command:

   ORACLE_HOME/opmn/bin> opmnctl [verbose] restartproc ias-component=HTTP_Server
   
   

Files must be copied to ORACLE_HOME/Apache/Apache/cgi-bin

#!<ORACLE_HOME>/perl/bin/perl -w 
  use DBI; 
  my $dataSource = "host=<hostname.domain>;sid=<orclsid>;port=1521"; 
  my $userName = "scott"; 
  my $password = "tiger"; 
  my $dbhandle = DBI->connect("dbi:Oracle:$dataSource", $userName, $password) 
    or die "Can't connect to the Oracle Database: $DBI::errstr\n"; 
  print "Content-type: text/plain\n\n"; 
  print "Database connection successful.\n"; 
  ### Now disconnect from the database 
  $dbhandle->disconnect 
    or warn "Database disconnect failed; $DBI::errstr\n"; 
  exit;

http://<hostname.domain>:<port>/cgi-bin/<scriptname>
http://<hostname.domain>:<port>/perl/<scriptname>

7.34.1.2 Testing Database Connection

The following is a sample Perl script for testing the database connection of a local seed database. To use the script to test another database connection, you must replace scott/tiger with the user name and password for the target database.

##### Perl script start ###### 
use DBI;
print "Content-type: text/plain\n\n"; 
$dbh = DBI->connect("dbi:Oracle:", "scott/tiger", "") || die $DBI::errstr;   $stmt = $dbh->prepare("select * from emp order by empno")|| die $DBI::errstr; 
$rc = $stmt->execute() || die $DBI::errstr; 
while (($empno, $name) = $stmt->fetchrow()) { print "$empno $name\n"; } 
warn $DBI::errstr if $DBI::err; 
die "fetch error: " . $DBI::errstr if $DBI::err; 
$stmt->finish() || die "can't close cursor"; 
$dbh->disconnect() || die "cant't log off Oracle"; 
##### Perl script End ###### 

7.34.1.3 Using SQL NCHAR Datatypes

SQL NCHAR datatypes have been refined since Oracle9i, and are now called reliable Unicode datatypes. SQL NCHAR datatypes such as NCHAR, NVARCHAR2 and NCLOB allow you to store any Unicode characters regardless of the database character set. The character set for those datatypes is specified by the national character set, which is either AL16UTF-16 or UTF8.

This release of DBD::Oracle supports SQL NCHAR datatypes and provides driver extension functions to specify the character form for data binding. The following script shows an example to access SQL NCHAR data:

# declare to use the constants for character forms
use DBD::Oracle qw(:ora_forms);
# connect to the database and get the database handle
$dbh = DBI->connect( ... );
# prepare the statement and get the statement handle
$sth = $dbh->prepare( 'SELECT * FROM TABLE_N WHERE NCOL1 = :nchar1' );
# bind the parameter of a NCHAR type
$sth->bind_param( ':nchar1', $param_1 );
# set the character form to NCHAR
$sth->func( { ':nchar1' => ORA_NCHAR } , 'set_form' );
$sth->execute;

# specify the default form to be NCHAR
$dbh->func( ORA_NCHAR, 'set_default_form' );

# a declaration example for the constants ORA_IMPLICIT and ORA_NCHAR
use DBD::Oracle qw(:ora_forms);
# set the character form for the placeholder :nchar1 to NCHAR
$sth->func( { ':nchar1' => ORA_NCHAR } , 'set_form' );
# set the character form using the positional index
$sth->func( { 2 => ORA_NCHAR } , 'set_form' );
# set the character form for multiple placeholders at once
$sth->func( { 1 => ORA_NCHAR, 2 => ORA_NCHAR } , 'set_form' );

$dbh->func( ORA_NCHAR , 'set_default_form' );

7.36.2.1 plsql.conf

This file contains the LoadModule directive to load mod_plsql into Oracle HTTP Server, any global settings for mod_plsql, and include directives for dads.conf and cache.conf. This file is included by the Oracle HTTP Server configuration file ORACLE_HOME/Apache/Apache/conf/oracle_apache.conf on UNIX or ORACLE_HOME\Apache\Apache\conf\oracle_apache.conf on Windows, which itself gets included in the primary Oracle HTTP Server configuration file httpd.conf.

7.36.2.2 dads.conf

This file contains the configuration parameters for the PL/SQL database access descriptor (DAD). A DAD is a set of values that specifies how mod_plsql connects to a database server to fulfill a HTTP request.

7.36.2.3 cache.conf

This file contains the configuration settings for the file system caching functionality implemented in mod_plsql. This configuration file is relevant only if PL/SQL applications use the OWA_CACHE package to cache dynamically generated content in the file system.

7.36.3.1 plsql.conf

This file contains the LoadModule directive to load mod_plsql into the Oracle HTTP Server, global settings for mod_plsql, and include directives for dads.conf and cache.conf.

The following section discusses the parameters that can be specified in plsql.conf:

• PlsqlDMSEnable

• PlsqlLogEnable

• PlsqlLogDirectory

• PlsqlIdleSessionCleanupInterval

PlsqlDMSEnable

Enables Dynamic Monitoring Service (DMS) for mod_plsql.

PlsqlLogEnable

Enables debug level logging for mod_plsql.

Debug level logging is meant to be used for debugging purposes only. When logging is enabled, log files are generated at:

• UNIX: ORACLE_HOME/Apache/modplsql/logs

• Windows: ORACLE_HOME\Apache\modplsql\logs

as configured by PlsqlLogDirectory. This parameter should be set to "Off" unless recommended by Oracle support to debug problems with mod_plsql.

To view more details about the internal processing of mod_plsql, set this directive to "On". This causes mod_plsql to start logging for every request that is processed. The log files are generated as specified by the PlsqlLogDirectory directive.

PlsqlLogDirectory

Specifies the directory where debug level logs are written out.

Set the directory name of the location where log files should be generated when logging is enabled. To avoid possible confusion about the location of this directory, an absolute path is recommended.

On UNIX, this directory must have write permissions by the owner of the child httpd processes.

PlsqlIdleSessionCleanupInterval

Specifies the time (in minutes) in which the idle database sessions should be closed and cleaned by mod_plsql.

This directive is used in conjunction with connection pooling of database connections and sessions in mod_plsql. When a session is not used for the specified amount of time, it is closed, and freed. This is done so that unused sessions can be cleaned, and the memory is freed on the database side.

Setting this time to a low number helps in faster cleanup of unused database sessions. Be aware that if this number is too low, then this may adversely affect the performance benefits of connection pooling in mod_plsql.

If the number of open database sessions is not a concern, you can increase the value of this parameter for best performance. In such a case, if the site is accessed frequently enough that the idle session cleanup interval is never reached for a session, then the DAD configuration parameter PlsqlMaxRequestsPerSession can be modified so that it is guaranteed that a pooled database session gets recycled on a regular basis.

For most installations, the default parameter value should suffice.

7.36.3.2 dads.conf

This file contains the configuration parameters for the PL/SQL Database Access Descriptor (DAD).

DAD Parameters

This section describes all the DAD level parameters that can be specified in the dads.conf file. Besides these directives, you can also specify additional Oracle HTTP Server directives that can be specified in the context of a <Location> directive, such as:

Order deny,allow
AllowOverride None

The following parameters are discussed in detail in the subsequent sections:

• PlsqlAfterProcedure

• PlsqlAlwaysDescribeProcedure

• PlsqlAuthenticationMode

• PlsqlBeforeProcedure

• PlsqlBindBucketLengths

• PlsqlBindBucketWidths

• PlsqlCGIEnvironmentList

• PlsqlCompatibilityMode

• PlsqlConnectionTimeout

• PlsqlConnectionValidation

• PlsqlDatabaseConnectString

• PlsqlDatabasePassword

• PlsqlDatabaseUserName

• PlsqlDefaultPage

• PlsqlDocumentPath

• PlsqlDocumentProcedure

• PlsqlDocumentTablename

• PlsqlErrorStyle

• PlsqlExclusionList

• PlsqlFetchBufferSize

• PlsqlInfoLogging

• PlsqlMaxRequestsPerSession

• PlsqlNLSLanguage

• PlsqlPathAlias

• PlsqlPathAliasProcedure

• PlsqlRequestValidationFunction

• PlsqlSessionCookieName

• PlsqlSessionStateManagement

• PlsqlTransferMode

• PlsqlUploadAsLongRaw

PlsqlAfterProcedure

Specifies the procedure to be invoked after calling the requested procedure. This enables you to put a hook point after the requested procedure is called. This is useful in doing SQL*Traces/SQL Profiles while debugging a problem with the requested procedure. This is also useful when you want to ensure that a specific call be made after running every procedure.

• For all purposes, except for debugging, this parameter should be omitted. You could use this parameter to stop SQL Trace/SQL Profiling.

• In older versions of the product, this parameter was called after_proc.

PlsqlAlwaysDescribeProcedure

Specifies whether mod_plsql should describe a procedure before trying to execute it. If this is set to "On", then mod_plsql will always describe a procedure before invoking it. Otherwise, mod_plsql will only describe a procedure when its internal heuristics have interpreted a parameter type incorrectly.

• For all purposes, except for debugging, you should leave this parameter set to "Off".

• In older versions of the product, this parameter was called always_desc.

PlsqlAuthenticationMode

Specifies the authentication mode to use for allow access through this DAD.

• Most customer applications use Basic Authentication. Custom Authentication modes (GlobalOwa, CustomOwa, PerPackageOwa) are used by very few PL/SQL applications. The SingleSignOn mode is supported only for Oracle Application Server releases, and is used by Oracle Application Server Portal and Oracle Application Server Single Sign-On.

• If the DAD is not using the Basic authentication, then you must include a valid username/password in the DAD configuration. For the Basic mode, if you wish to perform dynamic authentication, the DAD username/password parameters must be omitted.

• In older versions of the product, this configuration parameter was derived from a combination of enablesso and custom_auth.

  • enablesso = Yes translates to PlsqlAuthenticationMode SingleSignOn

  • custom_auth = Global translates to PlsqlAuthenticationMode GlobalOwa

  • custom_auth = Custom translates to PlsqlAuthenticationMode CustomOwa

  • custom_auth = PerPackage translates to PlsqlAuthenticationMode PerPackageOwa

  All other combinations translate to Basic.

  See Also:

  "Securing Application Database Access through mod_plsql" chapter in the Oracle Application Server mod_plsql User's Guide for more information regarding different authentication modes.

PlsqlBeforeProcedure

Specifies the procedure to be invoked before calling the requested procedure. This enables you to put a hook point before the requested procedure is called. This is useful in doing SQL*Traces/SQL Profiles while debugging a problem with the requested procedure. This is also useful when you want to ensure that a specific call be made before running every procedure.

• For all purposes, except for debugging purposes, this parameter should be omitted. You can use this parameter to start SQL Trace/SQL Profiling.

• In older versions of the product, this parameter was called before_proc.

PlsqlBindBucketLengths

Specifies the rounding size to use while binding the number of elements in a collection bind. While executing PL/SQL statements, the Oracle database maintains a cache of PL/SQL statements in the shared SQL area, and attempts to reuse the cached statement if the same statement is executed again. Oracle's matching criteria requires that the statement texts be identical, and that the bind variable data types match. Unfortunately, the type match for strings is sensitive to the exact byte size specified, and for collection bindings is also sensitive to the number of elements in the collection. Since mod_plsql binds statements dynamically, the odds of hitting the shared cache are low, and it may fill up with near-duplicates and lead to contention for the latch on the shared area. This parameter reduces that effect by bucketing bind lengths to the nearest level.

All numbers specified should be in ascending order. After the last specified size, subsequent bucket sizes will be assumed to be twice the last one.

• This parameter is relevant only if you are using procedures with array parameters, and passing varying number of parameters to the procedure.

• The default should be sufficient for most PL/SQL applications.

• To see if this parameter needs to be changed, check the number of versions of a SQL statement in the SQL area.

• Consider using flexible parameter passing to reduce the problem.

• In older versions of the product, this parameter was called bind_bucket_lengths.

PlsqlBindBucketWidths

Specifies the rounding size to use while binding the number of elements in a collection bind. While executing PL/SQL statements, the Oracle database maintains a cache of PL/SQL statements in the shared SQL area, and attempts to reuse the cached statement if the same statement is executed again. Oracle's matching criteria requires that the statement texts be identical, and that the bind variable data types match. Unfortunately, the type match for strings is sensitive to the exact byte size specified, and for collection bindings is also sensitive to the number of elements in the collection. Since mod_plsql binds statements dynamically, the odds of hitting the shared cache are low, and it may fill up with near-duplicates and lead to contention for the latch on the shared area. This parameter reduces that effect by bucketing bind widths to the nearest level.

All numbers specified should be in ascending order. After the last specified size, subsequent bucket sizes will be assumed to be twice the last one.

The last bucket width must be equal to or less than 4000. This is due to the restriction imposed by OCI where array bind widths cannot be greater than 4000.

• This parameter is relevant only of you are using procedures with array parameters, and passing varying number of parameters to the procedure.

• The default should be sufficient for most PL/SQL applications.

• To see if this parameter needs to be changed, check the number of versions of a SQL statement in the SQL area.

• Consider using flexible parameter passing to reduce the problem.

• In older versions of the product, this parameter was called bind_bucket_widths.

PlsqlCGIEnvironmentList

Specifies overrides and/or additions of CGI environment variables to the default set of environment variables passed down to a PL/SQL procedure. This is a multi-line directive of name-value pairs to be added, overridden or removed. You can only specify one environment variable for each directive.

You can add CGI environment variables from the Oracle HTTP Server environment by specifying the variable name. To remove a CGI environment variable, set it equal to nothing. To add your own name-value pair, use the syntax myname=myvalue.

• Environment variables added here are available in the PL/SQL application through the function owa_util.get_cgi_env.

• In older versions of the product, this parameter was called cgi_env_list.

PlsqlCompatibilityMode

Specifies the compatibility mode for running mod_plsql. This parameter is supported only for Oracle Application Server releases, and is used when you are using mod_plsql with an older version of Oracle Application Server Portal. In such situations, if you are running mod_plsql against a pre-9.0.2 version of Oracle Application Server Portal, this should be set to 1.

This parameter enables an old bug in mod_plsql in which mod_plsql incorrectly converted the plus symbol (+) to space characters for document downloads. Enabling the first bit in this flag will make it impossible to download documents that have a plus symbol (+) in the document name.

PlsqlConnectionTimeout

Specifies the timeout in milliseconds for testing a connection pooled in mod_plsql.

When PlsqlConnectionValidation is set to "Automatic" or "AlwaysValidate", mod_plsql will attempt to test pooled database connections. This parameter specifies the maximum time mod_plsql should wait for the test request to complete before it assumes that the connection is not usable.

PlsqlConnectionValidation

Specifies the mechanism mod_plsql should use to detect terminated connections in its connection pool.

For performance reasons, mod_plsql pools database connections. If a database instance goes down, and mod_plsql was maintaining a pool of connections to the instance, then each pooled database connection results in an error when it is next used to service a request. This can be a concern in high availability configurations like RAC where even if one node goes down, other nodes servicing the database might have been able to service the request successfully. mod_plsql provides for a mechanism whereby it can self-correct after it detects a failure that could be caused by a database node going down. This mechanism to self-correct is controlled by the parameter PlsqlConnectionValidation.

The following are the valid values for PlsqlConnectionValidation:

• Automatic: mod_plsql tests all pooled database connections which were created prior to the detection of a failure that could mean an instance failure.

• ThrowAwayOnFailure: mod_plsql throws away all pooled database connections which were created prior to the detection of a failure that could mean an instance failure.

• AlwaysValidate: mod_plsql always tests all pooled database connections which were created prior to issuing a request. Since this option has an associated performance overhead for each request, this should be used with caution.

• NeverValidate: mod_plsql never pings any pooled database connection. This option always for older behavior in mod_plsql.

When mod_plsql encounters one of the following errors, it assumes that the database might have been down.

• 00443, 00000, "background process did not start"

• 00444, 00000, "background process failed while starting"

• 00445, 00000, "background process did not start after x seconds"

• 00447, 00000, "fatal error in background processes"

• 00448, 00000, "normal completion of background process"

• 00449, 00000, "background process unexpectedly terminated with error"

• 00470, 00000, "LGWR process terminated with error"

• 00471, 00000, "DBWR process terminated with error"

• 00472, 00000, "PMON process terminated with error"

• 00473, 00000, "ARCH process terminated with error"

• 00474, 00000, "SMON process terminated with error"

• 00475, 00000, "TRWR process terminated with error"

• 00476, 00000, "RECO process terminated with error"

• 00480, 00000, "LCK* process terminated with error"

• 00481, 00000, "LMON process terminated with error"

• 00482, 00000, "LMD* process terminated with error"

• 00484, 00000, "LMS* process terminated with error"

• 00485, 00000, "DIAG process terminated with error"

• 01014, 00000, "ORACLE shutdown in progress"

• 01033, 00000, "ORACLE initialization or shutdown in progress"

• 01034, 00000, "ORACLE not available"

• 01041, 00000, "internal error. hostdef extension doesn't exist"

• 01077, 00000, "background process initialization failure"

• 01089, 00000, "immediate shutdown in progress- no operations permitted"

• 01090, 00000, "shutdown in progress- connection is not permitted"

• 01091, 00000, "failure during startup force"

• 01092, 00000, "ORACLE instance terminated. Disconnection forced"

• 03106, 00000, "fatal two-task communication protocol error"

• 03113, 00000, "end-of-file on communication channel"

• 03114, 00000, "not connected to ORACLE"

• 12570, 00000, "TNS: packet writer failure"

• 12571, 00000, "TNS: packet writer failure"

PlsqlDatabaseConnectString

Specifies the connection to an Oracle database.

• If the database is running in the same Oracle home, or the environment variable "TWO_TASK" is set, this parameter need not be specified.

• If the database is running in a separate Oracle home, then this parameter is mandatory.

• If you have problems connecting to the database:

  • Check the username and password information in the DAD.

  • Make sure that you run "tnsping <string>" and execute commands such as:

    sqlplus DADUsername/DADPassword@<string> 
    
    

  • Ensure that TNS_ADMIN is configured properly.

  • Verify that the HOST:PORT:SERVICE_NAME format makes the connection go through.

  • Ensure that the TNS listener and database are up and running.

  • Ensure that you can ping the host from this machine.

• From a mod_plsql perspective, TNSFormat and NetServiceNameFormat are synonymous and denote connect descriptors that are resolved by Net. The TNSFormat is provided as a convenience so that end-users use this to signify that the name resolution happens through the local tnsnames.ora. For situations where the resolution is through an LDAP lookup as configured in sqlnet.ora, it is recommended that the format specifier of NetServiceNameFormat be used.

  If your database supports high availability, for example, RAC database, it is highly recommended that you use the NetServiceNameFormat such that the resolution for the net service name is through LDAP. This enables you to add or remove RAC nodes accessible through mod_plsql by changing Oracle Internet Directory with the new/deleted node information. In such situations, hard-coding database listener HOST:PORT information in dads.conf or in the local tnsnames.ora is not recommended.

• In older versions of the product, this configuration parameter was called connect_string.

PlsqlDatabasePassword

Specifies the password to use to log in to the database.

After making manual configuration changes to DAD passwords, it is recommended that the DAD passwords are obfuscated by running the "dadTool.pl" script located in ORACLE_HOME/Apache/modplsql/conf.

The following are the steps to obfuscate DAD passwords:

1. If necessary, switch user to the Oracle software owner user, typically oracle using the following command:

   $su - oracle
   
   

2. Set the ORACLE_HOME environment variable to specify the path to the Oracle home directory for the current release and set the PATH environment variable to include the directory containing the Perl executable and the location of the dadTool.pl script.

   On Bourne, Bash, or Korn Shell:

   ORACLE_HOME=new_ORACLE_HOME_path;export ORACLE_HOME
   PATH=ORACLE_HOME/Apache/modplsql/conf:ORACLE_HOME/perl/bin:PATH;export PATH
   
   

   On C or tcsh Shell:

   setenv ORACLE_HOME new_ORACLE_HOME_PATH
   setenv PAT