Oracle APEX 4.2 Reporting

4.5 (2 reviews total)
By Vishal Pathak
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Know Your Horse Before You Ride It

About this book

The biggest challenge in the reporting world is to reduce the overall project cost by picking a tool that minimizes effort and time and to ensure an enriching user experience. Oracle APEX, a 4GL technology, with its unique features such as low implementation time and flexibility, aids us in creating performance-tuned applications with minimum hassle.

"Oracle APEX 4.2 Reporting" is a unique blend of fascinating solutions and intriguing integrations which gives you a 360 degree view of the reporting solutions available to you on the market. It showcases advanced APEX solutions that will empower you to fulfill all kinds of tricky requirements of the reporting world. Live applications support the discussions in the text, enabling you to perform your own experiments on prebuilt applications to take your learning to new heights.

"Oracle APEX 4.2 Reporting" will help you build a well-founded understanding of Oracle APEX along with most of the reporting technologies used today. It is written with the idea of being as practical as possible so that you reap the benefits from day one.

Starting with a brief introduction to the architecture of APEX and installation to enable you to see the applications provided with the book in action, we then move on to the uses of Classic and Interactive reports and explore the advanced features of APEX. We will also explore the most widely used reporting solutions, which is then followed by a brief discussion on BPEL, which is like the glue that can gel any number of tools together. Oracle 4.2 APEX Reporting concludes with several ways of tuning an APEX application, as good performance is the heart of customer satisfaction.

Publication date:
August 2013
Publisher
Packt
Pages
428
ISBN
9781849684989

 

Chapter 1. Know Your Horse Before You Ride It

The book presents Oracle Application Express (APEX), as a reporting solution. Oracle APEX is a 4GL technology based on PL/SQL that helps in rapid application development. It can easily be integrated with a number of reporting technologies and also has extensive reporting functionalities of its own. We will talk about these subjects in this book. In this chapter, we will see the practical details of APEX and try to understand the process by which the APEX engine processes our requests. A clear answer to this question will not only empower you to cook your own recipes in APEX but will also make you more confident in the development process and will cut your debugging time.

Note

I wish to inform you that the code throughout the book has been tested on Internet Explorer 9.0, so please work with Internet Explorer 9.0.

We will cover the following in this chapter:

  • Advantages of APEX

  • APEX web server configurations

  • Installing APEX Listener on the WebLogic server and creating an APEX workspace

  • Understanding the APEX URL and having a brief look at session management, cache management, and debugging

  • Understanding APEX page submissions and using DB audit trail to track APEX requests from the backend

  • Considering some of the lesser known alternatives of mod_plsql

  • Zones of caution: The A pessimist's view of APEX section describes the subjects about which an APEX developer should be cautious

 

What is really new in the new avatar of APEX?


APEX (APplication EXpress) is a 4GL RAD tool and is it a true incarnation of its name. Its strength lies in its architecture, which greatly minimizes the number of layers in the enterprise solution and helps a developer to minimize his development efforts by generating the façade and the layouts of the application on its own. It is driven by a metadata repository which stores the necessary objects required for the tool to function. This repository is stored in the database.

Some of the other advantages of APEX are:

  • The developer does not have to explicitly code the underlying HTML and CSS, and he can still get a glittering pretty application.

  • The wizard-based development approach further simplifies development efforts.

  • Inherent grouping of logical elements helps to prevent the code from becoming messy and error-prone.

  • The approach of encapsulating the code in applications, pages, regions, and items is intuitive because it is in sync with the final visible output.

  • The fact that APEX is shipped along with all versions of the database starting with 10gR2, and the fact that no external environment is required for its development, makes it very cost effective.

  • Since APEX sleeps in the database's house, its code is backed up with the regular backup of the DB and there is always a possibility of rollback.

  • APEX lets us design the lock for our house. The authentication and authorization schemes can be custom-written, giving the developer all opportunities to protect its brainchild. Integration with LDAP and Oracle SSO is much easier in the newer versions. AJAX rhymes with APEX and both in word and deed.

  • APEX codes the layout for us but we can tinker with it if the need be.

  • Entire business logic is written by the developer in PL/SQL so that all the opportunities to code the best solution rest in the hands of the coder.

I could go on and on and I haven't even started on the newest features in APEX 4.x.

Let me briefly pen the reasons for you to go for the newest avatar of APEX:

  • Plugins: It opens a plethora of opportunities as it lets the development community contribute in adding new features to the tool.

  • Team development: Get a team and start playing. APEX gives you the interface for the management of your development.

  • Websheets: Developers, pack your bags. This feature lets the business users design their own pages, and APEX does all the work from creation of DB objects to making frontend reports for them.

  • Dynamic actions: Why code when working on APEX? Create dynamic actions, and APEX codes the JavaScript for you.

  • RESTful web services: Rest is the new mantra in APEX. Send the HTTP requests and get web service responses. All modern day web services including those of Google and Yahoo can now be accessed using RESTful web services.

  • APEX now has ears: APEX Listener is a complete JEE approach of working on APEX applications. It makes direct JDBC connections to the DB.

  • Calendars: Playboy calendars will get tough competition from the pretty APEX calendars. These are new kinds of reports that both the developers and the end users love.

  • Error Handling: To err is human, to present a user friendly statement for the error is APEX.

Wait… there is more:

  • Better bricks, better building: New improved items with features such as auto complete.

  • From papyrus to paper, from JavaScript to jQuery: Inbuilt jQuery lets you do more with less code.

  • Beauty lies in the eyes of the beholder and in the APEX charts: The newer version has been fused with AnyChart 6—newer types of charts such as gantt and maps are now available.

  • Improved interactive report: Our very own interactive report from previous versions has been improved.

  • Our workshop gets a makeover: Application builder has a new glittery feel. Items can be dragged-and-dropped from one place to another. Almost feels like a small computer game.

  • Advanced skin care: Improved and ameliorated themes and templates are available now.

 

APEX configurations


Apex needs a web server to fulfill web requests. We will dedicate the next few pages to understanding each one of the possible web server configurations with APEX.

The following diagram shows the various types, and classification of APEX configurations:

APEX configuration using DAD

Under this section we will check out two configurations (external and internal mod_plsql) of apex DAD.

DAD is Database Access Descriptor. DADs are the means of transporting HTTP requests to the database. DADs have the authority to run under the privileges of a schema configured at the time of their creation. Once authorized, a DAD becomes the king of his fort and can execute any procedure in his kingdom. DADs can be created by using mod_plsql. mod_plsql, formerly known as Oracle PL/SQL Cartridge or Oracle Web Agent (OWA), has been the heart of APEX systems. mod_plsql, an extension module of Apache HTTP server, converts the HTTP request to database queries and vice versa. mod_plsql has a database connection pool which helps better performance.

