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 PATH ORACLE_HOME/Apache/modplsql/conf:ORACLE_HOME/perl/bin:PATH
   
   

   On Windows:

   set PATH=ORACLE_HOME\Apache\modplsql\conf;ORACLE_HOME\perl\5.6.1\bin\MSWin32-x86;%PATH%
   
   

   Note:

   The preceding command for Windows should be issued in one line.

3. Set the appropriate shared library path environment variable for your platform.

   • On UNIX platforms, include the ORACLE_HOME/lib directory in your shared library path. Table 7-4 shows the appropriate environment variable for each platform.

     Table 7-4 Platform Type and Corresponding Shared Library Path Environment Variable

     Platform	Environment Variable
     AIX	LIBPATH
     HP-UX	SHLIB_PATH
     Linux, Solaris, and Tru64 UNIX	LD_LIBRARY_PATH

     
     

     For example, to set the SHLIB_PATH environment in the Bourne shell on HP-UX systems, enter the following command:

     $SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH;export SHLIB_PATH
     
     

   • On Windows, include %ORACLE_HOME%\bin in your PATH, for example:

     set PATH=%ORACLE_HOME%\bin;%PATH%
     
     

4. Change directory to the mod_plsql configuration directory for the current release of Oracle HTTP Server:

   cd $ORACLE_HOME/Apache/modplsql/conf
   
   

5. Invoke the following Perl script to obfuscate DAD password:

   perl dadTool.pl -o
    
   

Notes:

• This is a mandatory parameter, except for a DAD that sets PlsqlAuthenticationMode to Basic and uses dynamic authentication.

• For DADs using SingleSignOn authentication, this parameter is the name of the schema owner.

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

PlsqlDatabaseUserName

Specifies the username to use to logon to the database.

• This is a mandatory parameter, except for a DAD that sets PlsqlAuthenticationMode to Basic and uses dynamic authentication.

• For DADs using SingleSignOn authentication, this parameter is the name of the schema owner.

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

PlsqlDefaultPage

Specifies the default procedure to call if none is specified in the URL.

• You can also use Oracle HTTP Server Rewrite rules to achieve the same effect as you get by setting this configuration parameter.

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

PlsqlDocumentPath

Specifies a virtual path in the URL that initiates document download form the document table. For example, if this parameter is set to docs, then the following URLs will start the document downloading process for URLs of the format:

/pls/dad/docs
/pls/plsqlapp/docs

• Omit this parameter for applications that do not perform document uploads or downloads.

  See Also:

  Oracle Application Server mod_plsql User's Guide

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

PlsqlDocumentProcedure

Specifies the procedure to call when a document download is initiated. This procedure is called to process the download.

• Omit this parameter for applications that do not perform document uploads or downloads.

  See Also:

  Oracle Application Server mod_plsql User's Guide

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

PlsqlDocumentTablename

Specifies the table in the database to which all documents are uploaded.

• Omit this parameter for applications that do not perform document uploads or downloads.

  See Also:

  Oracle Application Server mod_plsql User's Guide

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

PlsqlErrorStyle

Specifies the Error Reporting Mode for mod_plsql errors. This parameter accepts the following values:

• ApacheStyle: This is the default mode. In this mode, mod_plsql indicates to Oracle HTTP Server the HTTP error that was encountered. Oracle HTTP Server then generates the error page. This can be used with the Oracle HTTP Server ErrorDocument directive to produce customized error messages.

• ModplsqlStyle: mod_plsql generates the error pages, usually a short message indicating the PL/SQL error that was encountered and PL/SQL exception stack, if any. For example:

  scott.foo PROCEDURE NOT FOUND
  
  

• DebugStyle: This mode provides more details than ModplsqlStyle. mod_plsql provides more details about the URL, parameters and also produces server configuration information. This mode is for debugging purposes only. Do not use this in a production system, since displaying internal server variables could be a security risk.

  Category	Value
  Syntax	PlsqlErrorStyle ApacheStyle/ModplsqlStyle/DebugStyle
  Default	ApacheStyle
  Example	PlsqlErrorStyle ModplsqlStyle

  
  

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

PlsqlExclusionList

Specifies a pattern for procedures, packages, or schema names which are forbidden to be directly executed from a browser. This is a multi-line directive in which each pattern is on one line. The pattern is case-insensitive and can accept a wildcard such as '*'. The default patterns disallowed from direct URL access are: sys.*, dbms_*, utl_*, owa_*, owa.*, htp.*, htf.*, wpg_docload.* .

Setting this directive to "#NONE#" will disable all protection. This is not recommended for a live site and should not be done. (This is sometimes used for debugging purposes).

If this parameter is overridden, the defaults still apply, which means that you do not have to explicitly add the default list to the list of excluded patterns.

