Thursday, 11 July 2013

My upcoming book - Oracle APEX 4.2 reporting

I have finally finished writing my book on Oracle APEX reporting. The book will be  is now available at

Although the book primarily projects Oracle APEX as a reporting solution, it also speaks about the integration of APEX with a number of other technologies such as Apache FOP, Apache Cocoon, PL_PDF, PL_FPDF, iReports & Jasper, Eclipse BIRT, BI Publisher 11G, Oracle Business Intelligence 11G, BPEL, Oracle forms, Google API and Oracle R Enterprise.

The the text of the book is coupled with an APEX application with every chapter. The application of every chapter contains code to give you a live demo of the subjects discussed in the chapter.

The following is a brief overview of the contents of the book

Chapter 1: Know Your Horse Before You Ride It
This chapter lays the foundation of understanding the tool. A deep understanding of this chapter is vital to know the framework of APEX. This chapter talks about the anatomy of APEX and enables the reader to set up an environment. 

What is really new in the new avatar of APEX?
APEX configurations
                APEX configuration using DAD
                                Internal mod_plsql configuration
                                External mod_plsql configuration
                APEX Listener configuration
Installing the APEX engine and Listener
                Installing the APEX engine in the database
                Creating a WebLogic domain and starting the servers
                Setting the APEX Listener
                                Configuring and deploying APEX Listener on the WebLogic domain
Creating APEX workspace
Understanding the APEX URL
                The protocol
                Host name and port number
                DAD name and web context
                The f procedure
                Application ID in the URL
                Page number in the URL
                Session ID in the URL and Session Management
                                The zero session ID
                Request handling
                                Error handling
                Cache management
                Passing values and navigation
                Making APEX printer friendly
Decoding APEX page submissions
APEX behind the scenes
Other web interfaces
A pessimist’s view of APEX
                Cap of 200 page items
                SQL injection
                Cross site scripting

Chapter 2: Reports as We All Know Them
This chapter is dedicated to ethnic reporting. The reports introduced in this chapter exist as an out of the box feature of most reporting technologies. The chapter presents ways and means to implement these known reporting requirements in APEX.

Creating database objects and installing the reference application
Implementing external table authentication
Displaying environment properties using the USERENV namespace
Displaying the CGI environment variables
Implementing classic report search functionality
Enabling sorting and CSV download
Implementing group reports
                Using Break Formatting in classic reports
                Using JavaScript to modify report
                Using grouping sets and HTML formatting in a query
Additional methods of formatting
                Highlighting the searched text in a report
                Editing a template for additional formatting
Implementing matrix Report
                Understanding with clause
                Understanding Oracle 11g’s pivot operator
                Understanding String Aggregation techniques
Data highlighting using Dynamic Actions and JQuery
Advanced formatting using APEX templates
Understanding dynamic query region in APEX
Implementing hierarchical reports
                Hierarchical reports with drilldown
                Creating dynamic messages using the substitution variables
                Creating dynamic breadcrumbs for drill ups
                Creating hierarchical report with Connect By clause
                Creating hierarchical report using recursive with clause
                Creating a tree on hierarchical data
Understanding methods to upload files in APEX
                Using DA to set page items and side effects of PPR
                                Work-around to the side effects of PPR
                Using substitution variables for labels
                Auto feeding one APEX item based on another
                Method to upload files using WWV_FLOW_FILES
Understanding download methods
                Download using APEX format mask
                Download using developer defined stored function
                Download using APEX_UTIL.GET_BLOB_FILE_SRC
                Download using the p process
Implementing soft deletion with AJAX and APEX templates
DML operations and report filtering using DA, JavaScript and page processes
                Filtering report and logging values using Dynamic Actions
                AJAX: Two way communication between page process and JavaScript
Implementing a tabular form
                Creating a tabular form
                Changing item type on user event
Implementing master detail report and displaying complex types
                Displaying complex types and varray in a report
Implementing time series reports
                Creating aggregation on partitioned time dimension region
                Time series analysis with analytical functions and time dimension
                                Using query partition clause for data densification
                Creating aggregations using model clause
Implementing data-level security
                Using VPD
                Using query filters

Chapter 3: In the APEX Mansion: Interactive Reports
Love to talk? Here are interactive reports for you. These reports are exclusive to APEX. These reports let a user do a lot more with a report. The possible tasks include highlighting, filtering, coloring, selecting columns, creating aggregates etc. These reports are to a client what a box of crayons and a drawing book are to a child.

