Development

Working with REST POST and Other Operations in Visual Builder

Shay Shmeltzer - Fri, 2018-10-05 12:37

One of the strong features of Visual Builder Cloud Service is the ability to consume any REST service very easily. I have a video that shows you how to work with REST services in a completely declarative way, but that video doesn't show you what happens behind the scenes when you work with the quick starts. In addition, that video shows using the GET methods and several threads on our community's discussion forum asked for help working with other operations of REST.

The demo video aims to give you a better insight into working with REST operations showing how to:

  • Add service endpoints for various REST operations
  • Create a GET form manually for retrieving single records
  • Create a POST form manually
    • Create type for the request and response parameters
    • Create variables based on the types
    • Call the POST operation passing a variable as body
  • Get the returned values from the POST to show in a page or notifications

A couple of notes:

In the video I use the free REST testing platform at https://jsonplaceholder.typicode.com

While I do everything here manually - you should be able to use the quick starts for creating a "create" form and map them to the post operation - as long as you marked the specific entry as a "create" entry like I did in the demo.

If the concepts above such as types, variables, action chains are new to you - I would highly recommend watching this video on the VBCS Architecture and Building Blocks, it will help you better understand what VBCS is all about.

 

 

 

 

Categories: Development

Lizok's Bookshelf

Greg Pavlik - Sun, 2018-09-30 17:34
The first of Eugene Vodolazkin's novels translated to English was, of course, Laurus, which ranks as one of the significant literary works of the current century. I was impressed by the translators ability to convey not just a feel for what I presume the original has, but a kind of "other-time-yet-our-timeness" that seems an essential part of the authors objective. I recently picked up Volodazkin's Aviator and thought to look up the translator as well. I was delighted to find her blog on modern Russian literature, which can be found here:

http://lizoksbooks.blogspot.com/2018/09/the-2018-nose-award-longlist.html

Sea of Fertility

Greg Pavlik - Sun, 2018-09-23 18:24
In a discussion on some of my reservations on Murakami's take on 20th century Japanese literature, a friend commented on Mishima's Sea of Fertility tetrology with some real insights I thought worth preserving and sharing, albeit anonymously (if you're not into Japanese literature, now's a good time to stop reading):

"My perspective is different: it was a perfect echo of the end of “Spring Snow” and a final liberation of the main character from his self-constructed prison of beliefs. Honda’s life across the novels represents the false path: of consciousness the inglorious decay and death of the soul trapped in a repetition of situations that it cannot fathom being forced into waking. He is forced into being an observer of his own life eventually debasing himself into a “peeping Tom” even as he works as a judge. The irony is rich. Honda decays through the four novels since he clings to the memory of his friend (Kiyoaki) and does not understand the constructed nature his experience and desires. He is asleep. He wants Matsugae’s final dream to be the truth (that they will “...meet again under the Falls.”) His desires have been leading him in a circle and the final scene in the garden is his recognition of what the Abbess (Satoko from Spring Snow) was trying to convey to him. When she tells him, “There was no such person as Kiyoaki Matsugae”, it is her attempt to cure him of his delusion (and spiritual illness that has rendered him desperate and weak - chasing the ego illusions of his youth and seeking the reincarnation of his friend everywhere.) Honda lives in the dream of his ego and desire. In the final scene, he wakes up for the first time. I loved the image of the shadows falling on the garden. He is finally dying, stripped of illusion. I found it to be Mishima at his most powerful. I agree about “Sailor”, that is a great novel and much more Japanese in its economy of expression. Now, Haruki Murakami is a world apart from Kawabata and Mishima. I love his use of the unconscious/Id as a place to inform and enthrall: the labyrinth of dreams. Most of his characters are trapped (at least part of the time) in this “place”: eg Kafka on the Shore, Windup Bird Chronicle, Hard-boiled Wonderland and End of the World, etc. Literature has to have room for all of them. I like the other Murakami, Ryu Murkami, whose “Audition” and “Famous Hits of the Shōwa Era” are dark, psychotic tales of unrestrained, escalating violence but redeemed by deep probing of unconscious, hidden motives (the inhuman work of the unconscious that guides the characters like the Greek sense of fate (Moira)) and occasional black humor."
 