• Besides the patterns specified with this parameter, mod_plsql also disallows any procedure name which contains special characters like tabs, newlines, carriage-returns, single-quotes, the reverse slash, the form feed, the open parenthesis, close parenthesis, and space. This cannot be changed.

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

  See Also:

  Oracle Application Server mod_plsql User's Guide

PlsqlFetchBufferSize

Specifies the number of rows of content to fetch from the database for each trip, using either owa_util.get_page or owa_util.get_page_raw.

By default, mod_plsql attempts to fetch 200 response lines of output where each line is of 255 bytes. In situations where the response bytes are single-bytes, the response buffer is populated to the maximum and can pack 255*200=51000 bytes for each round trip. However, for responses containing multi-byte data, the byte packing for each row could be less than ideal resulting in lesser bytes getting transferred for each round trip. If your application generates large pages frequently and the response does not fit in one round trip, then consider setting this parameter higher. However, the memory usage for mod_plsql will increase.

• This parameter is changed only for performance reasons. The minimum value for this parameter is 28, but it is seldom reduced.

• Change this parameter only under the following circumstances:

  • The average response page is large and you want to reduce the number of round-trips mod_plsql makes to the database to fetch the response.

  • The character set in use is multi-byte, and you want to compensate for the problem of get_page or get_page_raw fetching fewer bytes for each row (calculations in the PL/SQL Web ToolKit are character-based and in the case of multi-byte characters, OWA packages assume a worst-case character byte size and do not attempt to pack each row to its maximum).

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

• In older versions of the product, the default for this parameter was 128.

PlsqlInfoLogging

Specifies what mode mod_plsql should use to do extra performance logging.

The mode is:

InfoDebug: This logs more information to the Apache's error_log. This is used in conjunction with Apache's "info" logging level. If the Apache's logging level is not at least set to this high, this setting will be ignored.

This logging setting is useful for debugging problems in your PL/SQL application.

PlsqlMaxRequestsPerSession

Specifies the maximum number of requests a pooled database connection should service before it is closed and re-opened.

• This parameter helps relieve memory and resource problems that may occur due to prolonged session reuse by a PL/SQL application.

• This parameter should not need to be changed; the default is sufficient in most cases.

• Setting this parameter to a low number can degrade performance. A case for a lower value might be an infrequently used DAD whose performance is not a concern, and for which limiting the number of requests provides some benefit.

• In older versions of the product, the equivalent to this parameter is reuse. Instead of taking a value of "Yes" or "No", the new parameter enables you to have finer control over the connection pool reuse in mod_plsql.

PlsqlNLSLanguage

Specifies the NLS_LANG variable for this DAD. This parameter overrides the NLS_LANG environment variable. When this parameter is set, the PL/SQL Gateway uses the specified NLS_LANG to connect to the database. Once connected, an alter session command is issued to switch to the specified language and territory. If the middle tier character set matches that of the database, then no alter session call is issued by mod_plsql.

• Most applications have PlsqlTransferMode set to CHAR which means that the character set in PlsqlNLSLanguage needs to match the character set of the database. In one special case, where the database and mod_plsql are both using fixed-size character sets, and the character set width matches, the character set can be different. The response character set is always the mod_plsql character set.

• If PlsqlTransferMode is set to RAW, then this parameter can be ignored.

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

PlsqlPathAlias

Specifies a virtual path alias to map to a procedure call. This is application specific.

• For applications that do not use path aliasing, this parameter may be omitted.

  See Also:

  Oracle Application Server mod_plsql User's Guide

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

PlsqlPathAliasProcedure

Specifies the procedure to call when the virtual path in the URL matches the path alias as configured by PlsqlPathAlias.

• For applications that do not use path aliasing, this parameter may be omitted.

  See Also:

  Oracle Application Server mod_plsql User's Guide

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

PlsqlRequestValidationFunction

Specifies an application-defined PL/SQL function which gives you the opportunity to allow/disallow further processing of the requested procedure. This is useful in implementing tight security for your PL/SQL application by blocking out package/procedure calls which should not be allowed to execute from this DAD.

The function defined by this parameter must have the following prototype:

boolean function_name (procedure_name IN varchar 2)

Upon invocation, the argument 'procedure_name' will contain the name of the procedure that the request is trying to execute.

For example, if all the PL/SQL application procedures callable from a browser are inside the package "mypkg", then a simple implementation of this function can be as follows:

boolean my_validation_check (procedure_name varchar 2
is
begin
  if (upper (procedure_name) like upper ('myschema.mypkg%')) then 
    return TRUE
  else
    return FALSE
  end if;
end;

• By default, mod_plsql already disallows direct URL access to certain schemas/packages. For more information, refer to PlsqlExclusionList.

• It is highly recommended that you provide an implementation for this function such that it only allows requests that belong to your application, and are callable from a browser.

• Since this function will be called for every request, be sure to make this function as performant as possible. Suggested recommendations are:

  • Name your PL/SQL packages in a fashion such that the implementation of this function can be similar to the example mentioned earlier.

  • If your implementation performs a table lookup to determine what packages/procedures should be allowed, performance can be improved if you pin the cursor in the shared pool.

PlsqlSessionCookieName

Specifies the cookie name when PlsqlAuthenticationMode is set to SingleSignOn. This parameter is supported only for Oracle Application Server releases, and is used by the Oracle Application Server Portal and Oracle Application Server Single Sign-On.

• For DADs not using SingleSignOn authentication, this parameter can be omitted. In most other cases, the session cookie name should be omitted (and this parameter automatically defaults to the DAD name).

• A session cookie name must be specified only for Oracle Application Server Portal instances that need to participate in a distributed Oracle Application Server Portal environment. For those Oracle Application Server Portal nodes you want to seamlessly participate as a federated cluster, ensure that the session cookie name for all of the participating nodes is the same.

• Independent Oracle Application Server Portal nodes need to use distinct session cookie names.

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

PlsqlSessionStateManagement

Specifies how package and session state should be cleaned up at the end of each mod_plsql request.

• Setting this parameter to StatelessWithResetPackageState causes mod_plsql to call dbms_session.reset_package_state at the end of each mod_plsql request.

• Setting this parameter to StatelessWithPreservePackageState causes mod_plsql to call htp.init at the end of each mod_plsql request. This cleans up the state of session variables in the PL/SQL Web ToolKit. The PL/SQL application is responsible for cleaning up its own session state. Failure to do so causes erratic behavior, in which a request starts recognizing or manipulating state modified in previous requests.

• Setting this parameter to StatelessWithFastResetPackageState causes mod_plsql to call dbms_session.modify_package_state(dbms_session.reinitialize) at the end of each mod_plsql request. This API is a lot faster than the mode of StatelessWithResetPackageState, and avoids some latch contention issues, but exists only in database versions 8.1.7.2 and higher. This mode uses up slightly more memory than the default mode.

  Category	Value
  Syntax	PlsqlSessionStateManagement StatelessWithResetPackageState/StatelessWithFastResetPackageState/StatelessWithPreservePackageState
  Default	StatelessWithResetPackageState
  Example	PlsqlSessionStateManagement StatelessWithResetPackageState

  
  

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

• An older value of stateful=no or stateful=STATELESS_RESET corresponds to PlsqlSessionStateManagement StatelessWithResetPackageState

• An older value of stateful=STATELESS_FAST_RESET corresponds to PlsqlSessionStateManagement StatelessWithFastResetPackageState

• An older value of stateful=STATELESS_PRESERVE corresponds to PlsqlSessionStateManagement StatelessWithPreservePackageState

mod_plsql does not support stateful mode of operation. To equip PL/SQL applications with stateful behavior, save state in cookies and/or in the database.

PlsqlTransferMode

Specifies the transfer mode for data from the database back to mod_plsql. Most applications use the default value of CHAR.

• This parameter only needs to be changed to enable sending back responses in different character sets from the same DAD. In such a case, the CHAR mode is useless, since it always converts the response data from the database character set to the mod_plsql character set.

• In older versions of the product, RAW transfer mode was not supported.

PlsqlUploadAsLongRaw

Specifies the extensions to be uploaded as LONGRAW data type, as opposed to using the default BLOB data type. The default can be overridden by specifying multi-line directives of file extensions for field. A value of '*' in this field causes all documents to be uploaded as LONGRAW.

• For applications that do not do document uploads or downloads, this parameter may be omitted.

  See Also:

  Oracle Application Server mod_plsql User's Guide for more information about upload and download processes and the structure of the restrictions on the document table format.

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

7.36.3.3 cache.conf

cache.conf file contains the cache settings for mod_plsql. This file contains parameters which specify the characteristics of the mod_plsql cache system.

The following parameters are specified in cache.conf file:

• PlsqlCacheCleanupTime

• PlsqlCacheDirectory

• PlsqlCacheEnable

• PlsqlCacheMaxAge

• PlsqlCacheMaxSize

• PlsqlCacheTotalSize

PlsqlCacheCleanupTime

Specifies the time to start the cleanup of the cache storage.

This setting defines the exact day and time in which cleanup should occur. The frequency can be set as daily, weekly, and monthly.

• To define daily frequency, the keyword "Everyday" is used. The cleanup starts everyday at the time defined. For example, Everyday 2:00. This causes the cleanup to happen everyday at 2 AM (local time) in the morning.

• To define weekly frequency, the days of the week such as "Sunday", "Monday", "Tuesday", and so on are used. For example, Wednesday 15:30. This causes the cleanup to happen every Wednesday at 3:30 PM (local time) in the afternoon.

• To define monthly frequency, the keyword "Everymonth" is used. The cleanup starts at the Saturday of the month at the time defined. For example, Everymonth 23:00. This causes the cleanup to happen the first Saturday of every month at 11:00 PM (local time) at night.

  Category	Value
  Syntax	PlsqlCacheCleanupTime <Sunday-Saturday, Everyday, Everymonth> <hh:mm>
  Default	Saturday 23:00
  Example	PlsqlCacheCleanupTime Saturday 23:00

  
  

PlsqlCacheDirectory

Specifies the directory where cache files are written out by mod_plsql. This directory must exist or else Oracle HTTP Server will not start.

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

In older versions, this parameter was called "cache_dir" and resides in the "[PLSQL Cache]" section of ORACLE_HOME/Apache/modplsql/cfg/cache.cfg.

PlsqlCacheEnable

Enables mod_plsql caching.

• If you are sure that your application does not make use of the OWA_CACHE packages, in the PL/SQL Web Toolkit, then you can choose to disable caching. In such situations, there will be a very minor performance benefit.

• In older versions, this parameter is called "enabled" and resided in the "[PLSQL Cache]" section of ORACLE_HOME/Apache/modplsql/cfg/cache.cfg.

PlsqlCacheMaxAge

Specifies the maximum time, in days, a cache file can be allowed to reside in a file system cache, after which the cached file will be removed for cache maintenance.

This setting is to ensure that the cache system does not contain old content. This setting removes old cache files and makes space for new ones.

PlsqlCacheMaxSize

Specifies the maximum possible size of a cache file.

This setting is to prevent the case in which one file can fill up the entire cache. In general, it is recommended that this be set to about 1-3 percent of the total cache size.

In older versions, this parameter was called "max_size" and resided in the "[PLSQL Cache]" section of ORACLE_HOME/Apache/modplsql/cfg/cache/cfg.

PlsqlCacheTotalSize

Specifies the total size of the cache directory.

This setting limits the amount of space the cache is allowed to use. Both PLSQL cache and Session Cookie cache share this cache space. Note that this setting is not a hard limit. It might exceed the limit temporarily during normal processing. This is normal behavior.

The cleanup algorithm uses this setting to determine how much to reduce the cache files. Therefore, the real space limit is the physical storage's available size.

This parameter takes bytes as values;

• 1 megabytes = 1048576 bytes

• 10 megabytes = 10485760 bytes

  Category	Value
  Syntax	PlsqlCacheTotalSize <number>
  Default	20971520 (20 MB)
  Example	PlsqlCacheTotalSize 20971520

  
  

In older versions, this parameter was called "total_size" and resided in the "[PLSQL Cache]" section of ORACLE_HOME/Apache/modplsql/cfg/cache/cfg.

7.38.2.1 RewriteEngine

Enables or disables the runtime rewriting engine. If it is set to "Off", this module does no runtime processing at all. Use this directive to disable the module instead of commenting out all the RewriteRule directives.

Rewrite configurations are not inherited by default. This means that you need to have ReWriteEngine On directive for each virtual host in which you want to use it.

7.38.2.2 RewriteOptions

By specifying RewriteOptions 'inherit', you can force the configuration of the parent by the children. In virtual-server context this means that the maps, conditions and rules of the main server are inherited. In directory context this means that conditions and rules of the .htaccess configuration of the parent directory are inherited.

7.38.2.3 RewriteLog

Sets the name of the file to which the server logs any rewriting action that it performs. If the name does not begin with a slash (/), then it is assumed to be relative to the Server Root. To disable logging, either remove or comment out the RewriteLog directive or use RewriteLogLevel 0. Avoid setting the filename to /dev/null to prevent logging. This can slow down the server with no advantage.

7.38.2.4 RewriteLogLevel

Sets the verbosity level of the rewriting log file. The default level 0 means no logging, while 9 or more means that practically all actions are logged.

7.38.2.5 RewriteBase

Explicitly sets the base URL for pre-directory rewrites. Rewrite rule can be used in per-directory configuration (.htaccess) files. When a substitution occurs for a new URL, the base URL should be added into the server processing. To be able to do this, the module needs to know what the corresponding URL-prefix or URL-base is. By default, this prefix is the corresponding file path itself. However, at most Web sites, URLs are not directly related to physical filename paths. In such cases, you have to use the RewriteBase directives to specify the correct URL-prefix.

If the URLs of your Web server are not directly related to physical file paths, you must use RewriteBase in every.htaccess file where you want to use RewriteRule directives.

## /abc/def/.htaccess - - per-dir config file for directory /abc/def
 # /abc/def is the physical path of /xyz,
RewriteEngine On
RewriteBase /xyz
RewriteRule ^oldstuff\.html$ newstuff.html