Note

mod_psql is also a part of SQL Developer and is used for the OWA output pane that enables us to see the HTML output in the SQL Worksheet.

The mod_plsql configurations are of two types:

  • Internal mod_plsql – This configuration is internal to the Oracle database and mod_plsql is a part of Embedded PLSQL Gateway (EPG)

  • External mod_plsql – This configuration of mod_plsql is external to the database and can be done only using Apache-based web servers such as native Apache, OHS, and OAS

The Oracle 11g installation, by default, creates a DAD called apex. The preinstalled APEX in 11g is an internal mod_plsql configuration which uses, out of the box, the embedded PL/SQL gateway and the ANONYMOUS schema. This DAD is authorized to execute any procedure on which the ANONYMOUS schema has the execute privileges.

Internal mod_plsql configuration

Let me talk a little about the flow in this configuration. When a user opens the APEX URL, a HTTP/HTTPS request is sent to the XML DB HTTP server which is a part of XMLDB. This request then flows through EPG to mod_plsql. mod_plsql authorizes the request using the wwv_flow_epg_include_modules.authorize procedure. The APEX engine, in combination with the OWA web toolkit and user-defined stored procedures and functions, generates the response HTML. Calls to the user-defined functions and procedures are coded by the developer in the APEX application. Once the response HTML has been generated, mod_plsql gives the response back to the user using XMLDB HTTP server. I have drawn this process flow in the following diagram:

Now let us see the process to configure the XML DB HTTP server (also called XMLDB protocol server), bundled with database, to work on port 8080.

The port on which the inbuilt XML HTTP server operates, can be configured using the DBMS_XDB.sethttpport procedure as shown in the following screenshot:

The account status of the XDB schema should be OPEN. Check this using the following SQL script:

SELECT account_status
FROM dba_users
WHERE username = 'XDB';

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Enter your XDB schema password (the XDB schema is created with database installation) after opening the URL: http://<hostname>:8080. You should be able to see the following page:

Oracle XML DB protocol server maintains a shared pool of sessions, but these connections are to handle the client web requests. Each protocol connection or client request is associated with one session from the pool of the protocol server. After a connection is closed, the session is put back into the shared pool and can be used to serve later connections.

To enable the Oracle XML DB repository to use secure HTTP connections (HTTPS), a DBA must configure the database accordingly. This configuration includes the setting of the http2-port and http2-protocol parameters, enabling the HTTP Listener to use SSL, and enabling the launching of the TCPS dispatcher. After doing this, the DBA must restart the database and the Listener.

Now let us check the DADs configured in the EPG. To do this, we will first have to compile the Chapter1 package in PACKT_SCHEMA. If you have not already created PACKT_SCHEMA, the user may check the Steps to create PACKT_SCHEMA section of the Appendix for the steps to do this. Execute the following command from SQLPLUS as SYSDBA to create CHAPTER1 package.. Make sure that you are running SQLPLUS from the directory that holds 4-98-9_01_chapter1.sql. Put PACKT_SCHEMA in the Enter the schema in which you want to compile this code: prompt:

SQL> @4-98-9_01_chapter1

Now execute the following two statements:

SQL> set serveroutput on
SQL> exec packt_schema.chapter1.get_dad_list();

The output will list apex along with the other DADs configured in the EPG. Run the following statement to find the configured database username for the apex DAD:

SQL> exec packt_schema.chapter1.chk_apex_dad_usr_nam();

You should get the output as shown in the following screenshot:

The Database and web interaction in DAD and Listener configurations section of the Appendix demonstrates the process of creating a DAD and assigning privileges to it. The demonstration also includes the use of this DAD for database and web interaction. Principally, APEX does the exact same thing. The difference is that APEX does it on a bigger scale.

Using the configurations done in the DAD, the EPG determines the database account to be used to fulfill the request, and authenticates the request based on the request-validation-function attribute configured for the DAD.

The EPG configuration of any database can be found out by running the following script:

<OracleHome>\RDBMS\ADMIN\epgstat.sql

You should get the output after running this script, as shown in the following screenshot:

External mod_plsql configuration

Depending on the requirements, we can choose, from a number of possible server combinations for external mod_plsql configuration. The possibilities are: Native Apache, Oracle HTTP Server (OHS), Oracle Application Server (OAS), or OHS + WebLogic Server.

Let me now talk about the flow in this configuration and how it is different from internal mod_plsql configuration. The only difference between external and internal mod_plsql configuration is that the mod_plsql plugin is external in the external configuration. So, if the EPG configuration (internal mod_plsql configuration) is a perfect human, then external configuration is a human with its heart beating outside the body. That may seem like a weird analogy, but that's what it is. Since mod_plsql is a module of Apache HTTP server, and since OHS is based on Apache, and since OHS is a component of OAS, both OHS and OAS can also be used for external mod_plsql configuration. OHS also offers HTTP services in OFM (Oracle Fusion Middleware) and can be configured in front of the WebLogic Server. So it is possible to configure OHS + WebLogic and then use the mod_plsql in OHS for the DAD configuration.

Note

It is important to point out that WebLogic is certified to host APEX Listener so the Listener configuration can be another way to use WebLogic to access the APEX engine.

The following diagram shows the external mod_plsql configuration:

External Apache has configuration files called httpd.config and dads.conf.

dads.conf holds the name of the DAD of APEX. The dads.conf file has a virtual path mentioned in the Location directive. The URL of external mod_plsql configuration looks like the following:

http://<hostname>:<port>/<virtual_path_in_location_directive>/f?p=app_id:page_no:session_id:Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

The following is a screenshot of a typical dads.conf file. Check out the similarities between the dads.conf file and the attributes configured for EPG:

Directives such as PlsqlRequestValidationFunction (refer the previous screenshot), which are allowed in the context of the Location directive, help us configure the attributes which get configured in EPG using the dbms_epg package. Note that the wwv_flow_epg_include_modules.authorize procedure is declared as the validation function in the dads.conf file. The same function is also configured as the value of the request-validation-function attribute of the apex DAD, which is created along with the installation of Oracle 11g. We can check this out using the following script:

SQL> exec packt_schema.chapter1.chk_apex_req_val_funct();

The wwv_flow_epg_include_modules.authorize procedure is called before creating an APEX session. This function in turn calls the wwv_flow_epg_include_mod_local function. The Gods of APEX have given you the freedom to invoke our own procedures using the apex DAD. All functions on which the ANONYMOUS schema have execute privileges, and have been declared in the wwv_flow_epg_include_mod_local function, can be invoked using the DAD of apex. Let us now have a look at the wwv_flow_epg_include_mod_local function:

create or replace function wwv_flow_epg_include_mod_local(
    procedure_name in varchar2)
