Feed aggregator

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane)

Richard Foote's Blog - Tue, 10/09/2018 - 06:16
In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate. Understanding […]

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie)

Richard Foote's Blog - Thu, 10/04/2018 - 08:00
When it comes to indexing a partitioned table, many automatically opt for Local Indexes, as it’s often assumed they’re simply easier to manage and more efficient than a corresponding Global Index. Having smaller index structures that are aligned to how the table is partitioned certainly has various advantages. The focus in this little series is on […]

Increasing Maximum Web Service Requests in Oracle APEX 18.1

Dimitri Gielis Blog - Mon, 09/10/2018 - 20: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.

New Zealand: “Oracle Indexing Internals and Best Practices” Seminars November 2018.

Richard Foote's Blog - Thu, 08/23/2018 - 22:37
Good news for those of you in beautiful New Zealand. Due to popular demand, I’ll be returning to run some of my acclaimed “Oracle Indexing Internals and Best Practices” seminars in November 2018. The dates and events are: Wellington: 19-20 November 2018: Registration Here or Buy Directly Here Auckland: 21-22 November 2018: Registration Here or […]

“Let’s Talk Database” coming to Wellington, Auckland, Brisbane and Perth in September.

Richard Foote's Blog - Fri, 08/17/2018 - 00:08
Due to popular demand, I’ve been asked by Oracle to again run some more “Let’s Talk Database” events in September. Dates and venues are as follows: Monday, 2 September – Wellington (Wellington Oracle Office): Registration Link. Tuesday, 3 September – Auckland (Auckland Oracle Office): Registration Link. Wednesday, 12 September – Brisbane (Brisbane Oracle Office): Registration Link. Thursday, 13 September – Perth (Perth Oracle Office): Registration Link. […]

Replace deprecated apex_util.string_to_table (APEX 5.1/18.1)

Dimitri Gielis Blog - Wed, 08/15/2018 - 20: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.

FAQ: Webinars for “Oracle Indexing Internals and Best Practices”

Richard Foote's Blog - Mon, 07/30/2018 - 10:01
I’ve been somewhat inundated with questions regarding the “Oracle Indexing Internals and Best Practices” webinar series I’ll be running in October and November since I announced both webinar series last week. So I’ve compiled the following list of frequently asked questions which I’m hoping will address most of those asked. If you have any additional […]

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

Dimitri Gielis Blog - Tue, 07/24/2018 - 11: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.

Announcement: Webinars for “Oracle Indexing Internals and Best Practices” Now Confirmed !!

Richard Foote's Blog - Mon, 07/23/2018 - 08:28
Exciting News !! I can now confirm the dates for my first webinars of my fully updated and highly acclaimed “Oracle Indexing Internals and Best Practice” seminar. For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page. The webinars will run for 4 hours each day, spanning a full week period […]

Hide certain objects on an APEX page

Dimitri Gielis Blog - Sun, 07/22/2018 - 08:16
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.


Note that in Oracle APEX, you can also use a dynamic action to hide or show certain elements. A dynamic action is a declarative way to do JavaScript, so when you use the Hide / Show in a dynamic action, behind the scenes it will do the necessary call for you. If the item or region is not known for APEX you would use the same technique as I described above to find the right element which you can reference in the dynamic action with a JavaScript selector (. for class # for id).
Typically doing something with CSS is more performant than doing it with JavaScript, but it all depends on your use case what technique makes sense to use.

Announcement: Venue Confirmed For Upcoming Brussels “Oracle Indexing Internals and Best Practices” Seminar

Richard Foote's Blog - Wed, 07/18/2018 - 07:55
I can finally confirm the venue for my upcoming “Oracle Indexing Internals and Best Practices” seminar in beautiful Brussels, Belgium running on 27-28 September 2018. The venue will be the Regus Brussels City Centre Training Rooms Facility, Avenue Louise / Louizalaan 65, Stephanie Square, 1050, Brussels. Note: This will be the last public seminar I’ll run […]

Rebuilding Indexes: Danger With Clustering Factor Calculation (Chilly Down)

Richard Foote's Blog - Tue, 07/17/2018 - 06:33
Let me start by saying if you don’t already following Jonathan Lewis’s excellent Oracle blog, do yourself a favour. In a recent article, Jonathan highlighted a danger with rebuilding indexes (or indeed creating an index) when used in relation to collecting index statistics with the TABLE_CACHED_BLOCKS preference. I’ve discussed the importance of the TABLE_CACHED_BLOCKS statistics […]

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

Dimitri Gielis Blog - Sun, 07/15/2018 - 11: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.

My top 5 APEX 18.1 Plug-ins

Dimitri Gielis Blog - Sun, 07/15/2018 - 10:43
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 plug-ins: 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 plug-in 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 plug-ins 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 plug-in. 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 plug-in 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 plug-in and I haven't used it that much yet, but I'm sure I will do. The nice thing with this item type plug-in 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.

Index Column Order – Impact On Index Branch Blocks Part II (The Weeping Song)

Richard Foote's Blog - Thu, 07/05/2018 - 02:25
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

Richard Foote's Blog - Tue, 07/03/2018 - 06:49
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

Dimitri Gielis Blog - Sat, 06/30/2018 - 21:37
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; }
Subscribe to Better Logic LLC aggregator