Better Logic for Better Solutions
Feed aggregator
Index Column Order – Impact On Index Branch Blocks Part II (The Weeping Song)
In Part I, I discussed how the order of columns in an index makes no real difference to the effectiveness of the index if all columns are referenced via equality predicates. If the leading column has a high number of distinct columns, it might result in less necessary data within index branches as less data […]
Announcement: New Europe Seminar in Brussels, Belgium 27-28 September 2018
Due to popular demand, I’ll be running another of my acclaimed seminars in Europe later in the year, this time in Brussels, Belgium on 27-28 September 2018. This is a must attend seminar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high […]
Automatically capture all errors and context in your APEX application
Let me start this post with a conversation between an end-user (Sarah) and a developer (Harry):
End-user: "Hey there, I'm receiving an error in the app."
Developer: "Oh, sorry to hear that. What is the message saying?"
End-user: "Unable to process row of table EBA_PROJ_STATUS_CATS. ORA-02292: integrity constraint (XXX.SYS_C0090660) violated - child record found"
Developer: "Oh, what are you trying to do?"
End-user: "I'm trying to delete a category."
Developer: "Oh, most likely this category is in use, so you can't delete the category, you first need ..."
End-user: "Ehh?!"
You might ask yourself, what is wrong with this conversation?
The first thing is that the end-user gets an error which is hard to understand. She probably got the error before but tried a few times before calling the developer (or support). Most likely Sarah has a tight deadline and these errors don't really help their mood. The other problem is that the developer was most likely just busy working on some complex logic and now gets interrupted. It takes some minutes before Harry can understand what Sarah is talking about. He needs to ask a few questions to know what Sarah is doing and doesn't have much context. He might ask to send a screenshot of the error and a few minutes later he receives this (app in APEX 5.1):
Harry is a smart cookie, so he knows in which schema to look for that constraint name, so he knows which table it's linked to. If Harry read my previous blog post on how to remotely see what Sarah was doing, he has more context too.
If the application is running in APEX 18.1, it's a different story. The screenshot will look like this:
APEX 18.1 actually enhanced the default error message. The user gets fewer details and sees a debug id. With this debug id the developer can get actually more info in Your App > Utilities > Debug Messages:
You might also want to check this blog post by Joel Kallman where to find more info when receiving an internal error with debug id.
Although APEX 18.1 captures more info, there's a more recommended way to deal with errors.
In APEX you can define an Error Handling Function which will kick in every time an error occurs. You can define this function in the Application Definition:
When you look in the Packaged applications that are shipped with Oracle Application Express (APEX), you find some examples. The above screenshot comes from P-Track.
The error handling function has this definition:
function apex_error_handling (p_error in apex_error.t_error )
return apex_error.t_error_result
The example used in P-Track gives a good overview (read the comments in the package) of the different errors you want to capture:
function apex_error_handling (
p_error in apex_error.t_error )
return apex_error.t_error_result
is
l_result apex_error.t_error_result;
l_constraint_name varchar2(255);
begin
l_result := apex_error.init_error_result (
p_error => p_error );
-- If it is an internal error raised by APEX, like an invalid statement or
-- code which can not be executed, the error text might contain security sensitive
-- information. To avoid this security problem we can rewrite the error to
-- a generic error message and log the original error message for further
-- investigation by the help desk.
if p_error.is_internal_error then
-- mask all errors that are not common runtime errors (Access Denied
-- errors raised by application / page authorization and all errors
-- regarding session and session state)
if not p_error.is_common_runtime_error then
add_error_log( p_error );
-- Change the message to the generic error message which doesn't expose
-- any sensitive information.
l_result.message := 'An unexpected internal application error has occurred.';
l_result.additional_info := null;
end if;
else
-- Always show the error as inline error
-- Note: If you have created manual tabular forms (using the package
-- apex_item/htmldb_item in the SQL statement) you should still
-- use "On error page" on that pages to avoid loosing entered data
l_result.display_location := case
when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
else l_result.display_location
end;
-- If it's a constraint violation like
--
-- -) ORA-00001: unique constraint violated
-- -) ORA-02091: transaction rolled back (can hide a deferred constraint)
-- -) ORA-02290: check constraint violated
-- -) ORA-02291: integrity constraint violated - parent key not found
-- -) ORA-02292: integrity constraint violated - child record found
--
-- we try to get a friendly error message from our constraint lookup configuration.
-- If we don't find the constraint in our lookup table we fallback to
-- the original ORA error message.
if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
l_constraint_name := apex_error.extract_constraint_name (
p_error => p_error );
begin
select message
into l_result.message
from eba_proj_error_lookup
where constraint_name = l_constraint_name;
exception when no_data_found then null; -- not every constraint has to be in our lookup table
end;
end if;
-- If an ORA error has been raised, for example a raise_application_error(-20xxx)
-- in a table trigger or in a PL/SQL package called by a process and we
-- haven't found the error in our lookup table, then we just want to see
-- the actual error text and not the full error stack
if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
l_result.message := apex_error.get_first_ora_error_text (
p_error => p_error );
end if;
-- If no associated page item/tabular form column has been set, we can use
-- apex_error.auto_set_associated_item to automatically guess the affected
-- error field by examine the ORA error for constraint names or column names.
if l_result.page_item_name is null and l_result.column_alias is null then
apex_error.auto_set_associated_item (
p_error => p_error,
p_error_result => l_result );
end if;
end if;
return l_result;
end apex_error_handling;
When defining this error handling function the error the user gets is more like a notification message and embedded in your app. You can also define a custom message, in the above package there's a lookup in an error_lookup table, but as it can't find the constraint name, it falls back to the normal message.
The real power comes when you start to combine the error handling function with a call to also log session state information. Then you know exactly which record this error was produced for.
There are a couple of ways to include the session state:
Team Development
I typically include a feedback page in my apps. When the user logs feedback by clicking on the feedback link, this is saved in Team Development. The really cool thing is that whenever feedback is logged, automatically the session state of items and some other info like the browser that was being used at the moment of the logging is included. But you can also log feedback through an APEX API:
apex_util.submit_feedback (
p_comment => 'Unexpected Error',
p_type => 3,
p_application_id => v('APP_ID'),
p_page_id => v('APP_PAGE_ID'),
p_email => v('APP_USER'),
p_label_01 => 'Session',
p_attribute_01 => v('APP_SESSION'),
p_label_02 => 'Language',
p_attribute_02 => v('AI_LANGUAGE'),
p_label_03 => 'Error orq_sqlcode',
p_attribute_03 => p_error.ora_sqlcode,
p_label_04 => 'Error message',
p_attribute_04 => p_error.message,
p_label_05 => 'UI Error message',
p_attribute_05 => l_result.message
);
Logger
Logger is a PL/SQL logging and debugging framework. If you don't know it yet, you should definitely check it out. In my opinion, Logger is the best way to instrument your PL/SQL code. Logger has many cool features, one of them is the ability to log your APEX items:
logger.log_apex_items('Debug Items from Error log');
With the above methods, you know which record the end-user was looking at and what the context was. Note that you might find this information too if you look at their session, but it would take more time to figure things out.
Be pro-active
Now, to prevent the conversation from happening again, you can take it one step further and start logging and monitoring those errors. Whenever errors happen you can, for example, log it in your own error table, or in your support ticket system and send yourself an email or notification.
Then instead of the end-user calling you, you call them and say "Hey, I saw you had some issues...".
By monitoring errors in your application, you can pro-actively take actions :)
Note that APEX itself also stores Application Errors. You find under Monitor Activity > Application Errors:
The report gives the error and the session, so you look further into what happened:
So, even when you didn't have an error handling function in place, you can still start monitoring errors that happen in your app. I know the readers of this blog are really smart so you might not see any errors, but still, it might be worthwhile to check it once and a while :)
You find another example of the error handling function in my Git account. I included an example of logging in your own error table and sending an email.
.gist .blob-wrapper.data { max-height:200px; overflow:auto; }
End-user: "Hey there, I'm receiving an error in the app."
Developer: "Oh, sorry to hear that. What is the message saying?"
End-user: "Unable to process row of table EBA_PROJ_STATUS_CATS. ORA-02292: integrity constraint (XXX.SYS_C0090660) violated - child record found"
Developer: "Oh, what are you trying to do?"
End-user: "I'm trying to delete a category."
Developer: "Oh, most likely this category is in use, so you can't delete the category, you first need ..."
End-user: "Ehh?!"
You might ask yourself, what is wrong with this conversation?
The first thing is that the end-user gets an error which is hard to understand. She probably got the error before but tried a few times before calling the developer (or support). Most likely Sarah has a tight deadline and these errors don't really help their mood. The other problem is that the developer was most likely just busy working on some complex logic and now gets interrupted. It takes some minutes before Harry can understand what Sarah is talking about. He needs to ask a few questions to know what Sarah is doing and doesn't have much context. He might ask to send a screenshot of the error and a few minutes later he receives this (app in APEX 5.1):
Harry is a smart cookie, so he knows in which schema to look for that constraint name, so he knows which table it's linked to. If Harry read my previous blog post on how to remotely see what Sarah was doing, he has more context too.
If the application is running in APEX 18.1, it's a different story. The screenshot will look like this:
APEX 18.1 actually enhanced the default error message. The user gets fewer details and sees a debug id. With this debug id the developer can get actually more info in Your App > Utilities > Debug Messages:
You might also want to check this blog post by Joel Kallman where to find more info when receiving an internal error with debug id.
Although APEX 18.1 captures more info, there's a more recommended way to deal with errors.
In APEX you can define an Error Handling Function which will kick in every time an error occurs. You can define this function in the Application Definition:
When you look in the Packaged applications that are shipped with Oracle Application Express (APEX), you find some examples. The above screenshot comes from P-Track.
The error handling function has this definition:
function apex_error_handling (p_error in apex_error.t_error )
return apex_error.t_error_result
The example used in P-Track gives a good overview (read the comments in the package) of the different errors you want to capture:
function apex_error_handling (
p_error in apex_error.t_error )
return apex_error.t_error_result
is
l_result apex_error.t_error_result;
l_constraint_name varchar2(255);
begin
l_result := apex_error.init_error_result (
p_error => p_error );
-- If it is an internal error raised by APEX, like an invalid statement or
-- code which can not be executed, the error text might contain security sensitive
-- information. To avoid this security problem we can rewrite the error to
-- a generic error message and log the original error message for further
-- investigation by the help desk.
if p_error.is_internal_error then
-- mask all errors that are not common runtime errors (Access Denied
-- errors raised by application / page authorization and all errors
-- regarding session and session state)
if not p_error.is_common_runtime_error then
add_error_log( p_error );
-- Change the message to the generic error message which doesn't expose
-- any sensitive information.
l_result.message := 'An unexpected internal application error has occurred.';
l_result.additional_info := null;
end if;
else
-- Always show the error as inline error
-- Note: If you have created manual tabular forms (using the package
-- apex_item/htmldb_item in the SQL statement) you should still
-- use "On error page" on that pages to avoid loosing entered data
l_result.display_location := case
when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
else l_result.display_location
end;
-- If it's a constraint violation like
--
-- -) ORA-00001: unique constraint violated
-- -) ORA-02091: transaction rolled back (can hide a deferred constraint)
-- -) ORA-02290: check constraint violated
-- -) ORA-02291: integrity constraint violated - parent key not found
-- -) ORA-02292: integrity constraint violated - child record found
--
-- we try to get a friendly error message from our constraint lookup configuration.
-- If we don't find the constraint in our lookup table we fallback to
-- the original ORA error message.
if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
l_constraint_name := apex_error.extract_constraint_name (
p_error => p_error );
begin
select message
into l_result.message
from eba_proj_error_lookup
where constraint_name = l_constraint_name;
exception when no_data_found then null; -- not every constraint has to be in our lookup table
end;
end if;
-- If an ORA error has been raised, for example a raise_application_error(-20xxx)
-- in a table trigger or in a PL/SQL package called by a process and we
-- haven't found the error in our lookup table, then we just want to see
-- the actual error text and not the full error stack
if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
l_result.message := apex_error.get_first_ora_error_text (
p_error => p_error );
end if;
-- If no associated page item/tabular form column has been set, we can use
-- apex_error.auto_set_associated_item to automatically guess the affected
-- error field by examine the ORA error for constraint names or column names.
if l_result.page_item_name is null and l_result.column_alias is null then
apex_error.auto_set_associated_item (
p_error => p_error,
p_error_result => l_result );
end if;
end if;
return l_result;
end apex_error_handling;
When defining this error handling function the error the user gets is more like a notification message and embedded in your app. You can also define a custom message, in the above package there's a lookup in an error_lookup table, but as it can't find the constraint name, it falls back to the normal message.
The real power comes when you start to combine the error handling function with a call to also log session state information. Then you know exactly which record this error was produced for.
There are a couple of ways to include the session state:
Team Development
I typically include a feedback page in my apps. When the user logs feedback by clicking on the feedback link, this is saved in Team Development. The really cool thing is that whenever feedback is logged, automatically the session state of items and some other info like the browser that was being used at the moment of the logging is included. But you can also log feedback through an APEX API:
apex_util.submit_feedback (
p_comment => 'Unexpected Error',
p_type => 3,
p_application_id => v('APP_ID'),
p_page_id => v('APP_PAGE_ID'),
p_email => v('APP_USER'),
p_label_01 => 'Session',
p_attribute_01 => v('APP_SESSION'),
p_label_02 => 'Language',
p_attribute_02 => v('AI_LANGUAGE'),
p_label_03 => 'Error orq_sqlcode',
p_attribute_03 => p_error.ora_sqlcode,
p_label_04 => 'Error message',
p_attribute_04 => p_error.message,
p_label_05 => 'UI Error message',
p_attribute_05 => l_result.message
);
Logger
Logger is a PL/SQL logging and debugging framework. If you don't know it yet, you should definitely check it out. In my opinion, Logger is the best way to instrument your PL/SQL code. Logger has many cool features, one of them is the ability to log your APEX items:
logger.log_apex_items('Debug Items from Error log');
With the above methods, you know which record the end-user was looking at and what the context was. Note that you might find this information too if you look at their session, but it would take more time to figure things out.
Be pro-active
Now, to prevent the conversation from happening again, you can take it one step further and start logging and monitoring those errors. Whenever errors happen you can, for example, log it in your own error table, or in your support ticket system and send yourself an email or notification.
Then instead of the end-user calling you, you call them and say "Hey, I saw you had some issues...".
By monitoring errors in your application, you can pro-actively take actions :)
Note that APEX itself also stores Application Errors. You find under Monitor Activity > Application Errors:
The report gives the error and the session, so you look further into what happened:
So, even when you didn't have an error handling function in place, you can still start monitoring errors that happen in your app. I know the readers of this blog are really smart so you might not see any errors, but still, it might be worthwhile to check it once and a while :)
You find another example of the error handling function in my Git account. I included an example of logging in your own error table and sending an email.
.gist .blob-wrapper.data { max-height:200px; overflow:auto; }
How to Export to Excel and Print to PDF in Oracle APEX? The answer...
Two questions that pop-up a lot when I'm at a conference or when doing consulting are:
Although the solution to both questions is the same, I'll go into more detail on the specific questions separately.
How can I export my data from APEX to Excel?
People typically want to export data from a Classic Report, Interactive Report, Interactive Grid or a combination of those to Excel.
What APEX provides out-of-the-box is the export to CSV format, which can be opened in Excel.
The biggest issue with CSV is that it's not native Excel format. Depending on the settings of Excel (or better your OS globalization settings) the CSV will open incorrectly. Instead of different columns, you see one big line. You also get an annoying message that some functions will be lost as it's not a native Excel format.
You can customize the CSV separator, so the columns are recognized. But with a global application (users with different settings), it's still a pain. Maybe the biggest issue people have with CSV export is that it's just plain text, so the markup or customizations (sum, group by, ...) are lost.
You can enable the CSV export in the attributes section of the respective components:
When you have BI Publisher (BIP) setup and in APEX specified as Print Server, you have a few more options. In the Classic Report, you find it in the Printing section - there's an option for Excel. In the Interactive Report, there's an option for XLS, the Interactive Grid doesn't have an option.
BI Publisher is expensive and comes with a big infrastructure and maintenance overhead, so this is not an option for many APEX people. But even the companies who have it, are looking at other solutions because although you get a native Excel file, it's cumbersome to use and BIP doesn't export your Interactive Report exactly as you see it on the screen with the customizations you did.
So how to get around those issues then? There are some APEX plugins to export an Interactive Report and Grid as you see it on the screen. The plugin of Pavel is probably the most popular one.
If you need to export one IR/IG at a time to Excel in a pre-defined Excel file, this might be an option for you. If you want to use your own Excel template, the ability to export multiple IR/IG at the same time or want more flexibility all around, you want to read on...
The solution
APEX Office Print (AOP). The AOP plugin extends APEX so you can specify the Excel file you want to start from, your template, in combination with the different APEX reports (Classic Report, Interactive Report, Interactive Grid) and get the output in Excel (or other formats). AOP is really easy to use, yet flexible and full of features no other solution provides. I'll touch on three different aspects customers love.
Interactive Report/Grid to Excel with AOP - WYSIWYG (!)
This feature is what customers love about AOP and something you won't find anywhere else. You can print one or more Interactive Reports and Grids directly to Excel, exactly as you see it on the screen. So if the end-user made a break, added some highlights or did some computations, it's all known by AOP. Even the Group by and Pivot are no problem. The implementation is super simple; in Excel, you can define your template; a title, a logo etc. Where you want to see the Interactive Report or Grid you specify {&interactive_1}, {&interactive_2} and for the Interactive Grid you specify {&static_id&}. In the AOP APEX plugin, you specify the template, and the static ids of the Interactive Report / Grid regions and that is it! AOP is doing the merge... if in the template the special tags are seen, AOP will generate the IR/IG. Not a screenshot - REAL table data! Here's an example with one Interactive Report:
In your Excel you can add multiple tags, on the same sheet and on different sheets... and this doesn't only work in Excel, but also in Word and PDF!
But there is even more... what if you look at the Interactive Report as a chart?
You got it... AOP even understands this. You can plot the table data with {&interactive} and by using {$interactive} it will generate the chart ... and that is a native Office chart, you can still change it in Excel!
Here's an example of the output generated by AOP with three interactive reports, one as a chart:
All the above goodies you can do through the AOP PL/SQL API too. Some people use this to schedule their reports and email them out on a daily basis, so they don't even have to go into APEX.
For me, the Interactive Report and Grid feature are one of the killer features of AOP.
Advanced templates in Excel with AOP
AOP is really flexible in how you build your template. The templating engine supports hierarchical data, angular expressions, conditions, blocks of data so you can view data next to each other and it supports HTML expressions too.
Here's an example of a template which loops over the orders and shows the product of that order. It contains a condition to show an "X" when the quantity is higher than 2 and it also has an expression to calculate the price of the line (unit price * quantity).
The data source specified in the plugin is of type SQL. AOP supports the cursor technique in SQL to create hierarchical data:
And (a part of) the output looks like this:
I'm amazed by what people come up with in their templates to create really advanced Excel sheets. It's really up to your imagination... and a combination of the features of Excel.
Multiple sheets in one Excel file with AOP
We have one customer who basically dumps their entire database in Excel. Every table has its own sheet in Excel. You just need to put the right tags in the different sheets and you are done.
AOP also supports the dynamic generation of sheets in Excel, so you get for example one sheet per customer and on that sheet the orders of that customer. The template looks like this (the magic tag is {!customers}):
The output is this:
We built this feature a while back based on some customers feedback.
Dynamic column generation in Excel with AOP
This is a new feature we have been working for in AOP 4.0. By using the {:tag} we can generate columns dynamically now too:
This might be useful if you want to pivot the data or want to see it in a different format. This feature is also available for Word tables. Another way of pivoting is doing it in Oracle or in an Interactive Report. This feature took us a long time to develop, but we think it's worth it.
I hope by the above I demonstrated why I believe APEX Office Print (AOP) is "THE" solution if you want to export your data from APEX (or the Oracle Database) into Excel.
Let's move on to the second question...
How can I print to PDF? Or how can I get a document/report with my data?
Oracle Application Express (APEX) has two integrated ways to print to PDF: either you use XSL-FO or you use BI Publisher. But the reason people still ask the question of how to print to PDF is that the one is too hard to implement (XSL-FO) and the other (BI Publisher) is too expensive, too hard to maintain and not user-friendly enough.
Again APEX Office Print (AOP) is the way to go. AOP is so easy to use, so well integrated with APEX, that most developers love to work with it. Based on a template you create in Word, Excel, Powerpoint, HTML or Text you can output to PDF. In combination with the AOP plugin or PL/SQL API, it's easy to define where your data and template is, and AOP does the merge for you.
Building the template
It begins the same as with any print engine... You don't want to learn a new tool to build your template in. You want to have a fast result. So the way you get there with AOP is, use the AOP plugin, define your data source and let AOP generate the template for you. AOP will look at your data and create a starter template for you (in Word, Excel, HTML or Text) with the tags you can use based on your data and some explanation how to use the tags.
Here's an example where AOP generates a Word template based on the SQL Query specified in the Data Source:
So now you have a template you can start from. Next, you customize the template to your needs... or you can even let the business user customize the template. The only thing to know is how to use the specific {tags}. As a developer, I always thought my time would be better spent than changing the logo on a template or changing some sentences over and over again. With AOP my dream comes true; as a developer, I can concentrate on my query (data), the business user can create the template themselves and send the new version or upload it straight into the app whenever changes are required.
When customers show me what they did with AOP; from creating templates for invoices, bills of materials, certificates to full-blown books, I'm really impressed by their creativity. If you imagine it, you can probably do it :)
Here's the AOP plugin, where we specify where the customized Word template can be found (in Static Application Files) and set the output to PDF:
Features in AOP that people love
When you download APEX Office Print, it comes with a Sample app, which shows the features of AOP in action. Here's a screenshot of some of the Examples you find in the AOP Sample App:
As this blog post is getting long, I won't highlight all the features of AOP and why they rock so much, but I do want to take two features you probably won't find anywhere else.
Native Office Charts and JET Charts in PDF
AOP supports the creation of native Office Charts, so you can even customize the charts further in Word. But sometimes people want to see exactly the chart they have on the screen, it is a JET chart, a Fusion chart, Highchart or any other library... With AOP you can get those charts straight into your PDF! The only thing you have to do is specifying the static id of the region and in your template, you put {%region} ... AOP will screenshot what the user sees and replace the tag with a sharp image. So even when the customer removed a series from the legend, it's exactly like that in the PDF.
HTML content in PDF
At the APEX World conference, a customer showed their use case of APEX together with AOP. Before they had to manage different Word documents and PDFs, but it was so hard as they had to update different documents every time again, it got out of sync and it was just a pain overall to deal with. So they replaced all this by Oracle APEX and Rich Text Editors. They created a structured database, so the information was in there once, but by using APEX Office Print (AOP) they generate all the different documents (Word/PDF) they need.
AOP will interpret the HTML when it sees an underscore in the tag e.g. {_tag}, then it will translate that HTML into native Word styling. If a PDF is requested, the Word is converted to PDF, so the PDF contains real bold text, or real colors etc.
Here's an example of how Rich Text is rendered to PDF.
AOP also understands when you use for example HTML expressions in your Classic or Interactive Report, or you do some inline styling. It took us a very long time to develop this feature, but the feedback we get from our customer base made it worthwhile :)
So far I showed Word as starting template for your PDF, but sometimes Powerpoint is a great start too, and not many people know about that. In Powerpoint you can make pixel perfect templates too and go to PDF is as easy as coming from Word.
In our upcoming release of AOP 4.0, we spend a lot of time improving our PDF feature set. We will introduce PDF split and merge and the ability to prepend and append files to any of your documents.
Some last words
If you are interested in what APEX Office Print (AOP) is all about, I recommend to sit down and watch this 45 minutes video I did at the APEX Connect conference. In that presentation, I go from downloading, installing to using and show many features of AOP live.
We at APEX R&D are committed to bringing the best possible print engine to APEX, which makes your life easier. We find it important to listen to you and support you however we can. We really want you to be successful. So if you have feedback for us in ways we can help you, even more, let us know, we care about you. We won't rest before we let everybody know about our mission and want to stay "the" printing solution for APEX.
Sometimes I get emails from developers who tell me they have to do a comparison between the print engines for Oracle APEX, but they love AOP. If you include some of the above features (IR/IG to PDF or Excel, JET Charts, and HTML to PDF) in your requirements, you are guaranteed to work with APEX Office Print, there's nothing else that comes even close to those features :)
AOP's philosophy has been to be as integrated as possible in APEX, as easy as building APEX applications, yet flexible enough to build really advanced reports. We make printing and exporting of data in APEX easy.
If you read until here, you are amazing, now I rest my case :)
- How can I export my data from APEX to Excel?
- How can I print to PDF? Or how can I get a document/report with my data?
Although the solution to both questions is the same, I'll go into more detail on the specific questions separately.
How can I export my data from APEX to Excel?
People typically want to export data from a Classic Report, Interactive Report, Interactive Grid or a combination of those to Excel.
What APEX provides out-of-the-box is the export to CSV format, which can be opened in Excel.
The biggest issue with CSV is that it's not native Excel format. Depending on the settings of Excel (or better your OS globalization settings) the CSV will open incorrectly. Instead of different columns, you see one big line. You also get an annoying message that some functions will be lost as it's not a native Excel format.
You can customize the CSV separator, so the columns are recognized. But with a global application (users with different settings), it's still a pain. Maybe the biggest issue people have with CSV export is that it's just plain text, so the markup or customizations (sum, group by, ...) are lost.
You can enable the CSV export in the attributes section of the respective components:
When you have BI Publisher (BIP) setup and in APEX specified as Print Server, you have a few more options. In the Classic Report, you find it in the Printing section - there's an option for Excel. In the Interactive Report, there's an option for XLS, the Interactive Grid doesn't have an option.
BI Publisher is expensive and comes with a big infrastructure and maintenance overhead, so this is not an option for many APEX people. But even the companies who have it, are looking at other solutions because although you get a native Excel file, it's cumbersome to use and BIP doesn't export your Interactive Report exactly as you see it on the screen with the customizations you did.
So how to get around those issues then? There are some APEX plugins to export an Interactive Report and Grid as you see it on the screen. The plugin of Pavel is probably the most popular one.
If you need to export one IR/IG at a time to Excel in a pre-defined Excel file, this might be an option for you. If you want to use your own Excel template, the ability to export multiple IR/IG at the same time or want more flexibility all around, you want to read on...
The solution
APEX Office Print (AOP). The AOP plugin extends APEX so you can specify the Excel file you want to start from, your template, in combination with the different APEX reports (Classic Report, Interactive Report, Interactive Grid) and get the output in Excel (or other formats). AOP is really easy to use, yet flexible and full of features no other solution provides. I'll touch on three different aspects customers love.
Interactive Report/Grid to Excel with AOP - WYSIWYG (!)
This feature is what customers love about AOP and something you won't find anywhere else. You can print one or more Interactive Reports and Grids directly to Excel, exactly as you see it on the screen. So if the end-user made a break, added some highlights or did some computations, it's all known by AOP. Even the Group by and Pivot are no problem. The implementation is super simple; in Excel, you can define your template; a title, a logo etc. Where you want to see the Interactive Report or Grid you specify {&interactive_1}, {&interactive_2} and for the Interactive Grid you specify {&static_id&}. In the AOP APEX plugin, you specify the template, and the static ids of the Interactive Report / Grid regions and that is it! AOP is doing the merge... if in the template the special tags are seen, AOP will generate the IR/IG. Not a screenshot - REAL table data! Here's an example with one Interactive Report:
In your Excel you can add multiple tags, on the same sheet and on different sheets... and this doesn't only work in Excel, but also in Word and PDF!
But there is even more... what if you look at the Interactive Report as a chart?
You got it... AOP even understands this. You can plot the table data with {&interactive} and by using {$interactive} it will generate the chart ... and that is a native Office chart, you can still change it in Excel!
Here's an example of the output generated by AOP with three interactive reports, one as a chart:
All the above goodies you can do through the AOP PL/SQL API too. Some people use this to schedule their reports and email them out on a daily basis, so they don't even have to go into APEX.
For me, the Interactive Report and Grid feature are one of the killer features of AOP.
Advanced templates in Excel with AOP
AOP is really flexible in how you build your template. The templating engine supports hierarchical data, angular expressions, conditions, blocks of data so you can view data next to each other and it supports HTML expressions too.
Here's an example of a template which loops over the orders and shows the product of that order. It contains a condition to show an "X" when the quantity is higher than 2 and it also has an expression to calculate the price of the line (unit price * quantity).
The data source specified in the plugin is of type SQL. AOP supports the cursor technique in SQL to create hierarchical data:
And (a part of) the output looks like this:
I'm amazed by what people come up with in their templates to create really advanced Excel sheets. It's really up to your imagination... and a combination of the features of Excel.
Multiple sheets in one Excel file with AOP
We have one customer who basically dumps their entire database in Excel. Every table has its own sheet in Excel. You just need to put the right tags in the different sheets and you are done.
AOP also supports the dynamic generation of sheets in Excel, so you get for example one sheet per customer and on that sheet the orders of that customer. The template looks like this (the magic tag is {!customers}):
The output is this:
We built this feature a while back based on some customers feedback.
Dynamic column generation in Excel with AOP
This is a new feature we have been working for in AOP 4.0. By using the {:tag} we can generate columns dynamically now too:
This might be useful if you want to pivot the data or want to see it in a different format. This feature is also available for Word tables. Another way of pivoting is doing it in Oracle or in an Interactive Report. This feature took us a long time to develop, but we think it's worth it.
I hope by the above I demonstrated why I believe APEX Office Print (AOP) is "THE" solution if you want to export your data from APEX (or the Oracle Database) into Excel.
Let's move on to the second question...
How can I print to PDF? Or how can I get a document/report with my data?
Oracle Application Express (APEX) has two integrated ways to print to PDF: either you use XSL-FO or you use BI Publisher. But the reason people still ask the question of how to print to PDF is that the one is too hard to implement (XSL-FO) and the other (BI Publisher) is too expensive, too hard to maintain and not user-friendly enough.
Again APEX Office Print (AOP) is the way to go. AOP is so easy to use, so well integrated with APEX, that most developers love to work with it. Based on a template you create in Word, Excel, Powerpoint, HTML or Text you can output to PDF. In combination with the AOP plugin or PL/SQL API, it's easy to define where your data and template is, and AOP does the merge for you.
Building the template
It begins the same as with any print engine... You don't want to learn a new tool to build your template in. You want to have a fast result. So the way you get there with AOP is, use the AOP plugin, define your data source and let AOP generate the template for you. AOP will look at your data and create a starter template for you (in Word, Excel, HTML or Text) with the tags you can use based on your data and some explanation how to use the tags.
Here's an example where AOP generates a Word template based on the SQL Query specified in the Data Source:
So now you have a template you can start from. Next, you customize the template to your needs... or you can even let the business user customize the template. The only thing to know is how to use the specific {tags}. As a developer, I always thought my time would be better spent than changing the logo on a template or changing some sentences over and over again. With AOP my dream comes true; as a developer, I can concentrate on my query (data), the business user can create the template themselves and send the new version or upload it straight into the app whenever changes are required.
When customers show me what they did with AOP; from creating templates for invoices, bills of materials, certificates to full-blown books, I'm really impressed by their creativity. If you imagine it, you can probably do it :)
Here's the AOP plugin, where we specify where the customized Word template can be found (in Static Application Files) and set the output to PDF:
Features in AOP that people love
When you download APEX Office Print, it comes with a Sample app, which shows the features of AOP in action. Here's a screenshot of some of the Examples you find in the AOP Sample App:
As this blog post is getting long, I won't highlight all the features of AOP and why they rock so much, but I do want to take two features you probably won't find anywhere else.
Native Office Charts and JET Charts in PDF
AOP supports the creation of native Office Charts, so you can even customize the charts further in Word. But sometimes people want to see exactly the chart they have on the screen, it is a JET chart, a Fusion chart, Highchart or any other library... With AOP you can get those charts straight into your PDF! The only thing you have to do is specifying the static id of the region and in your template, you put {%region} ... AOP will screenshot what the user sees and replace the tag with a sharp image. So even when the customer removed a series from the legend, it's exactly like that in the PDF.
HTML content in PDF
At the APEX World conference, a customer showed their use case of APEX together with AOP. Before they had to manage different Word documents and PDFs, but it was so hard as they had to update different documents every time again, it got out of sync and it was just a pain overall to deal with. So they replaced all this by Oracle APEX and Rich Text Editors. They created a structured database, so the information was in there once, but by using APEX Office Print (AOP) they generate all the different documents (Word/PDF) they need.
AOP will interpret the HTML when it sees an underscore in the tag e.g. {_tag}, then it will translate that HTML into native Word styling. If a PDF is requested, the Word is converted to PDF, so the PDF contains real bold text, or real colors etc.
Here's an example of how Rich Text is rendered to PDF.
AOP also understands when you use for example HTML expressions in your Classic or Interactive Report, or you do some inline styling. It took us a very long time to develop this feature, but the feedback we get from our customer base made it worthwhile :)
So far I showed Word as starting template for your PDF, but sometimes Powerpoint is a great start too, and not many people know about that. In Powerpoint you can make pixel perfect templates too and go to PDF is as easy as coming from Word.
In our upcoming release of AOP 4.0, we spend a lot of time improving our PDF feature set. We will introduce PDF split and merge and the ability to prepend and append files to any of your documents.
Some last words
If you are interested in what APEX Office Print (AOP) is all about, I recommend to sit down and watch this 45 minutes video I did at the APEX Connect conference. In that presentation, I go from downloading, installing to using and show many features of AOP live.
We at APEX R&D are committed to bringing the best possible print engine to APEX, which makes your life easier. We find it important to listen to you and support you however we can. We really want you to be successful. So if you have feedback for us in ways we can help you, even more, let us know, we care about you. We won't rest before we let everybody know about our mission and want to stay "the" printing solution for APEX.
Sometimes I get emails from developers who tell me they have to do a comparison between the print engines for Oracle APEX, but they love AOP. If you include some of the above features (IR/IG to PDF or Excel, JET Charts, and HTML to PDF) in your requirements, you are guaranteed to work with APEX Office Print, there's nothing else that comes even close to those features :)
AOP's philosophy has been to be as integrated as possible in APEX, as easy as building APEX applications, yet flexible enough to build really advanced reports. We make printing and exporting of data in APEX easy.
If you read until here, you are amazing, now I rest my case :)
Error!?! What's going in APEX? The easiest way to Debug and Trace an Oracle APEX session
There are some days you just can't explain the behaviour of the APEX Builder or your own APEX Application. Or you recognize this sentence of your end-user? "Hey, it doesn't work..."
In Oracle APEX 5.1 and 18.1, here's how you start to see in the land of the blinds :)
Logged in as a developer in APEX, go to Monitor Activity:
From there go to Active Sessions:
You will see all active sessions at that moment. Looking at the Session Id or Owner (User) you can identify the session easily:
Clicking on the session id shows the details: which page views have been done, which calls, the session state information and the browser they are using.
But even more interesting, you can set the Debug Level for that session :)
When the user requests a new page or action, you see a Debug ID of that request.
Clicking on the Debug ID, you see straight away all the debug info and hopefully it gives you more insight why something is not behaving as expected.
A real use case: custom APEX app
I had a real strange issue which I couldn't explain at first... an app that was running for several years suddenly didn't show info in a classic report, it got "no data found". When logging out and back in, it would show the data in the report just fine. The user said it was not consistent, sometimes it works, sometimes not... even worse, I couldn't reproduce the issue. So I told her to call me whenever it happened again.
One day she calls, so I followed the above to set debug on for her session and then I saw it... the issue was due to pagination. In a previous record she had paginated to the "second page", but for the current record there was no "second page". With the debug information I could see exactly why it was behaving like that... APEX rewrote the query rows > :first_row, which was set to 16, but for that specific record there were not more than 16 records, so it would show no data found.
Once I figured that out, I could quickly fix the issue by Resetting Pagination on opening of the page.
Debug Levels
You can set different Debug Levels. Level 9 (= APEX Trace) gives you most info whereas debug level 1, only shows the errors, but not much other info. I typically go with APEX Trace (level 9).
The different debug levels with the description:
Trace Mode
In case you want to go a step futher you can also set Trace Mode to SQL Trace.
This will do behind the scenes: alter session set events '10046 trace name context forever, level 12’;
To find out where the trace file is stored, go to SQL Workshop > SQL Scripts and run
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
It will return the path of the trace file. When looking into that directory you want to search for the filename which contains the APEX session id (2644211946422) and time you ran the trace.
In Oracle SQL Developer you can then look at those trace files a bit more easily. You can also use TKPROF or other tools.
When I really have performance issues and I need to investigate further, I like to use Method R Workbench. The Profiler interpretes the trace file(s) and gives an explanation what's going on.
And with the different tools on the left, you can drill down in the files.
I'm definitely not a specialist in reading those trace files, but the above tools really help me understanding them. When I'm really stuck I contact Cary Millsap - or I call him Mr Trace - he's the father of those tools and knows trace files inside out :)
A second use case: APEX Builder
I was testing our APEX Office Print plugin in APEX 18.1 and for some reason APEX was behaving differently than earlier versions, but I didn't understand why. I followed the above method again to turn debug and trace on for my own session - so even when you are in the APEX Builder you can see what APEX is doing behind the scenes.
Debugging and Tracing made easy
I hope by this post you see the light when you are in the dark. Let the force be with you :)
In Oracle APEX 5.1 and 18.1, here's how you start to see in the land of the blinds :)
Logged in as a developer in APEX, go to Monitor Activity:
From there go to Active Sessions:
You will see all active sessions at that moment. Looking at the Session Id or Owner (User) you can identify the session easily:
Clicking on the session id shows the details: which page views have been done, which calls, the session state information and the browser they are using.
But even more interesting, you can set the Debug Level for that session :)
When the user requests a new page or action, you see a Debug ID of that request.
Clicking on the Debug ID, you see straight away all the debug info and hopefully it gives you more insight why something is not behaving as expected.
A real use case: custom APEX app
I had a real strange issue which I couldn't explain at first... an app that was running for several years suddenly didn't show info in a classic report, it got "no data found". When logging out and back in, it would show the data in the report just fine. The user said it was not consistent, sometimes it works, sometimes not... even worse, I couldn't reproduce the issue. So I told her to call me whenever it happened again.
One day she calls, so I followed the above to set debug on for her session and then I saw it... the issue was due to pagination. In a previous record she had paginated to the "second page", but for the current record there was no "second page". With the debug information I could see exactly why it was behaving like that... APEX rewrote the query rows > :first_row, which was set to 16, but for that specific record there were not more than 16 records, so it would show no data found.
Once I figured that out, I could quickly fix the issue by Resetting Pagination on opening of the page.
Debug Levels
You can set different Debug Levels. Level 9 (= APEX Trace) gives you most info whereas debug level 1, only shows the errors, but not much other info. I typically go with APEX Trace (level 9).
The different debug levels with the description:
Trace Mode
In case you want to go a step futher you can also set Trace Mode to SQL Trace.
This will do behind the scenes: alter session set events '10046 trace name context forever, level 12’;
To find out where the trace file is stored, go to SQL Workshop > SQL Scripts and run
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
It will return the path of the trace file. When looking into that directory you want to search for the filename which contains the APEX session id (2644211946422) and time you ran the trace.
In Oracle SQL Developer you can then look at those trace files a bit more easily. You can also use TKPROF or other tools.
When I really have performance issues and I need to investigate further, I like to use Method R Workbench. The Profiler interpretes the trace file(s) and gives an explanation what's going on.
And with the different tools on the left, you can drill down in the files.
I'm definitely not a specialist in reading those trace files, but the above tools really help me understanding them. When I'm really stuck I contact Cary Millsap - or I call him Mr Trace - he's the father of those tools and knows trace files inside out :)
A second use case: APEX Builder
I was testing our APEX Office Print plugin in APEX 18.1 and for some reason APEX was behaving differently than earlier versions, but I didn't understand why. I followed the above method again to turn debug and trace on for my own session - so even when you are in the APEX Builder you can see what APEX is doing behind the scenes.
Debugging and Tracing made easy
I hope by this post you see the light when you are in the dark. Let the force be with you :)
Facebook, Google and Custom Authentication in the same Oracle APEX 18.1 app
Oracle APEX 18.1 has many new features, one of them is called Social Login.
On the World Cup 2018 Challenge, you can see the implementation of this new feature. The site allows you to sign-up or login with Facebook, Google, and your own email address.
It's even nicer that if you register with your email, but later decide to sign-up with Google or Facebook, it will recognize you as the same user if the email address is the same.
To get the Social Login to work I had to do the following...
Facebook
To enable Facebook login in your own app, you first have to create an app on Facebook. Creating an application is straightforward by following the wizards, just make sure you create a website app.
Google
To enable Google login in your own app, you first have to create a project on Google. Adrian did a really nice blog post which walks you through creating your project and setup Google authentication in your APEX application.
To hook-up Google and Facebook to our own APEX app, we have to let APEX know which credentials it should use, namely the info you find in the previous screenshots.
Web Credentials
Go to App Builder > Workspace Utilities > All Workspace Utilities and click on the Web Credentials link
I added the Web Credentials for Facebook and Google. Web Credentials store the necessary info (Client ID = App ID and Client Secret = App Secret) of the OAuth2 authentication. OAuth2 is a standard these days which most sites are using to authenticate you as a user. Web Credentials are stored on Workspace Level so you can reuse those credentials in all the APEX apps in the same workspace.
Authentication Scheme
We need to create the different authentication schemes. The Custom Authentication is to authenticate with email, next we have FACEBOOK, and GOOGLE (and Application Express Authentication which is there by default, but not used in this app).
Custom Authentication Scheme
I blogged before about Create a Custom Authentication and Authorization Scheme in Oracle APEX. The package I use in that blog post is pretty similar to the one of the World Cup app. In the Authentication Scheme, you define the authentication function. I also have a post-authentication procedure that sets some application items.
Facebook Authentication Scheme
Normally the authentication scheme of Facebook would look a bit different as Oracle APEX has built-in Facebook authentication, but for that to work, you need to load the SSL certificate in the Oracle wallet. On the platform the World Cup is running, the database is 12.1 and unfortunately, there's a bug in the database with multi-site or wildcard certificates (which Facebook has). So I had to workaround the issue, but I still used a new feature of APEX 18.1, instead of Facebook Authentication I used Generic OAuth2 Provider.
This is how it looks like:
As we are using the Generic OAuth2 Provider, we have to define the different OAuth URLs manually. When you look at my URLs they look a bit strange...
To get around the SSL issue I set up a reverse proxy in Apache which handles the SSL, so anytime the database does a call to http://apexrnd.localdomain it goes through the reverse proxy.
The reverse proxy in Apache is configured like this:
Note that in Oracle DB 12.2 and above the SSL bug is not there, so you don't need to do the above. I've been using the technique many times before if I don't want to deal with the SSL certificates and configuring the Oracle wallet. Adrian did a post about APEX Social Sign-In without a wallet, which might be of interest if you are on Oracle XE for example.
So what else is happening in the authentication scheme? You have to give the scope of what you want to get back from Facebook. In our case, we use the email as username and for additional attributes, we also want to know the first name, last name and the picture. It's really important you set those additional attributes, otherwise, APEX won't pass the full JSON through and takes a shortcut as it just needs the email.
The User info Endpoint URL is special:
http://apexrnd.localdomain/graph.facebook.com/v2.10/me?fields=#USER_ATTRIBUTES#&access_token=#ACCESS_TOKEN#
Special thanks to Christian of the APEX Dev team, without his help, I wouldn't have figured that one out. Thanks again, Christian!
The next big bit is the post_authenticate procedure which contains the logic to map the Facebook user to the World Cup app user. If it finds the user, it will set some application items again, just like in the custom authentication, but if it doesn't find the user (the first time somebody connects through Facebook), it will create a World Cup user. The most important part of that logic is the part to get the name and picture. Here we parse the JSON the authentication scheme holds in memory.
apex_json.get_varchar2('first_name')
apex_json.get_varchar2('last_name')
apex_json.get_varchar2('picture.data.url')
And then the final bit you have to be careful with, that in the authentication scheme "Switch in Session" is set to "Enabled". This setting is the magic bit to have your APEX application multiple authentication schemes and be able to use one or the other.
Google Authentication Scheme
The Google authentication is simpler than the Facebook one, as we don't have to do the workaround for the certificate as Oracle understands the Google certificate. So here I use the standard APEX 18.1 feature to authenticate against Google. The username attribute is again the email, and the "additional user attribute" is "profile" as that holds the name and picture of the person.
The rest of the authentication scheme is very similar to the one of Facebook. Again don't forget to switch in session to enable.
Login buttons
To call the different authentication schemes on our login page we included different buttons:
The Login button is a normal Submit and will do the Custom Authentication as that is the default authentication (see - Current in Shared Components > Authentication Schemes).
The Facebook button has a Request defined in the link: APEX_AUTHENTICATION=FACEBOOK, this is the way that APEX let you switch authentication schemes on the fly. Very cool! :)
The Google button is similar, but then the request is APEX_AUTHENTICATION=GOOGLE
(note the name after the equal sign needs to be the same as your authentication scheme)
I hope by me showing how the Social Authentication of Oracle APEX 18.1 was implemented in the World Cup 2018 Challenge, it will help you to do the same in your own APEX application.
I really love this new feature of APEX 18.1. The implementation is very elegant, user-friendly and flexible enough to handle most of the OAuth2 authentications out there. Note that Facebook and Google upgrade their APIs to get user info, so depending on when you read this, things might have changed. Facebook is typically backward compatible for a long time, but know that the current implementation in APEX is for API v2.10 and the default Facebook authentication is v3.0. As far as I experienced, the user info didn't change between the API versions. I'll do another blog post how you can debug your authentication as it might help you get other info than the one I got for the World Cup app. Feel free to add a comment if you have any question.
On the World Cup 2018 Challenge, you can see the implementation of this new feature. The site allows you to sign-up or login with Facebook, Google, and your own email address.
It's even nicer that if you register with your email, but later decide to sign-up with Google or Facebook, it will recognize you as the same user if the email address is the same.
To get the Social Login to work I had to do the following...
To enable Facebook login in your own app, you first have to create an app on Facebook. Creating an application is straightforward by following the wizards, just make sure you create a website app.
To enable Google login in your own app, you first have to create a project on Google. Adrian did a really nice blog post which walks you through creating your project and setup Google authentication in your APEX application.
To hook-up Google and Facebook to our own APEX app, we have to let APEX know which credentials it should use, namely the info you find in the previous screenshots.
Web Credentials
Go to App Builder > Workspace Utilities > All Workspace Utilities and click on the Web Credentials link
I added the Web Credentials for Facebook and Google. Web Credentials store the necessary info (Client ID = App ID and Client Secret = App Secret) of the OAuth2 authentication. OAuth2 is a standard these days which most sites are using to authenticate you as a user. Web Credentials are stored on Workspace Level so you can reuse those credentials in all the APEX apps in the same workspace.
Authentication Scheme
We need to create the different authentication schemes. The Custom Authentication is to authenticate with email, next we have FACEBOOK, and GOOGLE (and Application Express Authentication which is there by default, but not used in this app).
Custom Authentication Scheme
I blogged before about Create a Custom Authentication and Authorization Scheme in Oracle APEX. The package I use in that blog post is pretty similar to the one of the World Cup app. In the Authentication Scheme, you define the authentication function. I also have a post-authentication procedure that sets some application items.
Facebook Authentication Scheme
Normally the authentication scheme of Facebook would look a bit different as Oracle APEX has built-in Facebook authentication, but for that to work, you need to load the SSL certificate in the Oracle wallet. On the platform the World Cup is running, the database is 12.1 and unfortunately, there's a bug in the database with multi-site or wildcard certificates (which Facebook has). So I had to workaround the issue, but I still used a new feature of APEX 18.1, instead of Facebook Authentication I used Generic OAuth2 Provider.
This is how it looks like:
As we are using the Generic OAuth2 Provider, we have to define the different OAuth URLs manually. When you look at my URLs they look a bit strange...
To get around the SSL issue I set up a reverse proxy in Apache which handles the SSL, so anytime the database does a call to http://apexrnd.localdomain it goes through the reverse proxy.
The reverse proxy in Apache is configured like this:
Note that in Oracle DB 12.2 and above the SSL bug is not there, so you don't need to do the above. I've been using the technique many times before if I don't want to deal with the SSL certificates and configuring the Oracle wallet. Adrian did a post about APEX Social Sign-In without a wallet, which might be of interest if you are on Oracle XE for example.
So what else is happening in the authentication scheme? You have to give the scope of what you want to get back from Facebook. In our case, we use the email as username and for additional attributes, we also want to know the first name, last name and the picture. It's really important you set those additional attributes, otherwise, APEX won't pass the full JSON through and takes a shortcut as it just needs the email.
The User info Endpoint URL is special:
http://apexrnd.localdomain/graph.facebook.com/v2.10/me?fields=#USER_ATTRIBUTES#&access_token=#ACCESS_TOKEN#
Special thanks to Christian of the APEX Dev team, without his help, I wouldn't have figured that one out. Thanks again, Christian!
The next big bit is the post_authenticate procedure which contains the logic to map the Facebook user to the World Cup app user. If it finds the user, it will set some application items again, just like in the custom authentication, but if it doesn't find the user (the first time somebody connects through Facebook), it will create a World Cup user. The most important part of that logic is the part to get the name and picture. Here we parse the JSON the authentication scheme holds in memory.
apex_json.get_varchar2('first_name')
apex_json.get_varchar2('last_name')
apex_json.get_varchar2('picture.data.url')
And then the final bit you have to be careful with, that in the authentication scheme "Switch in Session" is set to "Enabled". This setting is the magic bit to have your APEX application multiple authentication schemes and be able to use one or the other.
Google Authentication Scheme
The Google authentication is simpler than the Facebook one, as we don't have to do the workaround for the certificate as Oracle understands the Google certificate. So here I use the standard APEX 18.1 feature to authenticate against Google. The username attribute is again the email, and the "additional user attribute" is "profile" as that holds the name and picture of the person.
The rest of the authentication scheme is very similar to the one of Facebook. Again don't forget to switch in session to enable.
Login buttons
To call the different authentication schemes on our login page we included different buttons:
The Login button is a normal Submit and will do the Custom Authentication as that is the default authentication (see - Current in Shared Components > Authentication Schemes).
The Facebook button has a Request defined in the link: APEX_AUTHENTICATION=FACEBOOK, this is the way that APEX let you switch authentication schemes on the fly. Very cool! :)
The Google button is similar, but then the request is APEX_AUTHENTICATION=GOOGLE
(note the name after the equal sign needs to be the same as your authentication scheme)
I hope by me showing how the Social Authentication of Oracle APEX 18.1 was implemented in the World Cup 2018 Challenge, it will help you to do the same in your own APEX application.
I really love this new feature of APEX 18.1. The implementation is very elegant, user-friendly and flexible enough to handle most of the OAuth2 authentications out there. Note that Facebook and Google upgrade their APIs to get user info, so depending on when you read this, things might have changed. Facebook is typically backward compatible for a long time, but know that the current implementation in APEX is for API v2.10 and the default Facebook authentication is v3.0. As far as I experienced, the user info didn't change between the API versions. I'll do another blog post how you can debug your authentication as it might help you get other info than the one I got for the World Cup app. Feel free to add a comment if you have any question.
The World Cup 2018 Challenge is live... An app created 12 years ago to showcase the awesome Oracle APEX
Since 2006 it's a tradition... every two years we launch a site where you can bet on the games of the World Cup (or Euro Cup). This year you find the app at https://www.wc2018challenge.com
You can read more about the history and see how things look like over time, or you can look on this blog at other posts in the different years.
The initial goal of the app was to showcase what you can do with Oracle Application Express (APEX). Many companies have Excel sheets where they keep the scores of the games and keep some kind of ranking for their employees. When I saw in 2006 that Excel sheet, I thought, oh well, I can do this in APEX, and it would give us way more benefits... results straight away, no sending of Excel sheets or merging data, much more attractive design with APEX etc. and from then on this app lives its own life.
Every two years I updated the app with the latest and greatest of Oracle APEX at that time.
Today the site is built in Oracle APEX 18.1 and it showcases some of the new features.
The look and feel is completely upgraded. Instead of a custom theme, the site is now using Universal Theme. You might think, it doesn't look like a typical APEX app, but it is! Just some minimal changes in CSS and a background image makes the difference.
The other big change is the Social Authentication, which is now using the built-in capabilities of APEX 18.1 instead of a custom authentication scheme I used the previous years. You can authenticate with Google, Facebook and with your own email (custom).
Some other changes came with JET charts and some smaller enhancements that came with APEX 5.1 and 18.1.
Some people asked me how certain features were done, so I'll do some separate blog posts about how Universal Theme was adapted on the landing page and how Social Authentication was included and what issues we had along the line. If you wonder how anything else was done, I'm happy to do some more posts to explain.
Finally, I would like to thank a few people who helped to make the site ready for this year: Erik, Eduardo, Miguel, Diego, Galan, and Theo, thanks so much!
Index Column Order – Impact On Index Branch Blocks Part I (Day-In Day-Out)
I recently replied on Twitter to some comments regarding an excellent blog post by Franck Pachot – Covering indexes in Oracle, and branch size, where I disagreed somewhat with one of the conclusions stated in the post: “ensure that selective columns appear as early as possible (without compromising the index access efficiency of course) in order to […]
Safely Upgrading to Oracle APEX 18.1
Oracle Application Express (APEX) 18.1 has been out now for a couple of days.
I typically don't wait long before doing the upgrade, as with every new release you get many new features I want to use. Also if you want to stay on top of the game, you just want to move as fast as you can. I typically start testing the Early Adopter releases and then when apex.oracle.com gets updated, I do more testing, but having it on your own system with applications that are used day-in-day-out is a different level.
So I thought to share how we update our environment in a safe way.
The first thing we do is put our maintenance pages on. We use an Apache Reverse Proxy in front of Apache Tomcat with ORDS which is connected to the Database. By specifying some ErrorDocuments the maintenance pages are being used the moment there's an error.
For example, you can add this to your httpd.conf:
ErrorDocument 404 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 500 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 503 https://s3.amazonaws.com/apexRnD/website/maintenance.html
When you update APEX you don't want any incoming connections, so we stop Apache Tomcat with ORDS. At that moment the Reverse Proxy gets an error and the ErrorDocument kicks in and serves the Maintenance page. This way if people want to use the system, they know we are working on it.
We use Oracle Database 12c container database and pluggable databases. We want to run different versions of APEX next to each other because we have to test APEX Office Print against all APEX releases. Our customers use different releases of Oracle APEX too, so when we do custom development we have to stick to their version, so we really need all supported APEX versions somewhere.
Our setup was like this before the APEX 18.1 upgrade:
- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1 (main - our most used one): apex_pdb
With every new major release of APEX we clone our main PDB and give it the name of the APEX release, so we keep the APEX release we are on.
The steps to clone a pluggable database in Oracle DB 12.1 (SQL*Plus or SQLcl):
alter pluggable database apex_pdb close immediate;
alter pluggable database apex_pdb open read only;
create pluggable database APEX51_PDB from APEX_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX_PDB/','/u01/app/oracle/oradata/cdb/APEX51_PDB/') PATH_PREFIX='/u01/app/oracle/oradata/cdb/APEX51_PDB';
alter pluggable database apex51_pdb open;
alter pluggable database apex_pdb close immediate;
alter pluggable database apex_pdb open;
After the above we have a situation like this:- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1: apex51_pdb- PDB with APEX 5.1: apex_pdb - will be upgraded to APEX 18.1 (main - our most used one)
Note: if you use Transparent Data Encryption (TDE) you have to perform some additional steps.
The installation of APEX 18.1 on the database side are basically 5 steps:1) download the software from OTN2) unzip in /tmp folder and cd into the /tmp/apex directory3) run SQLcl or SQLPlus as sys as sysdba and connect to the apex_pdb containeralter session set container=APEX_PDB;4) run the apexins command@apexins SYSAUX SYSAUX TEMP /i/
In my environment the script took about 23 minutes to complete:
Note: the APEX 18.1 scripts are in 3 phases and the wizard shows information and timings for all phases and at the end also a global timing for the whole. If you want to have less downtime you can run the phases separately - see the doc Maximizing Uptime During an Application Express Upgrade.
5) run the apex_rest_config command@apex_rest_config.sql
The pluggable database is ready now and contains APEX 18.1.
During the APEX upgrade and as we already have downtime, we typically make use of that time to upgrade the other components in a typical Oracle APEX stack, namely the web server (e.g. Apache Tomcat) and ORDS (Oracle REST Data Services). Another advantage of going with a new version of your middleware is that you have your working Apache Tomcat and ORDS untouched, so in case you have to rollback there's nothing to do. Note that you can prepare most of the following commands beforehand.
Upgrading the Application (web) Server:
Download Apache Tomcat.Unzip in your folder of choice.That is basically all you have to do (on Linux) :)
Download Oracle REST Data Services (ORDS).Unzip in your folder of choice and cd into it.Run: java -jar ords.war install advancedand follow the wizard to install ORDS in APEX_PDB
* make sure you use different config dirs for ORDS in order to run multiple versions of ORDS and APEX
Once done, copy the ords.war into /apache-tomcat-version/webappsNext copy the images folder of the apex directory to /apache-tomcat-version/webapps:cp -R /tmp/apex/images /apache-tomcat-version/webapps/i
Start Apache Tomcat:
cd bin
./startup.sh
Restart your Apache Reverse Proxy (and optionally take out the ErrorDocuments)/sbin/service httpd graceful
It sometimes happens to me that APEX isn't working the first time when I run it.Then I debug the connection and check the logs of the web server.
Another thing that often helps, is running ORDS in standalone mode as it will give me clear messages. e.g.
WARNING: *** jdbc.MaxLimit in configuration |apex|| is using a value of 10, this setting may not be sized adequately for a production environment ***
WARNING: *** jdbc.InitialLimit in configuration |apex|| is using a value of 3, this setting may not be sized adequately for a production environment ***
WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named apex_al, are invalid, expired, or the account is locked
WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named apex_rt, are invalid, expired, or the account is locked
The above warning remembers me to change some parameters of ORDS. Or I could look-up my previous configuration and copy those parameters. The above warning also indicates our APEX_LISTENER user can't connect (apex_al), so we need to fix that by specifying the correct password. For example, for apex_rt I forgot which user it was, but it's easy to find by navigating to the ords config folder and view the apex_rt file. It will tell the user in the file.
Now we should have APEX 18.1 up-and-running :)
We also want to access the previous versions of APEX. So I copy the older ordsxx.war files to the new web server, but I name those ords51.war, ords50.war, so the URL I access to the different APEX versions becomes https://www.apexrnd.be/ords50/ or https://www.apexrnd.be/ords51/
https://www.apexrnd.be/ords/ is always the latest version of APEX. The images folder of the older APEX version (5.1) we map to /i51/ (instead of /i/ as that is of APEX 18.1 now). In order to have a different image folder you need to run in apex51_pdb following sql:
SQL> @\utilities\reset_image_prefix.sql
We upgraded our systems this weekend, the second day after 18.1 was released. We followed more or less the above procedure and things went fine. Make sure to test your own apps first before doing the upgrade. Most of our apps were running just fine, but for some, we had to replace some older plugins with new versions or remove the plugins and replace by built-in functionality.
Note: there are many different ways of updating your system. It comes down to see what works for you. What I share works for us, but for example, if you can't afford downtime you probably want to work with standby databases and load balancers. Or if you work with virtual machines or Docker, it might be useful to clone the machine and test things on the entire machine first.
I typically don't wait long before doing the upgrade, as with every new release you get many new features I want to use. Also if you want to stay on top of the game, you just want to move as fast as you can. I typically start testing the Early Adopter releases and then when apex.oracle.com gets updated, I do more testing, but having it on your own system with applications that are used day-in-day-out is a different level.
So I thought to share how we update our environment in a safe way.
The first thing we do is put our maintenance pages on. We use an Apache Reverse Proxy in front of Apache Tomcat with ORDS which is connected to the Database. By specifying some ErrorDocuments the maintenance pages are being used the moment there's an error.
For example, you can add this to your httpd.conf:
ErrorDocument 404 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 500 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 503 https://s3.amazonaws.com/apexRnD/website/maintenance.html
When you update APEX you don't want any incoming connections, so we stop Apache Tomcat with ORDS. At that moment the Reverse Proxy gets an error and the ErrorDocument kicks in and serves the Maintenance page. This way if people want to use the system, they know we are working on it.
We use Oracle Database 12c container database and pluggable databases. We want to run different versions of APEX next to each other because we have to test APEX Office Print against all APEX releases. Our customers use different releases of Oracle APEX too, so when we do custom development we have to stick to their version, so we really need all supported APEX versions somewhere.
Our setup was like this before the APEX 18.1 upgrade:
- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1 (main - our most used one): apex_pdb
With every new major release of APEX we clone our main PDB and give it the name of the APEX release, so we keep the APEX release we are on.
The steps to clone a pluggable database in Oracle DB 12.1 (SQL*Plus or SQLcl):
alter pluggable database apex_pdb close immediate;
alter pluggable database apex_pdb open read only;
create pluggable database APEX51_PDB from APEX_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX_PDB/','/u01/app/oracle/oradata/cdb/APEX51_PDB/') PATH_PREFIX='/u01/app/oracle/oradata/cdb/APEX51_PDB';
alter pluggable database apex51_pdb open;
alter pluggable database apex_pdb close immediate;
alter pluggable database apex_pdb open;
After the above we have a situation like this:- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1: apex51_pdb- PDB with APEX 5.1: apex_pdb - will be upgraded to APEX 18.1 (main - our most used one)
Note: if you use Transparent Data Encryption (TDE) you have to perform some additional steps.
The installation of APEX 18.1 on the database side are basically 5 steps:1) download the software from OTN2) unzip in /tmp folder and cd into the /tmp/apex directory3) run SQLcl or SQLPlus as sys as sysdba and connect to the apex_pdb containeralter session set container=APEX_PDB;4) run the apexins command@apexins SYSAUX SYSAUX TEMP /i/
In my environment the script took about 23 minutes to complete:
Note: the APEX 18.1 scripts are in 3 phases and the wizard shows information and timings for all phases and at the end also a global timing for the whole. If you want to have less downtime you can run the phases separately - see the doc Maximizing Uptime During an Application Express Upgrade.
5) run the apex_rest_config command@apex_rest_config.sql
The pluggable database is ready now and contains APEX 18.1.
During the APEX upgrade and as we already have downtime, we typically make use of that time to upgrade the other components in a typical Oracle APEX stack, namely the web server (e.g. Apache Tomcat) and ORDS (Oracle REST Data Services). Another advantage of going with a new version of your middleware is that you have your working Apache Tomcat and ORDS untouched, so in case you have to rollback there's nothing to do. Note that you can prepare most of the following commands beforehand.
Upgrading the Application (web) Server:
Download Apache Tomcat.Unzip in your folder of choice.That is basically all you have to do (on Linux) :)
Download Oracle REST Data Services (ORDS).Unzip in your folder of choice and cd into it.Run: java -jar ords.war install advancedand follow the wizard to install ORDS in APEX_PDB
* make sure you use different config dirs for ORDS in order to run multiple versions of ORDS and APEX
Once done, copy the ords.war into /apache-tomcat-version/webappsNext copy the images folder of the apex directory to /apache-tomcat-version/webapps:cp -R /tmp/apex/images /apache-tomcat-version/webapps/i
Start Apache Tomcat:
cd bin
./startup.sh
Restart your Apache Reverse Proxy (and optionally take out the ErrorDocuments)/sbin/service httpd graceful
It sometimes happens to me that APEX isn't working the first time when I run it.Then I debug the connection and check the logs of the web server.
Another thing that often helps, is running ORDS in standalone mode as it will give me clear messages. e.g.
WARNING: *** jdbc.MaxLimit in configuration |apex|| is using a value of 10, this setting may not be sized adequately for a production environment ***
WARNING: *** jdbc.InitialLimit in configuration |apex|| is using a value of 3, this setting may not be sized adequately for a production environment ***
WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named apex_al, are invalid, expired, or the account is locked
WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named apex_rt, are invalid, expired, or the account is locked
The above warning remembers me to change some parameters of ORDS. Or I could look-up my previous configuration and copy those parameters. The above warning also indicates our APEX_LISTENER user can't connect (apex_al), so we need to fix that by specifying the correct password. For example, for apex_rt I forgot which user it was, but it's easy to find by navigating to the ords config folder and view the apex_rt file. It will tell the user in the file.
Now we should have APEX 18.1 up-and-running :)
We also want to access the previous versions of APEX. So I copy the older ordsxx.war files to the new web server, but I name those ords51.war, ords50.war, so the URL I access to the different APEX versions becomes https://www.apexrnd.be/ords50/ or https://www.apexrnd.be/ords51/
https://www.apexrnd.be/ords/ is always the latest version of APEX. The images folder of the older APEX version (5.1) we map to /i51/ (instead of /i/ as that is of APEX 18.1 now). In order to have a different image folder you need to run in apex51_pdb following sql:
SQL> @\utilities\reset_image_prefix.sql
We upgraded our systems this weekend, the second day after 18.1 was released. We followed more or less the above procedure and things went fine. Make sure to test your own apps first before doing the upgrade. Most of our apps were running just fine, but for some, we had to replace some older plugins with new versions or remove the plugins and replace by built-in functionality.
Note: there are many different ways of updating your system. It comes down to see what works for you. What I share works for us, but for example, if you can't afford downtime you probably want to work with standby databases and load balancers. Or if you work with virtual machines or Docker, it might be useful to clone the machine and test things on the entire machine first.
Announcing July/August Australian Dates: “Oracle Indexing Internals and Best Practices” Seminar
I’m very excited to announce new Australian dates for my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar. This is a must attend seminar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, […]
18c Scalable Sequences Part III (Too Much Rope)
I previously looked in Part I and Part II how Scalable Sequences officially released in 18c can reduce index contention issues, by automatically assigning a 6 digit prefix to the sequence value based on the instance ID and session ID of the session. We need to be careful and consider this 6 digit prefix if […]
ODTUG Kscope18 Conference: Change of Presentation Times
For those of you that were planning to attend my presentations at the upcoming ODTUG Kscope18 Conference in Orlando (yes, I’m specifically talking to the two of you), both presentations have been allotted new times and locations. They are now confirmed as: New Indexing Features Introduced in Oracle 12c Release 2 (and 18c): When: June […]
18c Scalable Sequences Part II (Watch That Man)
In Scalable Sequences Part I, I introduced this new 18c feature (although it was hidden and undocumented in previous releases). By adding a 6 digit prefix value that constitutes the first 3 digits for the Instance Id and the next 3 digits for the Session Id, it results in a sequence value that doesn’t always […]
“Let’s Talk Database” is Back !! Canberra/Sydney/Melbourne
Due to popular demand, I’ve been asked by Oracle to again run some “Let’s Talk Database” events this month. Dates and venues are as follows: Wednesday, 23 May – Canberra (Cliftons Canberra, 10 Moore St): Registration Link. Tuesday, 29 May – Melbourne (Oracle Melbourne Office, 417 St Kilda Road): Registration Link. Wednesday, 30 May – Sydney […]
18c Scalable Sequences Part I (Saviour Machine)
One of the problems with populating the value of a Primary Key (or some such), is that they can cause contention, especially in relation to indexes. By having an indexed value that monotonically increases, the right-hand most index leaf block is continually being accessed, which results in a “hot block”, which in turn results in […]
Europe June 2018 Seminars: Confirmed Venues for Oslo, Munich and Zürich
I’m very excited to confirm all venues for the European June 2018 dates of my popular “Oracle Indexing Internals and Best Practices” seminar. This is a must attend seminar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s […]
Answer: Anything Wrong With Query Performance? (Red Right Hand)
I of course attract a highly clever readership :). As some have commented, for a single table to require 1000+ consistent gets to retrieve 1000 rows implies that each row needs to be accessed from a different block. This in turn implies the Clustering Factor for this index to be relatively bad and the associated […]
Question: Anything Wrong With Query Performance? (Straight To You)
I have a query that runs pretty darn efficiently, here’s the setup: So the query basically returns 1000 rows based on the CODE column and it does so using an index on CODE. The CBO has got the costings for this just about spot on. For 1000 rows returned, it does so with just 1006 […]
BIG NEWS: I’ve Been Selected On “Journey To Mars” Program !!
HAPPY APRIL FOOLS !! After many months of nervous waiting, knowing that my chances were extremely slim at best, I have just received my confirmation letter from NASA saying that I’ve been one of the lucky ones selected to participate in the “Journey To Mars” program !! I’m soooo damn excited !!! Planning is now […]
Richard Foote Consulting Seminars At Your Workplace !!
If you have 6 or more people in you organisation who would be interested in attending a seminar, please contact me at richard@richardfooteconsulting.com. I currently run my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar as public events, which has now been run in some 18 countries. I’m also at work on developing an […]