Business Logic for Business Object in Visual Builder - Triggers, Object Functions, Groovy and More

Shay Shmeltzer - Fri, 2018-09-14 18:15

The business objects that you create in Visual Builder Cloud Service (VBCS) are quite powerful. Not only can they store data, manage relationships, and give you a rich REST interface for interacting with them, they can also execute dedicated business logic that deals with the data.

If you click on the Business Rules section of a business object you'll see that you can create:

  • Triggers - allow you to react to data events such as insert, update, and delete on records.
  • Object and field Validators - allowing you to make sure that data at the field or record level is correct.
  • Object Functions - A way to define "service methods" that encapsulate logic related to a business object. These functions can be invoked from various points in your application, and also from outside your app.

To code logic in any of these location you will leverage the Groovy language.

I wanted to show the power of some of the functionality you can achieve with these hook points for logic. The demo scenario below is based on a requirement we got from a customer to be able to send an email with the details of all the children records that belong to a specific master record. Imagine a scenario where we have travel requests associated with specific airlines. When we go to delete an airline we want to send an email that will notify someoe about the travel requests that are going to be impacted by this change.

To achieve this I used an accessor - an object that helps you traverse relationships between the two objects - to loop over the records and collect them.

In the video below you'll see a couple of important points:

  • Business object relationship and how to locate the name of an accessor
  • Using a Trigger Event to send an email
  • Passing an object function as a parameter to an email template
  • Coding groovy in a business object

For those interested the specific Groovy code I used is:

def children = TravelRequests; // Accessor name to child collection def ret_val = "List of travel requests "; if (!children.hasNext()) { return "no impact"; } while (children.hasNext()) { def emprec = children.next(); def name = emprec.name; ret_val=ret_val+" " +name; } return ret_val;

 

By the way - if, like me, you come from a background of using Oracle ADF Business Components you might find many of the things we did here quite familiar. That's because we are leveraging Oracle ADF Business Components in this layer of Visual Builder Cloud Service. So looking up old Groovy tutorial and blogs about ADF BC might prove to be useful here too :-)

 

 

Categories: Development

EnterpriseDB Postgres for Oracle Developers

Gerger Consulting - Tue, 2018-09-11 11:22
EnterpriseDB Postgres is the only open source database in Gartner's Magic Quadrant and has virtually full feature parity with Oracle. Yet, very few of us know about it or use it. Let's change this.

Attend the webinar by two of the best people in EnterpriseDB world and learn how EDB Postgres can help you.

Register at this link.


About the Webinar:

The greatest roadblock that many organizations face in their technology transformation initiatives is their Oracle relationship. High costs and restrictive licensing are preventing many organizations from investing in strategic new data-driven initiatives that strengthen competitiveness.
Recognized by Gartner as the only open source database provider in the Magic Quadrant for Operational Database Management Systems, EDB Postgres is the leading database alternative to Oracle. With Oracle compatibility features and tools that support migrations, often in just hours or days, organizations reduce costs by 80% or more and can deploy the database anywhere without restriction.
In this webinar you’ll learn how:
  • - The EDB Postgres Platform supports Oracle’s PL/SQL and comes with enterprise-level management and diagnostic tools;

  • - EDB’s Oracle migration expertise and technology coupled with Destiny’s application migration expertise helps organizations migrate to Postgres;

  • - The EDB Postgres Platform significantly reduces an organizations TCO costs while still providing enterprise-level capabilities for business needs and applications.


Categories: Development

Increasing Maximum Web Service Requests in Oracle APEX 18.1

Dimitri Gielis - Mon, 2018-09-10 15:29
While running our final tests of APEX Office Print (AOP) 18.1 we hit "ORA-20000: Issue calling Main AOP Service (REST call: ): ORA-20001: You have exceeded the maximum number of web service requests per workspace."


