3 Post-Upgrade Tasks Specific to Oracle Application Express

Oracle Database Express Edition features a browser-based user interface for running scripts and queries, and building Web-based applications. In Oracle Database 10g, this browser-based user interface is called Oracle Application Express. The following sections describe post-upgrade tasks specific to Oracle Application Express.

This chapter discusses the following topics:

• Choosing an HTTP Server

• Configuring Oracle HTTP Server with mod_plsql

• Copying the Images Directory

• Installing Oracle Application Express in Other Languages

• Managing JOB_QUEUE_PROCESSES

• Obfuscating PlsqlDatabasePassword Parameter

See Also:

"What's Different in Your New Database", "About Database Administration in Oracle Database 10g", and "Logging In to Oracle Application Express"

Note:

The post-upgrade tasks described in this chapter are not required if you continue to use Oracle XML DB HTTP Server as the HTTP Server for Application Express. You can proceed to "Installing Oracle Application Express in Other Languages".

3.1 Choosing an HTTP Server

Oracle Application Express requires access to either the embedded PL/SQL gateway or Oracle HTTP Server and mod_plsql.

The following topics would help you choose an HTTP server:

• About Oracle XML DB HTTP Server

• About Oracle HTTP Server and mod_plsql

3.1.1 About Oracle XML DB HTTP Server

The Oracle XML DB HTTP server that installs with the Oracle Database Express Edition has the embedded PL/SQL gateway. It provides the Oracle database with a Web server and also the necessary infrastructure to create dynamic applications.

The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database and includes the core features of mod_plsql. The following graphic illustrates the Oracle Application Express architecture using the embedded PL/SQL gateway.

Description of the illustration arch_epg.gif

As shown in the previous graphic, the embedded PL/SQL gateway offers a simple two tier architecture: a Web browser and an Oracle database, containing the embedded PL/SQL and Oracle Application Express.

3.1.2 About Oracle HTTP Server and mod_plsql

Oracle HTTP Server uses the mod_plsql plug-in to communicate to the Oracle Application Express engine within the Oracle database. It functions as communication broker between the Web server and the Oracle Application Express objects in the Oracle database. More specifically, it maps browser requests into database stored procedure calls over a SQL*Net connection. The following graphic illustrates the Oracle Application Express architecture using Oracle HTTP Server and mod_plsql.

Description of the illustration arch.gif

See Also:

"Installing Oracle HTTP Server" in Oracle Database Companion CD Installation Guide for your operating environment and "Configuring Oracle HTTP Server with mod_plsql"

Note that this configuration consists of three tier architecture: a Web browser, Oracle HTTP Server (Apache) with mod_plsql, and an Oracle database containing Oracle Application Express.

3.2 Copying the Images Directory

The following section applies only if you are using Oracle HTTP Server as your Web server. After you create all of the required Oracle Application Express database objects, you must copy the images directory into the directory tree of the Oracle HTTP Server. The ORACLE_HOME/apex/images directory contains Oracle Application Express images, templates, cascading style sheets, themes, JavaScript files, and other required file types.

Note:

In this chapter ORACLE_HOME is the location where Oracle HTTP server is installed.

On Windows, you can copy the appropriate directory using Windows Explorer, or run a command from a command prompt as shown in the following example:

xcopy /E /I apex\images ORACLE_BASE\ORACLE_HOME\Apache\Apache\images

On Linux, you can copy the appropriate directory by running from the command prompt, a command similar to the one shown in the following example:

cp -rf apex/images ORACLE_BASE/ORACLE_HOME/Apache/Apache

3.3 Configuring Oracle HTTP Server with mod_plsql

This section describes how to configure Oracle HTTP Server with mod_plsql in a new installation.

Topics in this section include:

• Disabling Oracle XML DB HTTP Server

• Configuring Oracle HTTP Server 10g or Oracle Application Server 10g

See Also:

"Installing Oracle HTTP Server" in Oracle Database Companion CD Installation Guide for your operating environment "About Oracle HTTP Server and mod_plsql" and "Copying the Images Directory"

3.3.1 Disabling Oracle XML DB HTTP Server

If you choose the Oracle HTTP Server with the mod_plsql plug-in, then you must disable Oracle XML DB HTTP Server in which the embedded PL/SQL gateway runs.

To disable the Oracle XML DB HTTP server:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS or SYSTEM. For example:

   c:\> sqlplus
   SQL> CONNECT SYS AS SYSDBA
   Enter password: SYS_password
   

2. Run the following command:

   EXEC DBMS_XDB.SETHTTPPORT(0);
   COMMIT;
   

3.3.2 Configuring Oracle HTTP Server 10g or Oracle Application Server 10g