About the reference application
Important features of Interactive Reports
                Search functionality
                Using the Select Columns feature of the Actions Menu
                Using the Filtering feature of the Actions Menu
                                Linking Interactive Rreports
                Using the Rows per Page feature of the Actions Menu
                                Customizing the number of rows in an IR
                Using the Formatting options of the Actions Menu
                                Using Control Break
                                Using the Compute option
                                Using Charts in an IR
                Using the Flashback feature of the Actions Menu
                Using the Save Report feature of the Actions Menu
                Using the Reset and Download features of the Actions Menu
                                Configuring eE-mail functionality
                                Downloading in CSV and HTML formats
                                Downloading in PDF format
                Using the Subscription feature of the Actions Menu
Other configurable attributes of an IR
                Using the Link Column section onf the Report Attributes page
                                Using the Column Group section on the Report Attributes page
                Using the Icon View section onf the Report Attributes page
                Using the Detail View section onf the Report Attributes page
                Using the Advanced section onf the Report Attributes page
Using Dynamic Actions (DA) to add custom functions in Actions Menu
Using CSS in IR
                Implementing conditional formatting using CSS
                                Using APEX region attributes with CSS for formatting
                Implementing conditional formatting using JQuery and CSS
                Altering APEX theme attributes for formatting
                                Changing the font color of alternate rows in APEX
Understanding the Process of any customizations in IR
Using APEX views to create Classic report on saved IR Reports
                Capturing report id using JavaScript
Creating Multiple IR on the same page
Authorizing user groups to view report columns
Creating Interactive Report Dashboard
Understanding Dynamic Interactive Reports
                Using Native PL/SQL Table Function approach and conditional columns
                                Using Parallel Enabled Table functions
                Understanding Interface Table function approach
                                Understanding ODCITableDescribe Function
                                Understanding ODCITablePrepare Function
                                Understanding ODCITableStart Function
                                Understanding ODCITableFetch Function
                                Understanding ODCITableClose Function
                Understanding the Collection Approach

Chapter 4: The Fairy Tale Begins  – Advanced Reporting
This chapter brings a different flavor to reporting and the clients love this flavor. This chapter deals with images and all kinds of animations along with advanced reporting features. This chapter helps you engineer secret drilldown tunnels and magical mystical paths. This chapter is really the difference between an average reporting experience and a user friendly, visually soothing, sensually appealing, smooth reporting experience.

The reference application
Using LDAP authentication
                Process to get the necessary DN from JXplorer
Creating sparkline reports
Creating reports with slider
Creating an APEX HTML chart
                Displaying the top N and the bottom N rows in HTML chart
                Understanding APEX HTML bar chart behind the scenes
                Creating personalized HTML charts
Creating report from XMLTYPE
Creating Google Visualization charts
Creating Flash charts
                Customizing chart XML
                Understanding AnyChart options using the doughnut chart
                Discussion on scatter and 3-D stacked charts
                Discussion on gauge chart
                Discussion on gantt chart
                Discussion on candlestick chart
                Creating flash image maps
Creating calendar
Creating report with images
Creating dialog box
Creating context menu
Creating wizard and using hierarchical queries with regular expression
                Setting different validations for different parts of a page processes

Chapter 5: Flight to Space Station: Advanced APEX
This chapter opens a new dimension to conventional reporting. This chapter presents advanced reporting methods and also shows the use the newest features of APEX 4.2 to standardize report developments. The world can exist without these but then merely existing is not fun.

Creating HTML image maps
                Server side Image maps
                Client side image maps
Creating PL/SQL Server Pages (PSP)
Understanding and using loadjava utility
Creating funnel charts using FusionCharts
Creating tag cloud
Creating plugins
                Creating item and page process plugin
                Creating DA plugin
Creating websheet application
                Understanding datagrid
                Understanding reports
                Understanding the features of administration and view dropdowns
                Understanding Websheet Help menu
                Sharing websheets using ACL
Configuring mail
Downloading APEX application without web server
Understanding Oracle OLAP cubes
Understanding Oracle's advanced queuing
Understanding other APEX features
                Understanding locking and unlocking of pages and team development
                Understanding database object dependencies report
                Understanding advisor
                Understanding shortcuts
                Understanding data loading wizard
                Understanding application express views