When you login into the Internal Workspace and navigate to a workspace, there's a setting for  Maximum Web Service Requests. The default value is 1000 requests per 24h (rolling window).

If you know that AOP has next to hundreds of server tests, also around 500 automated tests through APEX, we hit this limit after the second full run. After setting the value to 20000, we are able to continue our final testing :)

I guess the chances are small you will hit the limit in a normal APEX app, but if you do, it's easy to fix by setting a higher value for your workspace.
Categories: Development

Continued on arunbavera.wordpress.com

Arun Bavera - Mon, 2018-09-03 15:40
Continued on arunbavera.wordpress.com
Categories: Development

Automating CI/CD for Docker with Oracle Cloud Infrastructure Registry and Developer Cloud Service

Shay Shmeltzer - Tue, 2018-08-28 18:20

In recent releases Developer Cloud Service (DevCS) has expanded to allow you to manage the full life cycle of Infrastructure in addition to software. One area where we made things simpler is in the management of CI/CD for Docker containers.

In this blog entry I'll take you through the basics of setting up a CI/CD chain that publishes Docker containers into the Oracle Cloud Infrastructure Registry (OCIR) - Oracle's cloud hosted docker registry. If you need a little tutorial on getting started with OCIR and docker using command lines - you can use this one.

Here is a demo video showing you how to leverage DevCS to automate the publishing process of Docker images and hook it into the Git repository in DevCS:

A few notes to help you replicate the steps I take in the video:

You will need to configure a DevCS build server that will have the docker software on it to run your builds. You do this from the "organization" menu under your user name (or get your org admin to do this for you). If you need tips, Abhinav shows you how to do it in this blog entry.

In order to work with OCIR from DevCS you'll need to have an Oracle Compute user created and have an auth token generated for it - make sure you have this token available for you as you'll need it when working from DevCS. (Note that this is separate from the password the user uses to login into the Oracle compute dashboard).

User auth in OCI

Now that you have a user, it's time to start your DevCS work. The first thing you might want to do is upload the code for a docker image into your DevCS git repository. In the video below you'll see that I'm using a very simple application and that my docker file is part of the 3 files I upload into the git repository in DevCS. (need some sample code pick up this docker getting started tutorial).

Once your code is in DevCS - the next step is to create a build jobs that pushes your code into the OCI Registry. Our job has three steps that leverage the following docker commands:

  • Login - you'll need to use your tenantName/user as the username and the auth token as the password. Your registry URL will depend on the center you are in iad - Ashburn, phx -Phoenix etc. In my case it was iad.ocir.io.
  • Build - you'll want to specify a complete image name again using your tenant/user/imageName. Also make sure to indicate where your docker file is (or provide a docker file in that step).
  • Push - here everything should already be filled out for you and ready to go.

Here is how the completed job looks like:

Docker Build Job

Now run your build - and it will build your image and if it succeed it will push it to the registry. Monitor the build's log in case you run into errors.

You can hook up the docker registry section of DevCS to point to the OCIR registry - and then you'll be able to see all the images you pushed there. Again provide your tenant/user and auth token as the login info to the repository.

Docker Registry View in DevCS

Have fun automating your docker publishing on the Oracle Cloud!

 

Categories: Development

Replace deprecated apex_util.string_to_table (APEX 5.1/18.1)

Dimitri Gielis - Wed, 2018-08-15 15:44
Sometimes the Oracle APEX documentation announces some packages will become deprecated in a release. It's not that those packages are suddenly gone, but you should not use them anymore. Your code will run fine still, but in the future, APEX might take it out completely, so it's best to replace them with the new package.

One of those packages announced in Oracle APEX 5.1 that are deprecated, and which I used a lot, was apex_util.string_to_table.

For example, in APEX Office Print (AOP) we read the session state of page items and we have some code like this:
declare
l_string varchar2(4000) := 'P1_X:P1_Y';
l_page_items_arr apex_application_global.vc_arr2;
begin
l_page_items_arr := apex_util.string_to_table(p_string => l_string, p_separator => ':');
for i in 1..l_page_items_arr.count
loop
sys.htp.p(l_page_items_arr(i)||':'||apex_util.get_session_state(l_page_items_arr(i)));
end loop;
end;