return boolean
is
begin
    --return false;
    if upper(procedure_name) in (
          '') then
        return TRUE;
    else
        return FALSE;
    end if;
end wwv_flow_epg_include_mod_local; 

To invoke the procedures from apex DAD, comment the return false; statement and mention the procedure name which you want to invoke using the apex DAD in the in list of the if statement.

If you wish to call your own procedures in Listener configuration, then you can configure the same in the defaults.xml file of the Listener. A demonstration of calling a custom procedure in Listener configuration is shown in the The Listener configuration section of Appendix.

Similarly, we can see that other attributes configured in the EPG DAD are also configured in the external DAD configuration using the Location directive of the dads.conf file. In the previous screenshot of dads.conf file, the directive <Location> has /pls/apex as the virtual path. So you would have the following URL for your apex DAD with this configuration file:

http://<hostname>:<port>/pls/apex/f?p=4550

Note

You can configure more than one DAD in this configuration file by using multiple <Location> directives.

Httpd.conf is another important character in the story. If dads.conf is a super hero, then httpd.conf is super hero's brother and has got his own set of powers. It can hide the trademark APEX URL by helping you set virtual hosts. The RewriteRule directive in httpd.conf takes the concept of concealing the URL to a whole new level. It lets you define patterns of characters and if the URL entered by the user matches the pattern in this directive, then it will redirect to a page which is also defined in the directive. So you can have any URL for APEX, define its pattern in the RedirectRule directive, and then have your APEX application's URL as the URL to which the server will redirect.

APEX Listener configuration

We will be using the Listener configuration in all our discussions henceforth. This is because the Listener is the latest fashion and everyone is talking about it. It is in line with the whole architecture of database server and database Listener. The Listener is Oracle's direction for the future.

We have already seen what mod_plsql can do, so let's look at Listener as well.

Listener is a JEE replacement of mod_plsql and performs all the functions of mod_plsql. These include calling OWA and web toolkit functions to generate the HTML for APEX pages and accepting client requests. Just like mod_plsql was maintaining its connection pool, Listener also maintains a connection pool but it is a JDBC connection pool. Its URL is similar to the EPG URL with the difference that the apex DAD of EPG is replaced by apex web application context. So, it is important to realize that the look of the URL might not have been affected, but the underlying architecture is completely different. DADs are not used here at all. The configuration files and the method of implementation of various features are also completely different. For example, the validation function here is defined in the security.requestValidationFunction parameter of the defaults.xml file. In case of EPG configuration, it is defined in the EPG DAD attribute called request-validation-function. In the case of external mod_plsql configuration, the validation function is in the Location directive of the dads.conf file and the name of the directive holding it is PlsqlRequestValidationFunction. Let's have a look at the architecture now:

The APEX Listener does not have Apache in any form, so it doesn't have mod_plsql. In fact, APEX Listener is built for letting JEE-based web servers harness the powers of APEX and to increase APEX's acceptability.

 

Installing APEX engine and Listener


Till now, we have discussed all possible configurations of APEX. This section is dedicated to setting the APEX environment using the APEX Listener configuration on the WebLogic Server. The assumption here is that you have Oracle database 11.1.0.6.0 or higher installed on your machine. If not, then get the installer from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

We will discuss the following in this section:

  • Installing the APEX engine in the database

  • Creating a WebLogic domain and starting the servers

  • Setting the APEX Listener

  • Creating the APEX workspace

Installing the APEX engine in the database

Perform the following steps for installing the APEX engine in the database:

  1. Create a tablespace for APEX. Change the path of the data file according to your machine:

    CREATE TABLESPACE apex_tablespace datafile 'C:\Oracle11GDB\oradata\orcl\oracle_apex_datafile.dbf' 
    SIZE 500 m 
    autoextend ON maxsize 500 m
  2. If you are using an existing tablespace then use the following script to check if sufficient space is available for APEX:

    SQL> @4-98-9_01_chk_free_space_in_tablespace
    

    275 MB of space is required in APEX tablespace and 100 MB of space is required in the SYSTEM tablespace.

    Besides this, 278 MB of space is required on the filesystem if we use the English only download (apex_4.2_en.zip) and 935 MB of memory is required if we use the full download (apex_4.2.zip).

  3. Run the following query to get the version of PL/SQL Web Toolkit. This version should be greater than 10.1.2.0.6:

    SELECT owa_util.get_version
    FROM dual;
  4. Go to the command prompt and silence the Listener. Enter the following command in the command prompt:

    lsnrctl stop
    
  5. Check the Listener status:

    lsnrctl status
    

    You should get the following error messages in the console:

    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
    
  6. Open the command prompt and navigate to the apex directory under the directory in which apex_4.2.1_en.zip is unzipped.

  7. Connect to SQLPLUS in this command prompt window as sysdba, and execute the following command:

    @apexins apex_tablespace apex_tablespace temp /i/
    
  8. The previous script installs APEX. You should get the following message after the installation is finished:

    A logfile is created in the directory from which the installation was started (the directory in which we unzipped apex_4.2.1_en.zip). The name format of this log is installYYYY-MM-DD_HH24-MI-SS.log. This file will have the following message:

    Thank you for installing Oracle Application Express.
    Oracle Application Express is installed in the APEX_040200 schema.
  9. Run the following query to validate the installation after connecting to the database as sysdba. The status should be VALID:

    SELECT status
    FROM dba_registry
    WHERE comp_id = 'APEX';
    
  10. Run the following script after logging in as sysdba to set the password for the ADMIN user. The ADMIN user will be used to create the APEX workspace and for other admin activities. This script exists under <apex_4.1_en_Unzipped_Location>/apex along with apexins.sql.

    @apxchpwd
    
  11. Restart the Listener. This is done in the command prompt and not in the SQL prompt:

    lsnrctl start
    
  12. Unlock APEX_PUBLIC_USER and change its password after connecting as sys:

    alter user APEX_PUBLIC_USER account unlock; 
    alter user APEX_PUBLIC_USER identified by new_password; 
    
  13. Run the following commands. This is to ensure that the password of APEX_PUBLIC_USER does not expire every few days:

    CREATE PROFILE 
       apex_public_user_profile 
    LIMIT 
       PASSWORD_LIFE_TIME UNLIMITED; 
    ALTER USER apex_public_user PROFILE apex_public_user_profile; 
    

Creating a WebLogic domain and starting the servers

WebLogic will hold our APEX Listener. So, all the action will take place inside this. We can create a domain that has an admin server, a node manager, and a managed server, and configure the managed server to hold the APEX Listener. The managed server can be controlled from the admin server using node manager. We will, however, create a domain with just the Admin server. This reduces the memory requirement and suits a desktop class installation. APEX Listener will be deployed on the Admin server.

Note

