configuration Apex with Oracle DB 11g R2......

Today I come across one requirement which is configuration of Oracle Application Express on oracle DB 11g R2.

First we will talk about Oracle Application Express (i.e. Apex):

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle APEX is a fully supported, no cost option of the Oracle database.

Oracle Application Express Architecture features:

• Forms Conversion [3.2] – Enables the loading of Oracle Forms source into an Oracle Application Express project and generate an initial APEX application.

• Interactive Reports [3.1] – An innovative new technology that allows end users to customize reports. By putting more power in the hands of users it reduces development time and effort while simultaneously enhancing application functionality.

• Web Services Integration [3.1] – Ability to easily integrate with services through either built-in Web service support or through lower level PL/SQL UTL_HTTP package calls.

• Optional Runtime-Only Deployment [3.1] – Ability to install just a runtime APEX environment via SQL scripts for production environments, minimizing installed footprint and increasing security.

• PDF Printing [3.0] – Integrate with Report Server (Oracle BI Publisher, Apache FOP, or XSL-FO) to produce PDF, XLS, HTML outputs. Can define multiple report queries and load custom RTF or XSL-FO templates.

• Flash Charts [3.0] – Create professional charts using Adobe Flash

• MS Access Migration [3.0] – Use SQL Developer Migration Workshop to migrate the Access schema and data. Then migrate the Access Forms, Reports and Queries using APEX to generate a first-cut design

Implementation Oracle Application Express(APEX) on Oracle DB 11g R2 on windows :

Step 1:

Go to Following path(i.e, Apex path) :

C:\>cd C:\app\Administrator\product\11.1.0\db_1\apex

Step 2:

Connect Database from following path with Sys user :

C:\app\Administrator\product\11.1.0\db_1\apex>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 22 11:45:56 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode,log_mode,database_role from v$database;

NAME OPEN_MODE LOG_MODE DATABASE_ROLE

--------- ---------- ------------ ----------------

11gDB READ WRITE NOARCHIVELOG PRIMARY

Step 3 :

Run following Script apxconf.sql and answer questionnaires for the same :

SQL> @apxconf.sql

PORT

----------

8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.

Default values are in brackets [ ].

Press Enter to accept the default value.

Enter a password for the ADMIN user [] ADMIN

Enter a port for the XDB HTTP listener [ 8080] 8080

...changing HTTP Port

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

SQL> $ HOSTNAME

oracledb

To find workspace name and Apex user run following query :

SQL> SELECT WORKSPACE_NAME FROM APEX_WORKSPACE_APEX_USERS;

WORKSPACE_NAME

--------------------------------------------------------------------------------

INTERNAL

SQL> desc APEX_WORKSPACE_APEX_USERS

Name Null? Type

----------------------------------------- -------- ----------------------------

WORKSPACE_ID NOT NULL NUMBER

WORKSPACE_NAME NOT NULL VARCHAR2(255)

FIRST_SCHEMA_PROVISIONED NOT NULL VARCHAR2(30)

USER_NAME NOT NULL VARCHAR2(100)

EMAIL VARCHAR2(240)

DATE_CREATED NOT NULL DATE

DATE_LAST_UPDATED NOT NULL DATE

AVAILABLE_SCHEMAS NUMBER

IS_ADMIN VARCHAR2(3)

IS_APPLICATION_DEVELOPER VARCHAR2(3)

SQL> select user_name,workspace_name from APEX_WORKSPACE_APEX_USERS;

USER_NAME

--------------------------------------------------------------------------------

WORKSPACE_NAME

--------------------------------------------------------------------------------

ADMIN

INTERNAL

And run following url to get apex CONSOLE:

HTTP://LOCALHOST:8080/apex

and enjoy development with apex..