Perform the following post-installation steps if:

• You are running Oracle HTTP Server 10g or Oracle Application Server 10g.

• Oracle HTTP Server is installed in the Oracle Database 10g Oracle home.

• You have not previously configured Oracle HTTP Server to work with Oracle Application Express.

Topics in this section include:

• Changing the Password for the APEX_PUBLIC_USER Database User

• Create a marvel.conf File

• Edit the httpd.conf File

• Stop and Restart Oracle HTTP Server

3.3.2.1 Changing the Password for the APEX_PUBLIC_USER Database User

In order to specify the password in the DAD file, you have to change the password for the database user APEX_PUBLIC_USER. Please use the following steps to change the password for the APEX_PUBLIC_USER database user:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:

   C:\> sqlplus
   SQL> CONNECT SYS AS SYSDBA
   Enter password: SYS_password
   

2. Run the following statement:

   ALTER USER APEX_PUBLIC_USER IDENTIFIED BY password
   

3.3.2.2 Create a marvel.conf File

If you have not previously configured Oracle HTTP Server to work with Oracle Application Express, then you must create a marvel.conf file. The marvel.conf file contains the information about the DAD to access Oracle Application Express.

To create the marvel.conf file:

1. Use a text editor and create a file named marvel.conf.

   • On Linux, save to:

     $ORACLE_HOME/Apache/modplsql/conf
     

   • On Windows, save to:

     ORACLE_BASE\ORACLE_HOME\Apache\modplsql\conf\
     

2. Copy the following into the marvel.conf file. Replace ORACLE_HOME, host, port, service_name, and apex_public_user_password with values appropriate for your environment. Note that apex_public_user_password is the password you defined in "Changing the Password for the APEX_PUBLIC_USER Database User".

   In the following example, the path listed is likewise only an example. The path in the marvel.conf file should reference the file system path described in "Copying the Images Directory".

   Alias /i/ "ORACLE_BASE/ORACLE_HOME/apex/images/"
   AddType text/xml       xbl
   AddType text/x-component       htc
   
   <Location /pls/apex>
    Order deny,allow
    PlsqlDocumentPath docs
    AllowOverride None
    PlsqlDocumentProcedure      wwv_flow_file_mgr.process_download
    PlsqlDatabaseConnectString  host:port:service_name ServiceNameFormat
    PlsqlNLSLanguage            AMERICAN_AMERICA.AL32UTF8
    PlsqlAuthenticationMode     Basic
    SetHandler                  pls_handler
    PlsqlDocumentTablename      wwv_flow_file_objects$
    PlsqlDatabaseUsername       APEX_PUBLIC_USER
    PlsqlDefaultPage            apex
    PlsqlDatabasePassword       apex_public_user_password
    Allow from all
   </Location>
   

   See Also:

   The section "About Remote Connections" in Oracle Database Express Edition 2 Day DBA for more information about the parameters ORACLE_HOME, host, port, and service_name

3. Locate the line containing PlsqlNLSLanguage.

   The PlsqlNLSLanguage setting determines the language setting of the DAD. The character set portion of the PlsqlNLSLanguage value must be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8. For example:

   ...
   PlsqlNLSLanguage            AMERICAN_AMERICA.AL32UTF8
   ...
   

4. Save and exit the marvel.conf file.

3.3.2.3 Edit the httpd.conf File

Next, you must edit the httpd.conf file to reference the marvel.conf configuration file.

To edit the httpd.conf file:

1. Use a text editor and open the httpd.conf file.

   • On Linux:

     $ORACLE_HOME/Apache/Apache/conf/httpd.conf
     

   • On Windows:

     ORACLE_BASE\ORACLE_HOME\Apache\Apache\conf\httpd.conf
     

2. Add an entry to reference the marvel.conf configuration file.

   include "ORACLE_BASE/ORACLE_HOME/Apache/modplsql/conf/marvel.conf"
   

3. Save and exit the httpd.conf file.

3.3.2.4 Stop and Restart Oracle HTTP Server

You can stop and restart the Oracle HTTP Server by using the command line.

To stop and restart Oracle HTTP Server:

• On Linux, run the following commands from a command prompt:

  $ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server
  $ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server
  

• On Windows, run the following commands from a command prompt:

  ORACLE_BASE\ORACLE_HOME\opmn\bin\opmnctl stopproc ias-component=HTTP_Server
  ORACLE_BASE\ORACLE_HOME\opmn\bin\opmnctl startproc ias-component=HTTP_Server
  

3.4 Installing Oracle Application Express in Other Languages

The Oracle Application Express interface is translated into German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese. A single instance of Oracle Application Express can be installed with one or more of these translated versions. At runtime, each user's Web browser language settings determine the specific language version.