The process described here is to install WebLogic—the generic version of 11g on Windows 7 OS and it is assumed that the Oracle 11g database is already installed on your Windows 7 box.

Perform the following steps for creating a WebLogic domain and starting the servers:

  1. Download WebLogic 11g from http://www.oracle.com/technetwork/middleware/ias/downloads/wls-main-097127.html.

  2. Click on the Install button. The wizard will help you install WebLogic on your machine.

  3. Click on QuickStart in the newly installed program group of WebLogic and then click on Getting started with Weblogic Server.

  4. This will open Fusion Middleware Configuration Wizard. Select Create a new weblogic domain in it, and click on Next.

  5. Select Generate a domain configured automatically to support the following products. The Basic weblogic server domain checkbox will be checked by default. Do not select any other checkbox. Click on Next.

  6. Give the name and location of the domain which you want to create. This can be any fancy name. I gave apex_weblogic_domain. Fill these values and click on Next.

  7. Give the username and password for the administrator of this domain and click on Next. My username is apex_weblogic_admin.

  8. Select the Development mode radio button and select Sun SDK as the JDK.

  9. Select the Administration Server checkbox.

  10. If you want your Listener to listen on secured connections using HTTPS, then select the SSL enabled checkbox and give the port number on which this managed server will listen to HTTPS requests.

    If you do not want your APEX Listener to listen to HTTP requests, then do not give a port number in the Listener port textbox. I have not opted for HTTPS. Click on Next.

  11. All set. Click on the Create button to create a domain for your APEX Listener.

    To start the Admin server, go to WebLogic program group where you will see your newly created domain. Get in it to find the link to start the Admin server. Alternately, you could navigate to <MiddlewareWeblogicHome>\user_projects\domains\apex_weblogic_domain\bin in command prompt and execute:

    startWebLogic
    

    apex_weblogic_domain is the name of my domain in the previous path. You should be able to see the following message after starting WebLogic admin server:

Setting the APEX Listener

The prerequisites are:

  • You should have Java 6 Update 20 JDK or higher. Go to command prompt and write the following to check this:

    C:\> Java -version
    
  • You should have Java Servlet Specification 2.3 or higher, and WebLogic 11g R1 certainly has it.

APEX Listener can work in standalone mode and can also be hosted on Glassfish or OC4J or WebLogic. It uses Grizzly to build a scalable web server for itself in standalone mode. Running Listener in standalone mode is not supported in a production environment.

More info on Grizzly can be found at:

http://grizzly.java.net/

The HTTP server API of Grizzly can be found at:

https://grizzly.java.net/docs/2.3/apidocs/org/glassfish/grizzly/http/server/HttpServer.html

Grizzly can be embedded with other applications to provide HTTP services:

https://blogs.oracle.com/oleksiys/entry/grizzly_2_0_httpserver_api

Configuring and deploying APEX Listener on the WebLogic domain

  1. Go to the <Weblogic domain root directory>\config directory and edit the config.xml file in it. Add the <enforce-valid-basic-auth-credentials>false</enforce-valid-basic-auth-credentials> tag just before the closing the </security-configuration> tag. This is important for the Listener to function properly.

  2. Execute the following command:

    C:\> java -jar <path in which you unzipped the listener>\apex_listener.2.0.1.64.14.25\apex.war
    

    You will be prompted to enter the location of the configuration directory. Your Listener configuration files will be stored in the directory which you put here. You will also be prompted to put the database details and the credentials of the APEX_PUBLIC_USER schema along with the credentials of schema for RESTful service configuration. The following screenshot shows the configuration when I executed the previous command:

    Note

    The jar command will work only if the Path environment variable is set to the bin directory of your JDK. An example of bin directory is: C:\Program Files\Java\jdk1.6.0_26\bin.

  3. Start your WebLogic Admin server and open your WebLogic domain console. The link for this is present in your WebLogic program menu under your domain. It has the following form:

    http://<hostname>:<port_number_of_admin_server>/console

  4. Enter your WebLogic Admin credentials. You had created these in step 7 of the Creating a WebLogic domain and starting the servers section.

  5. Click on the Deployments link under Domain Structure panel on the left-hand side of the console.

  6. Click on Install and select apex.war. We had configured apex.war in step 2.

  7. Select Install the deployment as an application radio button and click on the Next button.

  8. Install on the Admin server.

  9. Select the following security model and click on the Next button:

    Custom roles: Use roles that are defined in the administration console; use policies that are defined in the deployment descriptor.

  10. Keep the other default settings and click on Next, and then click on Finish.

  11. Click on the Save button after everything is done.

  12. Open the command prompt and run the following command. This will generate a .war file called i.war that will help APEX to use static resources such as images, logos, SWF, CSS, and JavaScript files which are necessary for APEX to work.

    C:\> java -jar <Directory in which listener is unzipped>\apex.war static <Directory in which APEX is unzipped>\apex\images
    

    You should get an output as shown in the following screenshot:

  13. The previous command will create i.war in the directory from which you executed the command. I executed the command from the C:\Users\vishal directory shown in the previous screenshot and got i.war in the same directory. Install i.war just the way you did apex.war.

 

Creating APEX workspace


Perform the following step for creating APEX workspace:

  1. Log in to the admin console using ADMIN as the username and INTERNAL as the workspace. The password of the ADMIN user can be set using <Directory in which APEX is unzipped>\apex\apxchpwd.sql. The URL for logging in to the INTERNAL workspace is:

    http://<host>:<port>/apex_weblogic/f:p=4550

  2. Click on Manage Workspaces and create a new workspace. Name it PACKT_WORKSPACE.

  3. Select Yes in the Re-use existing schema? dropdown and put PACKT_SCHEMA in the Schema Name text box. If you haven't created PACKT_SCHEMA yet, then go the Steps to create PACKT_SCHEMA section in the Appendix to find the steps of creating this schema.

  4. Put PACKT_ADMIN in the Administrator Username textbox, set its password, and put an e-mail address.

  5. Click on the Create Workspace button to create the workspace.

You can also get a free test drive at http://apex.oracle.com. You can register here, and Oracle gives you a workspace of your own.

 

Understanding the APEX URL


The URL in APEX is related to sessions, debugging, caching, requesting actions, and passing variables. The URL is also closely coupled with the architecture of APEX and is dependent on the server configuration. So let's see it all.

Tip

Colons are a part of the URL and hold special meaning in the URL. Hence, the values passed by the developer cannot contain colons. We will see more on this in the Passing values and navigation section.

A typical URL is as follows http://<hostname>:<port>/dad_name_or_web_context/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly.

Let's dissect this URL in the following sections.

The protocol

Consider the following example:

http://<hostname>:<port>/dad_name_or_web_context/f?p=app_id:page_no:session_id:Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