Understanding background jobs in APEX
Knowing about important functions from the API
                Knowing about the APEX API
                Knowing about the database API

Chapter 6: Using PL/SQL Reporting Packages, Jasper and Eclipse 

This chapter introduces us to an array of technologies which can be used with APEX. These technologies include PL/PDF, PL_FPDF, Eclipse BIRT and Jasper Reports.

APEX reporting using PL/PDF and PL_FPDF
                Creating PDF using PL/PDF
                Creating PDF using PL_FPDF
Process to create RTF, XLS, CSV, and HTML documents
                RTF, CSV and HTML printing
                XLS document generation
APEX reporting using fop and Ccocoon
                APEX reporting using fop.war and WebLlogic
                APEX reporting using Apache Cocoon and WebLlogic
Creating Jasper reports
                Using Jasper in APEX
Creating reports using Eclipse BIRT and integrating with APEX
                Running the BIRT report on WebLogic

Chapter 7: Integrating APEX with OBIEE (discussion includes BI Publisher)
BI Publisher has been the most popular reporting engine and is integrated with most enterprise solutions. It has been everybody’s sweetheart because people do not have to shell out a lot of cash for it. It gives pretty reports, development environment (MS word can be used for creating templates) is familiar and Oracle is coupling it with everything else in the oracle world. OBIEE, on the other hand, is the grand old lady of BI world. It has been facilitating business examination from the beginning of modern analytic culture. This chapter presents the use of both these technologies, shows the requirement which can be easily implemented in these technologies and also talks about the integration of these technologies with APEX.

Understanding Oracle Fusion Management architecture
Understanding OBIEE
                Understanding The BI server
                Understanding The BI presentation
                Understanding Dashboards, Analysis (Answers) and Filters
                Understanding KPIs and KPI watchlist
                Understanding Actions
                Understanding OBIEE Mapviewer
                Understanding Strategy management
                Configuring gmail smtp server
                Understanding Agents
Integrating OBIEE with APEX
                Integration using web services
                Integration using GoURL
Understanding BI Publisher
                Creating and mailing reports in BI Publisher
                                Creating Data model in BI Publisher
                Using the BIP’s MS word plugin to make rtf templates
                Assembling the RTF template and then data model together
                Scheduling reports and Email delivery in BI Publisher
                Creating Bar code reports in BI Publisher
                Dynamic BIP
Integrating BI Publisher with APEX
                Using Convert servlet in Instance Settings
                Using Web services
                BIP Guest folder

Chapter 8: All about web services and integrations
I adore this workshop. Here we combine the best of every tool to make an unbeatable machine. Some of the highlights of this chapter include BPEL implementations with Human workflows and their use in APEX, integration with technologies such as Oracle R & Google API, understanding various ways to create web services in APEX and converting Oracle forms to APEX.

Understanding Reports on Webservices and Collections
                Understanding Native XMLDB web services
                                Setting XMLDB Webservices
                                Creating report on Native XMLDB webservices
                                Using XMLTable to parsing web service response
                                Using native webservice to return a collection of rows
                Querying XMLDB using web service
                Implementing and using RESTful web services using Resource Templates
                                Configuring RESTful webservices using Resource Templates
                                Creating RESTful webservices using Resource Templates
                                Parsing JSON objects and the using MAKE_REST_REQUEST
                Creating and using RESTful PLSQL web service
Understanding BPEL
                What is BPEL
                Installing BPEL
                Developing a BPEL Composite
                Deploying BPEL code
                Testing the BPEL code
                Understanding BPEL Human Workflows
                                Understanding Tthe General section
                                Understanding Tthe Assignment section
Integration with SAP Crystal reports
Migrating from MS Access to APEX
Migrating from Oracle Forms and Reports
Integration with Google API
Integration with Oracle R Enterprise

Chapter 9: Performance  analysis
One can never have enough of this. This chapter is focused on understanding various ways to fine tune APEX applications for a good user experience. The chapter talks ways to tune and debug client side code such as JavaScript. It also talks about a number of pointers that can help in the development of better APEX applications and finally it talks about the database tools that can help us understand and fix performance bottlenecks.