As the function is deprecated and APEX is already on release 18.1, it's good to start replacing those calls. The new function you can use is apex_string.split.

The above code becomes then:
declare
l_string varchar2(4000) := 'P1_X:P1_Y';
l_page_items_arr apex_t_varchar2;
begin
l_page_items_arr := apex_string.split(p_str => l_string, p_sep => ':');
for i in 1..l_page_items_arr.count
loop
sys.htp.p(l_page_items_arr(i)||':'||apex_util.get_session_state(l_page_items_arr(i)));
end loop;
end;

Depending on your application, you might need to be careful. For example with AOP, we support customers with versions of Oracle APEX 5.0, 5.1 and 18.1. We can't really force customers to move to higher APEX versions, so the way we solve it is by using conditional compilation of our code. If we see you are on APEX 5.1 or above we will use apex_string.split if not, and you are still on an earlier version, we will use apex_util.string_to_table.

Here's an example of what the code with conditional compilation looks like:
  $if wwv_flow_api.c_current >= 20160824
$then

l_page_items_arr := apex_string.split(p_str=>l_string, p_sep=>':');
$else
l_page_items_arr := apex_util.string_to_table(p_string=>l_string, p_separator=>':');
$end

Note the conditional compilation you also need to do on the variable if they are different, or you can choose to conditional compile on the entire function.

To conclude, I recommend with every new release of Oracle APEX to look for deprecated components and search for those and make notes to change those when needed.
Categories: Development

Patching E-Business Suite without Downtime

Gerger Consulting - Wed, 2018-08-15 11:58

Downtime during upgrades can cause a lot of headaches to E-Business Suite customers. No more!

Attend the free webinar by Oracle ACE Associate and Chicago OUG President Alfredo Abate and learn how you can patch your EBS installations without any downtime.

Register at this link.


Categories: Development

Edit Form in a Popup with Oracle Visual Builder

Shay Shmeltzer - Mon, 2018-08-06 11:51

In Visual Builder Cloud Service (VBCS) it is very easy to create a CRUD application where one page shows you a list of records, and then clicking on one of them you are directed to another page where you edit the record. But what if instead you want to edit the record on the same page - or in a popup window when you click the record in the table?

This is what this blog is all about - the results looks like this:

Edit Popup

The video combines several techniques and tips, some of which I covered in details in other blog entires. Since the video is a bit on the long side (14 min) - here is a break down of what it shows and a way for you to skip to the parts that interest you:

 

Categories: Development

Tips and Tricks for List of Values in Visual Builder Cloud Service

Shay Shmeltzer - Fri, 2018-08-03 17:39

While working on some customers' applications, I ran into a few performance and functionality tips related to list of values in Visual Builder Cloud Service (VBCS). While it is very tempting to use the built in quick start that binds a list to results from a service, in some cases you might want to take a different approach.

One reason is performance - some lists don't change very often - and it makes more sense to fetch them only once instead of at every entry into a page. VBCS offers additional scopes for variables - and in the demo below I show how to use an application scope to only fetch a list of countries once (starting at 1:15). I also show how to define an array that will store the values of the list in a way that will allow you to access them from other locations in your app, and not just the specific UI component.

The other scenario that the demo shows relates to situations where you need to get additional information on the record you selected in the list. For example, your list might have the code and label but might contain additional meaningful fields. What if you need access to those values for the selected record?

In the demo below (6:40), I use a little JavaSciprt utility method that I add to the page to get the full details of the selected record from the list. The code used is (replace the two bold names with the id field and the value you want to return):

PageModule.prototype.FindThreeLetter = function(list,value) {
 return  list.find(record => record.alpha2_code === value).alpha3_code;
}