http is the protocol here. If the SSL (Secured Socket Layer) is enabled, then the protocol will be https. The protocol depends on the web server configuration.

The hostname and port number

Consider the following link:

http://<hostname>:<port>/dad_name_or_web_context/f?p=app_id:page_no:session_id:Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

<port> in the previous URL is the port of your web server. Your web server can be GlassFish, WebLogic, or OC4J if you are using APEX Listener. XMLBD HTTP Protocol Server will be used if you are using EPG configuration. Native Apache, OHS, OAS or OHS + WebLogic can be used if you are using external mod_plsql configuration.

The DAD name and web context

Consider the following link:

http://<hostname>:<port>/dad_name_or_web_context/f?p=app_id:page_no:session_id:Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

Previous versions of APEX used DAD, but APEX 4.x can also use APEX Listener. The Database and web interaction in DAD and Listener configurations section of the Appendix will boost your understanding of this part of the URL.

It contains:

  • A discussion on the creation of a DAD and a demonstration of the use of that DAD to do both database and web interaction. The mod_plsql configuration of APEX also does the same thing but it does it on a bigger scale.

  • A section on calling a custom function from the URL using the Listener configuration.

The f procedure

Consider the following example:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

f in f?p is the name of a stored procedure and p is its argument. We can draw an analogy between f and APPENDIX.DAD_PROC which is defined in the Database and web interaction in DAD and Listener configurations section of the Appendix. f has a single argument, that is, p while APPENDIX.DAD_PROC has val and redirect_url as its arguments.

The application ID in the URL

Consider the following example:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

app_id in the above URL is the application ID of your application. We can hide the application ID by using application alias, but the application ID will be exposed when the user navigates between the pages. This also holds true for page number. Permanent hiding of the app_id and page_id can be controlled only through the web server.

We can, however, use an application alias instead of our app_id value in the URL. Our application alias should be unique in a workspace, and if two workspaces have the same alias then the syntax of our URL will be like the following:

f?p=common_alias:page_alias_or_page_number:&APP_SESSION.&c=desired_workspace

This makes sense since APEX should have a way to uniquely identify the desired application. If two applications have same alias, then APEX will need the support of our workspace name to find the application which it should present to us.

The page number in the URL

Consider the following example:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

page_no is the identifier to your APEX page. The name itself explains it all. It's the page number; nothing more, nothing less. This can also be replaced by page alias.

Session management

Consider the following example:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

session_id is the APEX session ID and is very different from the DB session. Every APEX page request is furnished by an existing or new DB connection. On every page view request, APEX picks a database connection from the pool and then relinquishes it soon after the request is fulfilled. If no connections are free, then a new DB connection is created. The big guys, mod_plsql/Listener, take care of this connection pool.

APEX has some views to check the session activity. Apex_activity_log logs the activity in APEX applications. Apex_workspace_activity_log is a view in the APEX schema that helps us track every activity in APEX. The granularity of the log in this view is page view. So, the setting and resetting of APEX items do not get logged, but every page view does. This logging is done only for the applications which have logging enabled. We can also check the WWV_FLOW_DATA table to see the state of our items in some of our previous sessions. Flow_instance of this table holds our session ID and flow_id holds our application ID.

We can also click on the Session button in the developer toolbar of APEX to see session states of various items.

To capture the session values of various items in your application, do one of the following:

  • :MY_ITEM – It can be used in SQL or PL/SQL. The item name cannot be more than 30 characters long. Use :"MY_ITEM" if item name contains special characters.

  • You can also reference them as substitution strings, that is, as &MY_ITEM. Again, if the name contains special characters, then you can use &"MY_ITEM".

  • Substitution strings can be used in page template and region source. Substitution strings are also sometimes referenced as #MY_ITEM#.

  • If you are coding in PL/SQL, then you have the freedom to use V('MY_ITEM') for getting the value of any item or NV('MY_NUMERIC_ITEM') for getting the value of numeric items.

We can use the following query to check out the setting and resetting of items in the development environment (flow_id = 4550) when we navigate from one page to other to develop our code. This information can also be viewed using public APEX views which expose APEX data for our use:

Select flow_instance, item_name, item_value_vc2, flow_id
From apex_040200.wwv_flow_data
Where flow_id = 4550

The session ID is created at login time and APEX preserves the sanctity of a user session by continually verifying it against the value in the APEX session cookie and the value of the session in the database.

Execute the following anonymous block from SQL Workshop to get a feel of what the session cookie looks like:

BEGIN
  owa_util.print_cgi_env();
END;

Search for Cookie in the output. You might get something like the following when you search for it:

Cookie = WWV_CUSTOM-F_1301809025994046_101=00C1A195B51BDAFDC8B2A3C3E006DCC2; LOGIN_USERNAME_COOKIE=packt_admin; ORA_WWV_ATTRIBUTE_PAGE=4495%2C%23COMMENTS; ORA_WWV_REMEMBER_UN=PACKT_ADMIN:PACKT_WORKSPACE; 

The highlighted piece of code is the hashed session ID. We can match this hashed session ID with the session_id_hashed column of the following query to get the actual APEX session ID from the corresponding value of the id column of the following query.

SELECT session_id_hashed,
  id,
  created_on
FROM apex_040200.wwv_flow_sessions$

For security reasons, APEX hashes the session ID and stores this hashed value in session_id_hashed. This value is then passed in the cookie.

Session ID is our passport to know everything about the relation of this cookie to our APEX session. Use apex_workspace_activity_log to know more:

SELECT *
FROM apex_workspace_activity_log
WHERE apex_session_id = '241352717594401'

More snooping can be done using the APEX_CUSTOM_AUTH.GET_COOKIE_PROPS and APEX_CUSTOM_AUTH.GET_SESSION_ID_FROM_COOKIE procedures of the APEX API. APEX_CUSTOM_AUTH.GET_SESSION_ID can help us get the session ID of the session from which it is called. This value will match with the value in the URL.

Note

There are many more procedures scattered all around the API to set and get various properties of the session. Some examples of these functions are: APEX_UTIL.SET_SESSION_LANG and APEX_UTIL.SET_SESSION_TIME_ZONE.

The zero session ID

This is a novel step to increase the popularity of APEX among web applications on the Internet. The success of a website hugely depends on its appearance in the relevant search results. Google crawler and other bots do the laborious task of crawling through various links on a website and bookmarking them so that they can be presented to the user when related keywords are searched. Since the session ID of APEX is a part of the URL and since it changes in every session, it is virtually impossible to bookmark an APEX page. We can solve this problem in the following three ways:

If an application that does not require authentication is opened with 0 in the session ID place of the URL, APEX will internally generate a session ID, pass it to the browser cookie, and use it to maintain the APEX session, but will show 0 as the session ID in the URL.