The translated version of Oracle Application Express should be loaded into a database that has a character set that can support the specific language. If you attempt to install a translated version of Oracle Application Express into a database that does support the character encoding of the language, then the installation may fail or the translated Oracle Application Express instance may appear corrupt when run. The database character set AL32UTF8 supports all the translated versions of Oracle Application Express.

You can manually install translated versions of Oracle Application Express using SQL*Plus. The installation files are encoded in UTF8.

Note:

Regardless of the target database character set, to install a translated version of Oracle Application Express, you must set the character set value of the NLS_LANG environment variable to AL32UTF8 prior to starting SQL*Plus.

The following examples illustrate valid NLS_LANG settings for loading Oracle Application Express translations:

American_America.AL32UTF8
Japanese_Japan.AL32UTF8 

To install a translated version of Oracle Application Express:

1. Set the NLS_LANG environment variable, ensuring that the character set is AL32UTF8. For example:

   • Bourne or Korn shell:

     NLS_LANG=American_America.AL32UTF8
     export NLS_LANG
     

   • C shell:

     setenv NLS_LANG American_America.AL32UTF8
     

   • For Windows based systems:

     set NLS_LANG=American_America.AL32UTF8
     

2. Start SQL*Plus and connect to the target database as SYS.

3. Execute the following statement:

   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000;
   

4. Execute the appropriate language specific script. For example:

   @load_de.sql
   

   The installation scripts are located in subdirectories identified by a language code in the unzipped distribution ORACLE_HOME/apex/builder. For example, the German version is located in ORACLE_HOME/apex/builder/de and the Japanese version is located in ORACLE_HOME/apex/builder/ja. Within each of these directories, there is a language loading script identified by the language code (for example, load_de.sql or load_ja.sql).

3.5 Managing JOB_QUEUE_PROCESSES

JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In Oracle Application Express, transactional support and SQL scripts require jobs. If JOB_QUEUE_PROCESSES is not enabled and working properly, then you cannot successfully execute a script.

Topics in this section include:

• Viewing the Number of JOB_QUEUE_PROCESSES

• Changing the Number of JOB_QUEUE_PROCESSES

3.5.1 Viewing the Number of JOB_QUEUE_PROCESSES

There are currently two ways to view the number of JOB_QUEUE_PROCESSES:

• On the About Workspace home page in Oracle Application Express

• From SQL*Plus

3.5.1.1 Viewing JOB_QUEUE_PROCESSES in Oracle Application Express

You can also view the number of JOB_QUEUE_PROCESSES on the About Application Express page.

To view the About Application Express page:

1. Log in to Oracle Application Express. See "Logging In to Oracle Application Express".

2. On the Administration list, click About Application Express.

   The current number JOB_QUEUE_PROCESSES displays at the bottom of the page.

3.5.1.2 Viewing JOB_QUEUE_PROCESSES from SQL*Plus

You can also view the number of JOB_QUEUE_PROCESSES from SQL*Plus by running the following SQL statement:

SQL> CONNECT SYSTEM
Enter password: <password>
SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'

3.5.2 Changing the Number of JOB_QUEUE_PROCESSES

You can change the number of JOB_QUEUE_PROCESSES by running a SQL statement in SQL*Plus.

To update the number of JOB_QUEUE_PROCESSES:

1. Log in to the database as SYSDBA using SQL*Plus.

2. In SQL*Plus run the following SQL statement:

   ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>
   

   For example, running the statement ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 sets JOB_QUEUE_PROCESSES to 20.

3.6 Obfuscating PlsqlDatabasePassword Parameter

The PlsqlDatabasePassword parameter specifies the password for logging in to the database. You can use the dadTool.pl utility to obfuscate passwords in the dads.conf file.

You can find the dadTool.pl utility in the following directory:

• On Linux:

  $ORACLE_HOME/Apache/modplsql/conf
  

• On Windows:

  ORACLE_BASE\ORACLE_HOME\Apache\modplsql\conf
  

3.6.1 Obfuscating Passwords

To obfuscate passwords:

1. Use a text editor and copy the entry for /pls/htmldb or /pls/apex from the marvel.conf file into the dads.conf file.

   • On Linux, these files are located in:

     $ORACLE_HOME/Apache/modplsql/conf/dads.conf
     

   • On Windows, these files are located in:

     ORACLE_BASE\ORACLE_HOME\Apache\modplsql\conf\dads.conf
     

2. Run dadTool.pl by following the instructions in the dadTool.README file.

3. Copy the entry for /pls/htmldb or /pls/apex from the dads.conf file back into marvel.conf.

4. Remove the entry for /pls/htmldb or /pls/apex from the dads.conf file.