In the past, any array used for an LOV had to have "label" and "code" fields, but Oracle JET now allows you to set other fields to act in those roles this is shown at 5:54 using the options-keys property of the list component - a combobox in my case.

Check it out:

Categories: Development

"ORA-22902: CURSOR expression not allowed" in ORDS and APEX and how to fix them

Dimitri Gielis - Tue, 2018-07-24 06:18
When you want to define different blocks of data, some hierarchical, some not, you can do that by using the cursor expressions in SQL. An example of a query looks like this:

  select
    c.cust_first_name,
    c.cust_last_name,
    c.cust_city,
    cursor(select o.order_total, order_name,
              cursor(select p.product_name, i.quantity, i.unit_price
                       from demo_order_items i, demo_product_info p
                      where o.order_id = i.order_id
                        and i.product_id = p.product_id
                    ) product                
             from demo_orders o
            where c.customer_id = o.customer_id
          ) orders
  from demo_customers c

In the above query you see you can nest the cursor expressions. But you can also define the cursors next to each other. We use this technique a lot when defining where the data comes from in the APEX Office Print (AOP) APEX plugin:


In Oracle Application Express 18.1 there's a small bug (Bug 28298260 - REGRESSION: SQL QUERY CONTAINING CURSOR EXPRESSION CAN'T BE PARSED) that when you validate the query you get "ORA-22902: CURSOR expression not allowed".

The APEX Dev team already fixed it - you can download from Oracle Support the bundle PSE patch #28128115. Once applied everything is validating correctly again.


AOP also supports REST web services, and some people define those in ORDS (Oracle REST Data Services). Depending the version of ORDS you might get the same error: "Error during evaluation of resource template: GET test/cursor/, SQL Error Code: 22,902, SQL Error Message: ORA-22902: CURSOR expression not allowed"



This doesn't mean your environment can not use the cursor syntax, you just have to set the pagination size to 0 and it's fixed.

In the latest version of ORDS (18.2) you get by default the 500 error without the error number:


Fix is the same, set pagination to 0 and you are good to go.

Categories: Development

Hide certain objects on an APEX page

Dimitri Gielis - Sat, 2018-07-21 17:20
A few days ago I got a question on how to hide the title row from the Interactive Report Pivot view.
So the person didn't want to show the red area:


The solution to this problem is to add the following CSS to your page:


table.a-IRR-table--pivot tr:nth-child(3) {
    display:none;
}

The result is this - the title is gone:


Doing this blog post is not about giving the solution to the above problem. I find it more important to show you the process to come to your answer. It comes down to find the right elements on the page which you can manipulate with CSS or JavaScript. To hide something, you can either use CSS with display: none or a JavaScript function (or JQuery hide()). The first thing you do is a search for the element. You want to use the Developer Tools of your browser for that. Most of the time you can right click on your page and do Inspect Element. The browser will show the HTML that is behind what you see on the page.


In the above screenshot, I see that row is a TR in a Table.
So the next step is to find a way to select that element. Typically you would use the id or class tag and look that up. The TR in our case doesn't have any of those, so I went up a line in the hierarchy until I find a good selector. The table has a class a-IRR-table--pivot which we can use.
Once we have the selector, we want to go to the real element, so we navigate back down. Now you need to know a bit of JavaScript or CSS or search on the internet how to do that. You can add elements after each other and it will drill down in the hierarchy again.
In our case, the TR is the third TR in the table, and there's a function to select that, which I used in CSS (nth-child).

If this is all new to you, learning about JavaScript and CSS selectors is a great start. For example, W3School is a nice site to get started learning more about HTML, CSS, JavaScript, and general web.

Categories: Development

I'll be at APEX Meetup Munich: Thu 19 Jul 2018

Dimitri Gielis - Sun, 2018-07-15 06:14
Just a quick note I'll do two presentations at the APEX Meetup in Munich on Thursday, July 19th 2018.

In the first presentation I'll bring you to a virtual and augmented world, entirely build in Oracle Application Express (APEX). There are 30 Google Cardboards available to make the experience complete. Fun guaranteed! :)


