(For more resources related to this topic, see here.)
The objective of the first chapter is to quickly introduce you to the technology and then dive deep into the understanding the fabric of the tool. The chapter also helps you set the environment, which will be used throughout the book.
Chapter 1, Know Your Horse Before You Ride It, starts with discussing the various features of APEX. This is to give heads up to the readers about the features offered by the tool and to inform them about some of the strengths of the tool. In order to understand the technology better, we discuss the various web server combinations possible with APEX, namely the Internal mod_plsql, External mod_plsql, and Listener configuration. While talking about the Internal mod_plsql configuration, we see the steps to enable the XMLDB HTTP server. In the Internal mod_plsql configuration, Oracle uses a DAD defined in EPG to talk to the database and the web server. So, we try to create a miniature APEX of our own, by creating our DAD using it to talk to the database and the web server. We then move on to learn about the External mod_plsql configuration. We discuss the architecture and the roles of the configuration files, such as dads.conf and httpd.conf. We also have a look at a typical dads.conf file and draw correlations between the configurations in the Internal and External mod_plsql configuration. We then move on to talk about the wwv_flow_epg_include_mod_local procedure that can help us use the DAD of APEX to call our own stored PL/SQL procedures. We then move on to talk about APEX Listener which is a JEE alternative to mod_plsql and is Oracle’s direction for the future.
Once we are through with understanding the possible configurations, we see the steps to set up our environment. We use the APEX Listener configuration and see the steps to install the APEX engine, create a Weblogic domain, set the listener in the domain, and create an APEX workspace.
With our environment in place, we straight away get into understanding the anatomy of APEX by analyzing various parts of its URL. This discussion includes a natter on the session management, request handling, debugging, error handling, use of TKPROF for tracing an APEX page execution, cache management and navigation, and value passing in APEX. We also try to understand the design behind the zero session ID in this section.
Our discussions till now would have given you a brief idea about the technology, so we try to dig in a little deeper and understand the mechanism used by APEX to send web requests to its PL/SQL engine in the database by decoding the APEX page submission. We see the use of the wwv_flow.accept procedure and understand the role of page submission. We try to draw an analogy of an APEX form with a simple HTML to get a thorough understanding about the concept.
The next logical thing after page submission is to see the SQL and PL/SQL queries and blocks reaching the database. We turn off the database auditing and see the OWA web toolkit requests flowing to the database as soon as we open an APEX page.
We then broaden our vision by quickly knowing about some of the lesser known alternatives of mod_plsql.
We end the chapter with a note of caution and try to understand the most valid criticisms of the technology, by understanding the SQL injection and Cross-site Scripting (XSS).
After going through the architecture we straight away spring into action and begin the process of learning how to build the reports in APEX. The objective of this chapter is to help you understand and implement the most common reporting requirements along with introducing some interesting ways to frame the analytical queries in Oracle. The chapter also hugely focuses on the methods to implement different kinds of formatting in the APEX classic reports.
We start Chapter 2, Reports, by creating the objects that will be used throughout the book and installing the reference application that contains the supporting code for the topics discussed in the second chapter.
We start the chapter by setting up an authentication mechanism. We discuss external table authentication in this chapter. We then move on to see the mechanism of capturing the environment variables in APEX. These variables can help us set some logic related to a user’s session and environment. The variables also help us capture some properties of the underlying database session of an APEX session. We capture the variables using the USERENV namespace, DBMS_SESSION package, and owa_util package.
After having a good idea of the ways and means to capture the environment variables, we build our understanding of developing the search functionality in a classic APEX report. This is mostly a talk about the APEX classic report features, we also use this opportunity to see the process to enable sorting in the report columns, and to create a link that helps us download the report in the CSV format.
After trying our hand at formatting, we try to understand the mechanism to implement matrix reports in APEX. We use matrix reports to understand the use of the database features, such as the with clause, the pivot operator, and a number of string aggregation techniques in Oracle. The discussion on the string aggregation techniques includes the talk on the LISTAGG function, the wm_concat function, and the use of the hierarchical queries for this purpose. We also see the first use of the APEX items as the substitution variables in this book. We will see more use of the APEX items as the substitution variables to solve the vexed problems in other parts of the book as well. We do justice with the frontend as well, by talking about the use of jQuery, CSS, and APEX Dynamic Actions for making the important parts of data stand out. Then, we see the implementation of the handlers, such as this.affectedElements and the use of the jQuery functions, such as this.css() in this section. We also see the advanced formatting methods using the APEX templates. We then end this part of the discussion, by creating a matrix report using the APEX Dynamic Query report region.
The hierarchical reports are always intriguing, because of the enormous ability to present the relations among different rows of data, and because of the use of the hierarchical queries in a number of unrelated places to answer the business queries. We reserve a discussion on the Oracle’s hierarchical queries for a later part of the section and start it, by understanding the implementation of the hierarchical reports by linking the values in APEX using drilldowns. We see the use of the APEX items as the substitution variable to create the dynamic messages. Since we have devised a mechanism to drill down, we should also build a ladder for the user to climb up the hierarchical chain. Now, the hierarchical chain for every person will be different depending on his position in the organization, so we build a mechanism to build the dynamic bread crumbs using the PL/SQL region in APEX. We then talk about two different methods of implementing the hierarchical queries in APEX. We talk about the connect, by clause first, and then continue our discussion by learning the use of the recursive with clause for the hierarchical reporting. We end our discussion on the hierarchical reporting, by talking about creating the APEX’s Tree region that displays the hierarchical data in the form of a tree.
The reports are often associated with the supporting files that give more information about the business query. A typical user might want to upload a bunch of files while executing his piece of the task and the end user of his action might want to check out the files uploaded by him/her. To understand the implementation of this requirement, we check out the various ways to implement uploading and downloading the files in APEX.
Discussion on the methods to upload the files is immediately followed by talking about the ways to download these files in APEX. We nest the use of the functions, such as HTF.ANCHOR and APEX_UTIL.GET_BLOB_FILE_SRC for one of the ways to download a file, and also talk about the use of dbms_lob.getlength along with the APEX format mask for downloading the files. We then engineer our own stored procedure that can download a blob stored in the database as a file. We end this discussion, by having a look at the APEX’s p process, which can also be used for downloading.
While most business intelligence and reporting solutions are a one way road and are focused on the data presentation, Oracle APEX can go a step further, and can give an interface to the user for the data manipulations as well. To understand this strength of APEX, we have a look at the process of creating the tabular forms in APEX. We extend our understanding of the tabular forms to see a magical use of jQuery to convert a certain sections of a report from display-only to editable textboxes.
We move our focus from implementing the interesting and tricky frontend requirements to framing the queries to display the complex data types. We use the string aggregation methods to display data in a column containing a varray.
Time dimension is one of the most widely used dimension in reporting circles and comparing current performance with the past records is a favorite requirement of most businesses. With this in mind, we shift our focus to understand and implement the time series reports in APEX. We start our discussion by understanding the method to implement a report that shows the contribution of each business line in every quarter. We implement this by using partitioning dimensions on the fly. We also use the analytical functions, such as ratio_to_report, lead, and lag in the process of creating the time series reports. We use our understanding of time dimension to build a report that helps a user compare one time period to the other. The report gives the user the freedom to select the time segments, which he wishes to compare. We then outwit a limitation of this report, by using the query partition clause for the data densification.
We bring our discussion on reports based on time dimension, by presenting a report based on the modal clause to you. The report serves as an example to show the enormous possibilities to code, by using the modal clause in Oracle.
We bring our discussion on reports based on time dimension, by presenting a report based on the modal clause to you. The report serves as an example to show the enormous possibilities to code, by using the modal clause in Oracle.
Chapter 3, In the APEX Mansion – Interactive Reports is all about Interactive Reports and the dynamic reporting. While the second chapter was more about the data presentation using the complex queries and the presentation methods, this chapter is about taking the presentation part a step ahead, by creating more visually appealing Interactive Reports.
We start the discussion of this chapter, by talking about the ins and outs of Interactive Reports. We postmortem this feature of APEX to learn about every possible way of using Interactive Reports for making more sense of data. The chapter has a reference application of its own, which shows the code in action.
We start our discussion, by exploring at various features of the Actions menu in Interactive Reports. The discussion is on search functionality, Select Columns feature, filtering, linking and filtering Interactive Reports using URLs, customizing Rows per page feature of an IR, using Control Break, creating computations in IR, creating charts in IR, using the Flashback feature and a method to see the back end flashback query, configuring the e-mail functionality for downloading a report, and for subscription of reports and methods to understand the download of reports in HTML, CSV, and PDF formats.
Once we are through with understanding the Actions menu, we move on to understand the various configuration options in an IR. We talk about the Link section, the Icon View section, the Detail section, the Column Group section, and the Advanced section of the Report Attributes page of an IR. While discussing these sections, we understand the process of setting different default views of an IR for different users.
Once we are through with our dissection of an IR, we put our knowledge to action, by inserting our own item in the Actions menu of an IR using Dynamic Actions and jQuery.
We continue our quest for finding the newer formatting methods, by using different combinations of SQL, CSS, APEX templates, and jQuery to achieve unfathomable results. The objectives attained in this section include formatting a column of an IR based on another column, using CSS in the page header to format the APEX data, changing the font color of the alternate rows in APEX, using a user-defined CSS class in APEX, conditionally highlighting a column in IR using CSS and jQuery, and formatting an IR using the region query.
After going through a number of examples on the use of CSS and jQuery in APEX, we lay down a process to use any kind of changes in an IR. We present this process by an example that changes one of the icons used in an IR to a different icon.
APEX also has a number of views for IR, which can be used for intelligent programming. We talk about an example that uses the apex_application_page_ir_rpt view that show different IR reports on the user request.
After a series of discussion on Interactive Reports, we move on to find a solution to an incurable problem of IR. We see a method to put multiple IR on the same page and link them as the master-child reports.
We had seen an authentication mechanism (external table authentication) and data level authorization in Chapter 2, Reports. We use this chapter to see the object level authorization in APEX. We see the method to give different kinds of rights on the data of a column in a report to different user groups.
After solving a number of problems and learning a number of things, we create a visual treat for ourselves. We create an Interactive report dashboard using Dynamic Actions. This dashboard presents different views of an IR as gadgets on a separate APEX page.
We conclude this chapter, by looking at advanced ways of creating the dynamic reports in APEX. We look at the use of the table function in both native and interface approach, and we also look at the method to use the APEX collections for creating the dynamic IRs in APEX.
Chapter 4, The Fairy Tale Begins – Advanced Reporting, is all about advanced reporting and pretty graphs. Since we are talking about advanced reporting, we see the process of setting the LDAP authentication in APEX. We also see the use of JXplorer to help us get the necessary DN for setting up the LDAP authentication. We also see the means to authenticate an LDAP user using PL/SQL in this section. This chapter also has a reference application of its own that shows the code in action.
We start the reporting building in this chapter, by creating the Sparkline reports. This report uses jQuery for producing the charts. We then move on to use another jQuery library to create a report with slider. This report lets the user set the value of the salary of any employee using a slider.
We then get into the world of the HTML charts. We start our talk, by looking at the various features of creating the HTML chart regions in APEX. We understand a method to implement the Top N and Bottom N chart reports in the HTML charts. We understand the APEX’s method to implement the HTML charts and use it to create an HTML chart on our own. We extend this technique of generating HTML from region source a little further, by using XMLTYPE to create the necessary HTML for displaying a report.
We take our spaceship into a different galaxy of the charts world and see the use of Google Visualizations for creating the charts in APEX.
We then switch back to AnyChart, which is a flash charting solution, and has been tightly integrated with APEX. It works on an XML and we talk about customizing this XML to produce different results. We put our knowledge to action, by creating a logarithmic chart and changing the style of a few display labels in APEX. We continue our discussion on AnyChart, and use the example of Doughnut Chart to understand advanced ways of using AnyChart in APEX. We use our knowledge to create Scatter chart, 3D stacked chart, Gauge chart, Gantt chart, Candlestick chart, Flash image maps, and SQL calendars.
We move out of the pretty heaven of AnyChart only to get into another beautiful space of understanding the methods of displaying the reports with the images in APEX. We implement the reports with the images using the APEX’s format masks and also using HTF.IMG with the APEX_UTIL.GET_BLOB_FILE_SRC function.
We then divert our attention to advanced jQuery uses such as, the creation of the Dialog box and the Context menu in APEX. We create a master-detail report using dialog boxes, where the child report is shown in the Dialog box.
We close our discussion in this chapter, by talking about creating wizards in APEX and a method to show different kinds of customized error messages for problems appearing at different points of a page process.
Chapter 5, Flight to Space Station: Advanced APEX, is all about advanced APEX. The topics discussed in this chapter fall into a niche category of reporting the implementation. This chapter has two reference applications of its own that shows the code in action.
We start this chapter, by creating both the client-side and server-side image maps APEX. These maps are often used where regular shapes are involved. We then see the process of creating the PL/SQL Server Pages (PSPs). PSPs are similar to JSP and are used for putting the PL/SQL and HTML code in a single file. Loadpsp then converts this file into a stored procedure with the necessary calls to owa web toolkit functions. The next utility we check out is loadjava. This utility helps us load a Java class as a database object. This utility will be helpful, if some of the Java classes are required for code processing.
We had seen the use of AnyChart in the previous chapter, and this chapter introduces you to FusionChart. We create a Funnel Chart using FusionChart in this chapter. We also use our knowledge of the PL/SQL region in APEX to create a Tag Cloud.
We then stroll into the world of the APEX plugins. We understand the interface functions, which have to be created for plugins. We discuss the concepts and then use them to develop an Item type plugin and a Dynamic Action plugin. We understand the process of defining a Custom Attribute in a plugin, and then see the process of using it in APEX.
We move on to learn about Websheets and their various features. We acquaint ourselves with the interface of the Websheet applications and understand the concept of sharing Websheets. We also create a few reports in a Websheet application and see the process of importing and using an image in Websheets. We then spend some time to learn about data grids in Websheets and the method to create them. We have a look at the Administration and View dropdowns in a Websheet application.
We get into the administration mode and understand the process of configuring the sending of mails to Gmail server from APEX.
We extend our administration skills further, by understanding the ways to download an APEX application using utilities, such as oracle.apex.APEXExport.
Reporting and OLAP go hand in hand, so we see the method of using the OLAP cubes in APEX. We see the process of modeling a cube and understand the mechanism to use its powerful features.
We then have a talk about Oracle Advanced Queues that can enable us to do reliable communication among different systems with different workloads in the enterprise, and gives improved performance.
We spend a brief time to understand some of the other features of APEX, which might not be directly related to reporting, but are good to know. Some of these features include locking and unlocking of pages in APEX, the Database Object Dependencies report, Shortcuts, the Dataloading wizard, and the APEX views.
We bring this exclusive chapter to an end, by discussing about the various packages that enable us to schedule the background jobs in APEX, and by discussing various other APEX and Database API, which can help us in the development process.
Chapter 6, Using PL/SQL Reporting Packages, Jasper, and Eclipse BIRT, begins a new phase of our journey. Since we have explored most of the reporting features of APEX, we set our eyes on other technologies that can serve as extensions of APEX. We talk about creating reports in them and then using these reports in APEX. This chapter has two reference applications of its own that shows the code in action.
We begin this chapter, by exploring some of the PL/SQL wrappers that can help us do the PDF printing. The chapter begins, by discussing the process of writing the code that can help us print the PDF reports using both PL/PDF and PL_FPDF. We then move on to see the less cost intensive measures, and understand the process of coding that can help us generate the RTF, XLS, CSV, and HTML documents without any separate printing engine.
We move from no engine to explicit engines and see the process of deploying the Apache cocoon and Apache fop on Weblogic, and using them for printing reports in most known formats in APEX.
While cocoon and fop were used for the report printing, the reports were themselves in APEX. We now check out technologies, such as Eclipse BIRT and Jasper reports. We understand the process of installing these technologies, creating reports in them, and then using them in APEX through the RESTful web services.
Chapter 7, Integrating APEX with OBIEE, takes us to a whole new world. We talk about OBIEE and BI Publisher in this chapter. These tools have been the face of Oracle’s business intelligence solution and are loaded with all kinds of the reporting features. The primary objective to introduce these technologies to you and to show their integration with APEX is to help you understand the strengths of each of these technologies and to enable you to use them with APEX, whenever required. This chapter also has a reference application of its own.
We start our discussion in this chapter, by talking about the Oracle Fusion Middleware architecture since OBIEE is a part of it. We then spend some time to understand the wiring of OBIEE, its various components, and their uses. The talk includes discussion on the BI Server component and the BI Presentation Server component. After understanding the basics, we move on to see the process of creating a simple report in OBIEE. We learn about various OBIEE features, such as selection steps, filters, and hierarchies in the process.
Once there, switch from the development mode to the observation mode and have a look at some of the features that OBIEE shows in its sample repository and web catalog. We check out a typical OBIEE dashboard, understand KPI (Key Performance Indicators) and KPI watchlists. We then pay some attention to create an action in OBIEE. Actions are OBIEE’s way of scheduling and delivering reports. We understand the OBIEE Mapviewer, which helps us use the external maps provided by Google and Navteq. This enables us to overlay our BI data on top of the maps to produce the best in class location intelligence solutions. We skim over the OBIEE’s strategy management feature that helps us to link business objectives and presents them in a number of solutions, such as Strategy Tree, Strategy Wheel, Cause and Effect Map, and Strategy Map.
We end our discussion of the features of OBIEE by looking at the process of configuring an SMTP server for OBIEE and understanding the delivery of e-mails, by scheduling an OBIEE agent, and monitoring it through OBIEE Job Manager.
Once we have a good idea about the possibilities with OBIEE, we start looking at the process of integrating OBIEE with APEX. We start, by using the web service approach and have a look at iBotService of OBIEE that can help us trigger an agent in OBIEE from APEX, and hence enable us to deliver the OBIEE reports by e-mails. We also look at OBIEE’s HTMLViewService, which enables us to fetch the OBIEE reports at APEX end using AJAX.
Finally, we have a look at a less secure method to use OBIEE in APEX. We check out the process of using OBIEE’s Go URL for linking it with APEX. The credentials and all the necessary information required by OBIEE are passed from APEX in the URL in this method. iFrames in APEX can also use Go URL and we have a discussion on this part as well.
After having a good discussion about OBIEE, we have a look at another interesting technology called BI Publisher. BI Publisher is not a new name for the APEX developers since it has historically been used for the report printing with APEX.
We have a brief talk about the tool and then nosedive into the process of creation of a report in BI Publisher. The book is shipped with all kinds of code to make your life easier. We start by understanding the process of creating a data model in BI Publisher and then use the MS Word plugin of BI Publisher to create a template. We put both these pieces together to create a BI Publisher report.
Once our report is ready, we look at the process of scheduling this report, and delivering it via e-mail.
We then move into an exclusive zone in BI Publisher. We talk about the creation of the Bar code reports in BI Publisher. Note that we haven’t discussed these reports yet in any of the technologies so far, so this type of report makes BI Publisher unique. We then take our BI Publisher development skills to a whole new level, by looking at the possibilities of coding the dynamic reports in BI Publisher. This talk includes discussion on changing columns on the fly, changing groupings on the fly, changing sort order, and much more.
Once we have seen some of the good reporting features of BI Publisher, it is time for us to get into the integration. We start this discussion with the traditional method of using the convert servlet of BI Publisher for the report printing. In this method, data and the template required by BI Publisher to print the report, are stored at the APEX end and BI Publisher just serves as a printing engine.
We then move on to see the use of BI Publisher’s web service for integration with OBIEE and finally check out a less secure method of using the BI Publisher’s guest folder method for using the BI Publisher reports in APEX.
Chapter 8, All About Web Services and Integrations, is one of my favorites. Till now, we have been talking about using web services in APEX, but this teaches us about the creation of different types of web services. The spectrum includes the use of PL/SQL to Resource Templates of APEX Listener to BPEL for the creation of web services. Not only this, the chapter also talks about using the statistical tools, such as Oracle R for predictive analysis in APEX and the use of Google API to help us import data from the external servers for advanced analytics.
We start the discussion of this chapter, by talking about creating the necessary ACL and assigning the right grants to schemas. We then set the XMLDB services, which help us to project stored procedures and functions as web services. We then have a look at parsing the response of an invocation to a stored procedure using the XMLDB services. The stored function, which is invoked is such that it returns different columns of a single record. We then device a function that is capable of returning a bunch of records, we parse its response, and display the result in a report region. We also see the process of passing the APEX items as inputs to the XMLDB services. We see a report region that uses XMLTable for parsing the web service response. Until now, we had been handling the stored procedures and functions through the XMLDB web services, but now we fire queries on tables using native web service, and parse its response.
We then move on to see the process of configuring and creating the RESTful web services using Resource Templates. Resource Templates return the JSON objects as response, and we use a method to parse the response in our report regions.
Finally, we look at a process of creating a RESTful PL/SQL web service using DADs. We see the process of passing the arguments to this web service, by using the RESTful web service reference in APEX. We also have a look at the method to parse the response of this web service.
BPEL is a glue that can hold a variety of technologies together in an heterogeneous system. It also has a unique feature called Human Workflows and its associated worklist that can help us code the complex business decision processing systems and can help us transfer the job from the workstack of one user to the workstack of another. It is these wide applications of the technology that made us look into the various possibilities in BPEL and to find ways to use them in APEX. We use this part of the text to install, configure, and create both the synchronous and asynchronous processes in BPEL, which can then be used in APEX just like any other SOAP web service. We also have a detail discussion on using BPEL Human Workflows and have presented an example to use it.
Once we are through with BPEL, we have a short section dedicated to understanding the creation of the reports in the SAP Crystal reports, and using them in APEX. We also have short sections dedicated to the migration of data and code from MS Access to APEX and from Oracle Forms and Reports to APEX.
We bring our discussion of this chapter to an end, by elaborately discussing about the use of Oracle R and Google API in APEX. The talk on the Google report implements a use case of finding the best places to place the ATM machines in an area of interest and to get a PNG image that shows the plot of the points suggested by Oracle R for placing the ATM machines in the area of interest.
The discussion on Google API on the other hand is about using the Google Places API for finding the latitude/longitude information of important landmarks, such as bus stops, airports, and so on. The process described in this section fetches information from API, parses it, and presents the extracted information as an output of a SQL query. The code that is the SQL query that triggers the request to fire Google API also sends the desired landmark type (for example, bus stop and airport), and the desired city to Google API. The parsed information from the response can then be combined with our BI information to find the correlation of landmarks with the location of our retail store. This is however, a use case, and the process can be applied to get data from a number of other Google APIs. Data can be used in APEX for a variety of analysis.
Chapter 9, Performance Analysis, is all about tuning. The chapter informs you about the best development practices, and also introduces you to some of the database tools that can be used for the purpose of tuning the code.
The Tuning pointers for development section discusses v(),page and region caching, pagination scheme, tuning of like comparison, and many more.
Similarly, the talk on the database tools includes a natter about most of the features of the database that can help you hunt a bottle neck and eliminate it.
In a nut shell, the book is intended for all those who believe that making the technologies work in harmony and using their strengths to meet the objectives is a potent challenge. This book is for you, if you wish to spring into the action of the APEX development from the time you hold this book in your hand. The book is designed for innovative architects and enthusiastic developers.
Resources for Article :
- Prepare and Build [Article]
- Oracle APEX Plug-ins [Article]
- Getting Your APEX Components Logic Right [Article]