You can check this by enabling the audit trail. Follow the steps mentioned in the Enabling auditing on the APEX_PUBLIC_USER schema section of the Appendix to check the calls made to the web toolkit.

Now open your APEX development console with zero session ID. For example

http://localhost:7001/apex_weblogic/f?p=4550:1:0:::::

Now execute the 4-98-9_Appendix_check_calls_by_lsnr_2_web_toolkit script from SQL Prompt after logging into the database using the SYSDBA user. This script can be found in the code pack. You should be able to see nobody:3011100369646501 in the client_id column. The client_id column is a combination of user ID and session ID. So, you see that the URL was opened with the 0 session ID but APEX internally is maintaining an actual APEX session ID.

Execute the following script using SYSDBA to turn off the auditing:

SQL> @4-98-9_Appendix_disable_auditing

Request handling

Consider the following link:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id:Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

Request in the previous URL is to differentiate between the requests going from one page to another page. Let's say we have a report which has links on every row to edit the corresponding row and also to delete the record. Let's say we have another page that handles both these requests. There should be a way to tell the drilldown page that the user has requested to edit the row and not to delete it. Request in the URL is a method to achieve this.

Now every button in the application can set the value of Request to its own name. So, when the user navigates from one page to another on the click of a button, this part of the URL will hold the name of the clicked button. The value of Request can be retrieved using any of the following:

  • Substitution string: &REQUEST

  • PL/SQL: V('REQUEST')

  • Bind variable: :REQUEST

There are many functions in APEX's JavaScript API which set Request, for example, apex.submit(pRequest) and apex.confirm(pMessage, pRequest). The following function call submits the page, sets the value of REQUEST to NEW, and sets the value of P1_ITEM to 555:

apex.submit({
request:"NEW",
set:{"P1_ITEM":555}});

Note

G_REQUEST is a global variable in the APEX_APPLICATION package which holds the most recent value.

Request value is also set when we call an OnDemand APEX process. Syntax is f?p=application_id:page_id:session_id:APPLICATION_PROCESS=process_id.

The syntax gives a feeling that this is applicable only for application processes but it works well for page OnDemand processes as well.

Debugging

Consider the following link:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

Debugging is never fun and can sometimes trouble you more than your mother-in-law. So, it makes sense to learn a few things that will reduce the time you spend with debugging.

DEBUG is actually a flag. You will either see YES or NO in this place of the URL. If the value is set to YES and if you have logged in using developer credentials, then you will be able to see the debug log by clicking on the View Debug button in the developer toolbar at the bottom of an APEX page. You can set the value of DEBUG by clicking on the Debug button on the developer toolbar or by manually putting YES in this part of the URL, and then loading the page. DEBUG gives you the divine eye which lets you see what you otherwise might not. Use it when nothing makes sense to you. Use it when you think APEX is drunk.

Ways to get the value of this flag:

  • Short substitution string: &DEBUG

  • PL/SQL: V('DEBUG')

  • Bind variable: :DEBUG

But all this fun only happens if you have enabled debugging at the application level if it is not already enabled. To do this, click on the Edit Definition link that appears on the right pane under Shared Components and then select Yes in the Debugging dropdown under the Properties section.

In APEX 4.2, debugging is enabled by default. The debug feature in APEX 4.x also gives you a pretty cool graph. The debug report tells you about the processing time for each step. You should store these messages in your own table if you want to check the trends in the performance of APEX reports because the messages in APEX log table age out after two weeks. The debugging messages are logged at log level 4 and can be queried by using the APEX_DEBUG_MESSAGES view.

There is a whole package in APEX called APEX_DEBUG_MESSAGE to give you more freedom to use DEBUG. This package lets you log messages at different log levels. A developer can put his messages too. Again, we can query to retrieve messages of a certain log level. The package also has functions to clear the log messages.

We can use this package to capture the flow of control in our APEX application since we can use this for logging messages in stored PL/SQL procedures and functions as well.

The functions of this package are not affected by the application-level debugging setting.

G_DEBUG is a global variable in the APEX_APPLICATION package which tells whether DEBUG is enabled or not. APEX_PLUGIN_UTIL also has some functions such as APEX_PLUGIN_UTIL.DEBUG_DYNAMIC_ACTION which let us debug Dynamic Actions, the newest gig in APEX.

Other functions such as APEX_PLUGIN_UTIL.DEBUG_PAGE_ITEM let us debug page items. Now we can obviously check the value of an APEX item by clicking on the session link in the developer toolbar. We also have functions such as APEX_PLUGIN_UTIL.DEBUG_PROCESS, and APEX_PLUGIN_UTIL.DEBUG_REGION. These functions do the job that their name suggests.

Note

Debugging of JavaScript in your application is possible using tools such as Firebug of Mozilla Firefox and Microsoft Script Debugger. Similarly, debugging of Dynamic Actions is also a little different. When the application debugging is on and the page is rendered in the debug mode, JavaScript debugging tools such as Firebug will show you information about timing of the firing of a Dynamic Action, its name, and the action resulting in its execution.

It is also important to note that AJAX calls are not tracked by APEX's debugging functionality. Since interactive reports make a lot of AJAX calls, most of the actions performed on interactive reports are also not traceable using the debug functionality. We can, however, call the procedures in APEX_DEBUG_MESSAGE to log messages inside the processes called by AJAX calls. Some information about PPR or interactive report AJAX calls can also be seen in browser script debugging tools. Similarly, flash charts also offer very little scope for debugging.

Error handling

We can specify the name of a function in Error Handling Section of Application Definition or in the Error Handling section in page attributes to declare a function which will generate custom error messages. These places also let us specify the display location of error messages.

The implementation of this function must be:

function <name of function> (
p_error in apex_error.t_error )
return apex_error.t_error_result

Apart from this, we also have the usual error and success display sections in page processes. We also have an error message section in application processes.

The API documentation of APEX_ERROR can be found at http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_error.htm. The documentation also shows an example of the error-handling function.

TKPROF

Consider the following link:

http://localhost:7003/apex_weblogic/f?p=105:1:416650869240101&p_trace=YES

Add &p_trace=YES at the end of a page request to generate the trace for the request. APEX drops the DB connection once a page request is fulfilled and then takes an existing or a fresh connection to furnish a fresh page request. Adding &p_tracle=YES turns on 10046 level trace and does it only for the page request with which it is associated.

We can use the following procedure to find the name of the trace file in which 10046 level trace is written:

  1. Run the following query to find the location in which all the trace files exist on your database

    SQL> select value from v$parameter where name   = 'user_dump_dest';
    
  2. Put the following in the same page for which you want to get the trace:

    select sys_context('userenv','sessionid') from dual;

Use the output of this in the Enter the session id from USERENV context in APEX: prompt while executing the following script to get the trace filename:

SQL> @4-98-9_01_get_trace_file_name

Cache management

Consider the following link:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

We can edit the definitions of regions and pages to enable caches in them. Application items are set and reset multiple times so the cache of these can also be cleared.

The ClearCache part of the URL is used to clear the session state of an item, all items on a page, a group of pages, an application, or all applications. The page which makes the request has to put the "right text" in this position in the URL to clear the cache. The data for a cache is fetched directly from the WWV_FLOW_PAGE_CACHE table instead of the actual execution of the code for the page. The following are the available options to clear the cache:

  • Put the name of an item in this part of the URL if you want to clear the value of a particular item in the requested page.

  • Put the page number in this part of the URL if you want to clear the state of all items and any stateful process on a single page.

  • Put a comma-separated list of pages in this part of the URL if you want to clear the cache for a set of pages. The comma-separated list can include collection names to be reset.

  • Keyword RP resets regional pagination on the requested page.

  • Keyword APP is a tsunami. It clears all pages and application level items of an application.

  • Keyword SESSION is apocalypse. It clears items associated with all applications which have been used in the current session.

    Note

    Please do not confuse this with the SESSION which we discussed earlier. These two have different places in the URL and have totally different meanings. If this is chalk, then that is cheese.

We can use a combination of the the previous options to have multiple effects. Let's say that we want to render 10th page, reset its pagination and clear cache for page 11 and 12. Our URL in such a case will look like the following:

f?p=101:10:2141754341423301::NO:RP,11,12:::

APEX_UTIL has tons of functions for clearing cache. Some of the cache related functions in this package are APEX_UTIL.CLEAR_PAGE_CACHE, APEX_UTIL.CLEAR_USER_CACHE, APEX_UTIL.CACHE_PURGE_STALE, APEX_UTIL.CACHE_PURGE_BY_PAGE, APEX_UTIL.CACHE_PURGE_BY_APPLICATION, APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE, and APEX_UTIL.CACHE_GET_DATE_OF_PAGE_CACHE.

There are many more scattered all across the API to clear certain parts of the application. An example of this can be APEX_COLLECTION.TRUNCATE_COLLECTION, which clears a named collection.

Administrators can clear the cache by following the following steps:

Click on the arrow next to the Administration button on the top, select Manage Services, select Application Cache present in the Manage Meta Data list on the right side of the page.

This will take you to a list which will let you clear cache for pages and regions.

If you are still looking for more ways to clear the cache, then you can use the Session State process for this. You can also use the Link section in Column Attributes of either Classic or interactive reports to clear the cache. Calendar regions also give this option.

Alright, I'm tired of writing about ways to clear cache but I will still keep listing. Dynamic Actions, buttons, branches, lists, bread crumbs, and navigation bar entry, can also be used for clearing cache. The Cache button on the developer toolbar lets you manage cached regions and pages.

Apart from this, there is a relatively unsecure feature called browser-side cache. You can enable this by going to the Browser Security section in Shared Components | Security Attributes. Enabling this lets the browser store vital information and can lead to security problems. Enabling this has also been known to create problems with pages which have partial page refresh content. It is important to note the distinction between this cache and the other cache which we have been talking about. Apart from this paragraph, we have been talking about setting and resetting APEX server-side cache while this one is browser-side cache.

Passing values and navigation

Consider the following link:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

This is the developer's playground Params:ParamValues is to let you set the items in the application to certain values. Params is a comma-separated list of item names whose values have to be set and ParamValues is a comma-separated list of values of the corresponding item names.

Colons are a part of the URL and hold special meaning in the URL. Hence the values themselves cannot contain colons. If there is a possibility of item values containing colons, then we should use translate or replace functions to encrypt the string so that colons are removed from the values. We can then decrypt the encrypted string at the destination page. If a value contains comma, then enclose the value within two backward slashes. For example, \1,234\.

Some of the APEX API functions also let us set the session state of an item. One of these functions is APEX_UTIL.SET_SESSION_STATE.

Making APEX printer friendly

Consider the following link:

http://<hostname>:<port>/dad_name/f?p=app_id:page_no:session_id: Request:Debug:ClearCache:Params:ParamValues:PrinterFriendly

PrinterFriendly is a flag which when set to YES, renders the page in printer-friendly mode. It tells whether the page is in printer-friendly mode or not. We can get the value of this using V('PRINTER_FRIENDLY').

The G_PRINTER_FRIENDLY global variable in the APEX_APPLICATION package is a flag, which tells whether the application is running in print view mode or not.

 

Decoding the APEX page submissions


Now that we understand the URL of APEX and have a broad understanding of the tool, I want to get into the specifics. I want to talk a little about the mechanism used by APEX to submit pages. This section tries to answer the following questions:

  • How does APEX submit pages?

  • How does a submit request flow from the browser to the APEX engine?

  • How do HTML requests get converted to PL/SQL processing and how the switching happens?

APEX creates an HTML form for any APEX page created by us. APEX items defined by us become the various elements of the HTML form. The name of the APEX items become the IDs of the HTML elements and these HTML elements are given any name between p_t01 to p_t200 inclusive.

For example, if we have an item named P2_REPORT_SEARCH in our APEX page, if this item is a textbox and if this is our first page item, then APEX will generate the following HTML for this APEX item:

<input type="text" id="P2_REPORT_SEARCH" name="p_t01" value="" size="30" maxlength="2000" onkeypress="return submitEnter(this,event)" class="text_field" />

The p_t01 to p_t200 range is reserved for page items excluding shuttles and multiselects. Other APEX objects have other ranges of names. Now the big question is, why couldn't APEX assign the names of items as the names of HTML elements? The answer lies in the way in which APEX submits its pages.

The HTML form created by APEX is submitted using the HTTP POST method. We can check this in the form action code at the bottom of this paragraph. The HTML form generated by APEX has wwv_flow.accept in its action attribute. I am sure that wwv_flow.accept is ringing some bells in your head. This looks like the name of some APEX package and procedure. If you check the packages under the APEX_040200 schema, you will see wwv_flow package and the accept procedure in that package. The body of this package is wrapped but you can still check out its specification. Now, every argument in the signature of wwv_flow.accept is directly fed from the HTML form. The HTML name directly maps to the name of the arguments in this procedure. So APEX has to assign the names of the arguments of this procedure as the names of the elements in the HTML form (an APEX page), otherwise the values submitted on the page cannot pass to the APEX engine. Hence, the names of the APEX items cannot be assigned as the names of the HTML elements generated for the items. The HTML element names have to be the names of the arguments of the wwv_flow.accept procedure. This is the entry point of all foreign values that get passed using forms. Let me show you the beginning of a typical HTML form generated by APEX:

<form action="wwv_flow.accept" method="post" name="wwv_flow" id="wwvFlowForm">