At KScope I was also interviewed by Bob Rhubart on my talks over there, which the AR/VR presentation was one of them.


In my second presentation at Munich I'll show the upcoming version of APEX Office Print (AOP).
I'll show some features nobody has seen before :) With every major release of AOP I feel like this:


If you are in the Munich area I would love to meet you at the meetup.

Categories: Development

My top 5 APEX 18.1 Plugins

Dimitri Gielis - Sat, 2018-07-14 05:46
With every new version of Oracle Application Express (APEX) new features are added and the life of a developer is made even easier. If the feature set is not enough or you see you need to build the same functionality more often, you can always extend APEX with plug-ins.

There are six different types of plugins: dynamic action, item,  region, process, authentication scheme and authorization scheme.

Plug-ins are absolutely fantastic to extend the native functionalities of APEX in a declarative way. The APEX plugin becomes a declarative option in the APEX Builder and has the [Plug-in] text next to it. In the next screenshot, you see the dynamic actions being extended by two Plug-ins.


If you are searching for an APEX plug-in, I typically go to APEX World > Plug-ins. The nice thing about that site is that the plug-ins seem to be maintained, so if a plug-in is not supported anymore it gets the status deprecated.

!! And here lays the catch with using plug-ins. When you decide to use a plug-in in your project, you become responsible for this and need to make sure it's compatible with every release of Oracle APEX. Many plug-ins are open source and many plug-in developers maintain their plug-ins, but it's really important you understand that at the end you are responsible for things you put in your application. If the plug-in is not secure or it breaks in the next release of APEX, you need to find a solution. So use plug-ins with care and see for example how many likes the plug-in has or what the comments are about the plug-in or author. Oracle is not reviewing or supporting the plug-ins !!

When I saw the tweet of Travis, I thought to do a blog post on my top 5 plugins I use in almost every project.


Here we go:

1. Built with love using Oracle APEX

I'm proud to built applications with Oracle Application Express, and this plug-in makes it very clear :) At the bottom of the app, you will see this text:


Note that in Oracle APEX 18.1 this text in included by default and you don't even need to add the plugin. Nevertheless, I wanted to include it in this list as it should be there in every app, even the ones built before APEX 18.1 :)

2. Select2

When a select list (or drop-down) has many values, it takes too long to find the right value. Select2 makes it easy to search for values, it also supports lazy loading and multiple select.


3. APEX Office Print

APEX Office Print extends APEX so it becomes possible to export to native Excel files and generate documents in Word, Powerpoint, PDF, HTML and Text, all based on your own template. It has many more features, I blogged about some before.



4. Dropzone

APEX 18.1 has declarative multi-file upload, but still, I love the Dropzone plugin developed by Daniel Hochleitner. You can drag multiple files from your desktop straight in your APEX app. Daniel is one of my favorite plug-in developers. When he releases something, you know it will be good.



5. Modal LOV

This is a newer plugin and I haven't used it that much yet, but I'm sure I will do. The nice thing with this item type plugin is that it also supports Interactive Grid. Where Select2 stays within the page, this Modal LOV comes with a modal list of values (pop-up) which is great if you want to show multiple columns or need more context for the record you need to select.


There are many more plug-ins out there, most of them work on APEX 5.x and upwards. For example, Pretius has some cool plug-ins too, the one to create nested reports I recently used in a project. Another site you can find plug-ins is APEX-Plugin.com.

Categories: Development

Automatically capture all errors and context in your APEX application

Dimitri Gielis - Sat, 2018-06-30 16:30
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; }
Categories: Development

How to Export to Excel and Print to PDF in Oracle APEX? The answer...

Dimitri Gielis - Thu, 2018-06-28 16:46
Two questions that pop-up a lot when I'm at a conference or when doing consulting are:
  • 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?
The reason those questions are asked every time again is that although those features exist to a certain extent in APEX, what you actually want, is not shipped with Oracle Application Express (APEX), at least not yet in Oracle APEX 18.1 and before.

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 :)
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development