Tuning pointers for Development
                Using v()
                Using Page and region caching
                Understanding Weighted page performance of APEX
                Using the #TIMING# substitution string
                Choosing Pagination scheme
                Tuning the like comparisons
                Using bind variables
                Using Materialized views
                Using Bulk operations
                Using Sequences
                Understanding Indexes
                                Understanding Bitmap index
                                Understanding Reverse key index
                                Ordering columns in Index
                Using WITH clause
                Understanding Partitioning Strategies
                Understanding Anonymous blocks vs stored procedures
                Using Star transformation
                Understanding Clustering of tables
                Understanding Parallelism
                Using Code inlining
                Understanding Short circuit evaluation
                Understanding PLSQL_CODE_TYPE
                Using PLSQL datatypes
                Using HTML in SQL
                Implementing XML Pivot queries
                Understanding Denormalization
                Understanding Secure files, deduplication and large object storage
Discussions on HTML, JavaScript and Client side tools
                Understanding Image storage
                Using Browser specific tools
                Using Minified Javascript
                Compressing HTML for better performance
                APEX Listener parameters
Discussions on Database tools  for performance tuning
                Using PL/SQL Hierarchical Profiler and DBMS_PROFILER
                Understanding Data guard
                Understanding SGA Result cache
                Understanding SQL Work areas
                Using data compression
                Understanding Segment space problems
                Understanding Database Resource Manager`
                Understanding SQL Access Advisor
                Understanding Temporary tablespace
                Understanding SQL Monitoring
                Understanding DB_FILE_MULTIBLOCK_READ_COUNT
                Understanding Extended statistics
                Understanding SQL Performance Analyzer
                Understanding SQL Tuning Advisor
                Understanding Pinning in Keep Pool
                Understanding Automatic Database Diagnostic Monitor (ADDM)
                Understanding Automatic Workload Repository (AWR)
                Understanding Index Organized Tables
                Understanding Row chaining and row migration
                Understanding Clustering factor
                Understanding Active Session History report

Please share your thoughts on this ..

Blog updated on 29th Aug 2013


Anonymous said...

Hi Vishal,

Is your book available in India ???

Vishal Pathak said...

It will be available everywhere around the world once it is published. The book can be pre ordered at

The book is expected to be published in the next month


Balaji Subramaniam said...

Congrats Man!...

Martin D'Souza said...

Very comprehensive list! Looking forward to reading it when it comes out.

Vishal Pathak said...

Thanks Martin. The book is out. It is now available at


Damir Vadas said...


Did I missed something but I didn't find how to pass parameters to Jasper Reports with proper security...I mean no way for URL tampering or any kind of modifications to sent parameters to Jasper.

Hope I'll get an answer ..


Vishal Pathak said...

Hi Damir,

On the question of Authentication:

The following code is a part of the book.It shows the use of utl_http.set_authentication function to pass the credentials that can be used for authentication. The book also demonstrates the method to pass credentials from the URL itself using other examples

l_http_req UTL_HTTP.req; l_http_resp UTL_HTTP.resp;
l_web_serv_end_pt_url VARCHAR2 (2000) := 'http://localhost:8444/jasperserver-pro/rest_v2/reports/reports/samples/AllAccounts.pdf';
l_blob BLOB; l_raw RAW(32767);
l_http_req := UTL_HTTP.begin_request (l_web_serv_end_pt_url, 'GET', 'HTTP/1.1');
DBMS_LOB.createtemporary(l_blob, FALSE);
utl_http.set_authentication(r => l_http_req, username => 'jasperadmin', password => 'jasperadmin', scheme => 'Basic', for_proxy => FALSE);
l_http_resp := UTL_HTTP.get_response (l_http_req);
UTL_HTTP.read_raw(l_http_resp, l_raw, 32767);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
WHEN UTL_HTTP.end_of_body THEN
owa_util.mime_header('application/pdf',false); htp.p('Content-length: ' || dbms_lob.getlength(l_blob));
owa_util.http_header_close; wpg_docload.download_file(l_blob);

On the question of passing arguements:

The following syntax can be used to pass arguements to restful webservice.


The book does not mention this syntax of passing arguements but has a link to
The above documentation talks in detail about this syntax

Other methods

Apart from RESTful webservice, SOAP web service of Jasper can also be used in APEX.

The book has a link to as well. This link also gives a method to link Jasper to APEX


Damir Vadas said...

THX for reply but...
is exactly what I want to avoid because user may change arguments and make URL tampering very easy ...

And this was mine initial ask ... how to avoid that?

Vishal Pathak said...

Hi Damir,

Jasper has SOAP web services as well. You can try those. The following guide talks in detail about these webservices.