You should be able to find a similar string if you right-click on any APEX page and check out the HTML page source.

Now, wwv_flow.accept is invoked, just the way procedure f of f?p is called from the apex DAD/Web context. The Wwv_flow.accept is also a procedure just like f and is called in a similar fashion. Both these functions are called using the apex DAD / web context but in case of f, its argument, that is, p is passed in the URL (passing argument in the URL is called the HTTP GET method) and in case of wwv_flow.accept, the arguments are passed from the form using the HTTP POST method. Arguments passed using the HTTP POST method do not appear in the URL.

Are you wondering that how does a mere mention of the name of a procedure in the action attribute of a form direct the APEX engine to run the procedure and pass values to its arguments? The answer lies in the fact that the action you mention in the action attribute of a form is searched in the calling resource. Let us put this statement to test.

Write the following HTML code in a plain text editor and save it on your desktop as test.html:

 <html> <body>
<form action="dummy_accept" method="post">
  First name: <input type="text" name="p_dummy_item" />
  <input type="submit" value="Submit" />
</form>
</body> </html>

Now open test.html in your web browser and hit the Submit button.

What do you see? A Cannot display the Webpage message? This message is generally not a good omen but in our case, this message is fine. We want to see what is going on in the URL.

When you open test.html, your URL will be similar to C:\Users\acer\Desktop\test.html and after hitting the submit button, test.html in the end of the URL will change to dummy_accept. The URL will then look like C:\Users\acer\Desktop\dummy_accept and you would get a Cannot display the Webpage message because C:\Users\acer\Desktop\dummy_accept does not exist, that is, nothing called dummy_accept exists on C:\Users\acer\Desktop. The important point to note here is that the HTML page tried to search for dummy_accept (the value of form action) in C:\Users\acer\Desktop. Similarly, a typical APEX URL is:

http://<host_name>:<port>/<dad_or_web_context>/f?p=blah,blah,blah

When you submit this APEX page, the HTML form in it is submitted and the resource called is:

http://<host_name>:<port>/<dad_or_web_context>/wwv_flow.accept

The URL has wwv_flow.accept in the end because wwv_flow accept is the value of form action in an APEX page.

The process of calling wwv_flow.accept from http://<host_name>:<port>/<dad_or_web_context>/f?p=blah,blah,blah is similar to the call of C:\Users\acer\Desktop\dummy_accept from C:\Users\acer\Desktop\test.html.

 

APEX behind the scenes


Behind the scenes, APEX Listener / DAD calls web toolkit functions. These calls can be seen by enabling the db audit trail on user configured in the APEX Listener / DAD. Since the APEX developer environment is also an APEX application, these calls can be seen as soon as you open your console login page to log in to the development environment. A point by point process to enable auditing and then see these calls has been described in the APEX behind the scenes section of the Appendix.

More information on db audit trail can be found at http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm.

 

Other web interfaces


There are some less known relatives of mod_plsql, and this section plans to bring these web interfaces to your notice.

Out of these, the most important one is Thoth Gateway. Thoth is an open source interface which has been developed for IIS and performs almost the same functions which mod_plsql does for the Apache servers. Since it is free, it can easily be extended, whenever required.

DBPrism is a SourceForge's initiative to produce a JEE alternative to mod_plsql. More info on DBPrism can be found at http://sourceforge.net/projects/dbprism/.

Mod_owa is functionally similar to mod_plsql but does not share any common code. While serious efforts have been made to use mod_owa for APEX applications, its use is still not completely tested. More information on mod_owa can be found at https://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm.

 

A pessimist's view of APEX


Let's check out some of the most valid criticisms in the following sections.

Cap of 200 page items

As discussed in the Decoding the APEX page submissions section previously, only 200 page items are possible on an APEX page.

SQL Injection

This works like a charm. The only problem is that the charm does not work in your favor.

SQL Injection is PL/SQL hacking and can be stopped to a large extent if proper coding practices are followed. The attack is easy if your PL/SQL code contains dynamic SQL, and the code is generated by concatenating variables to the dynamic SQL string. Again, if your code assumes some implicit conversions and formats, then an experienced hacker can change those assumed formats and demolish your security like it was a deck of cards.

SQL Injection is of two types. Refer to SQL Injection section of the Appendix to see a discussion about both of these types with a working example of one of the types of SQL Injection and various ways to combat SQL Injection.

Cross-site scripting

Cross-site scripting is an attack by which hackers can bypass access controls such as same origin policy of the target server, and hence access sensitive data using client-side scripting such as JavaScript. Check out the Cross Site Scripting section of the Appendix to see the same origin policy in action. You will find a piece of code to use client-side scripting to access vital web resources of the same domain. Cross-site scripting also uses similar code but the attack is from a different domain.

 

Summary


APEX is a sophisticated 4GL RAD development tool and is fun to work with. When you have to do something as boring as work, then you better do it with something as interesting as APEX. It lets you code where it makes sense to involve a human brain and takes care of the layouts and themes.

This chapter presented the wiring of APEX and it also presented a step-by-step process to install APEX engine and APEX Listener. The following chapters will build on the concepts introduced in this chapter. The next chapter will talk about using classic reports for all possible reporting requirements known to man. We will blend classic reports with AJAX, jQuery and Dynamic Actions to orchestrate the variety of uses of each one of these. We have a long road ahead and miles to go before we sleep. It's time to have a short break and a glass of water before we dive into the next chapter.

About the Author

  • Vishal Pathak

    Vishal Pathak is an IT professional with over six years of experience of working in the BI space. He is specialized in Oracle APEX and integrating technologies on a heterogeneous system. His skills include an array of technologies such as Oracle APEX, Oracle BI Publisher, OBIEE, BPEL, PL/SQL, JEE, Jasper Reports, jQuery, and HTML 5. He has a degree in Electrical and Electronics Engineering from the Birla Institute of Technology, Mesra. He has worked with multinational giants such as TCS, Wipro, Capgemini, and CTS. He has led and worked on many huge Business Intelligence implementations across diverse industry sectors. Some of the major clients that he has worked with include British Telecommunications, Uninor, Department of Social Services (LA DPSS), Marriot and Sony DADC. He is also a Sun Certified Java Programmer and an Oracle Certified Associate and blogs about his technical thoughts at http://obiee-oracledb.blogspot.com. He lives in India and he loves to trek frequently. Sitting in a secluded part of his garden, thinking about the world and its numerous complexities, and appreciating its beauty is one of his favorite activities.

    Browse publications by this author

Latest Reviews

(2 reviews total)
Great book and it's really helpful for me...
C'est un livre très intéressant; J'invite ceux qui veulent aller plus loin dans le reporting avec APEX à le lire.
Book Title
Unlock this book and the full library for FREE
Start free trial