EXECUTE IMMEDIATE statements act as separate sessions, so it is important to note that exception propagation from these statements also follows the same model. When an exception propagates beyond an EXECUTE IMMEDIATE entry point, a rollback is incurred. This may come as a surprise since normal procedure calls do not rollback, leaving transaction control in the hands of the programmer. Here is an example to demonstrate. First, some support objects:

As an exercise in dynamically creating controls and custom event handling, this article will walk through a simple thumbnail control that supports both left- and right-mouse clicks. The control will populate itself with a dynamic set of panels. The control posts two custom messages to its parent: WM_ELEMCLICK and WM_ELEMRIGHTCLICK. When the user left-clicks a panel, WM_ELEMCLICK will be posted to the parent dialog for the event. When the user right-clicks a panel, a WM_ELEMCLICK message will be posted to indicate a changing selection and a pop-up menu will be presented. If the user clicks the pop-up menu, a WM_ELEMRIGHTCLICK message will also be posted to the parent window. Any of this functionality can be easily modified based on the desired application. To keep the scope in check, each panel will be drawn as a simple, solid-colored panel. In a real application, a more sophisticated OnPaint() function will be needed to render images or dynamically draw application-specific data.

The control can handle a large number of panels. For this specific example, we are loading the control with five panels, but only displaying three at a time. The control supports a function to scroll left and right, which is called when dialog buttons are clicked. Below are a few screenshots.

The main window before any user interaction:

Notice the change in panels below as the user has selected to scroll right by one panel (by clicking the button labeled Right):

Here, the user has left-clicked a panel. The panel has highlight itself and a left-click message has been posted to the dialog. This is indicated by the text control:

Finally, the image below shows the progression of a right-click, including the pop-up menu and events indicated by the text control:

This program consists of three primary classes:

CBaseStatic – This is the most fundamental of the classes. It extends a CStatic class and encapsulates a single panel in our thumbnail control. It handles the left button click and a right mouse-button up event. Interestingly, CStatic does not handle left- and right-clicks in the same way.

CBaseCollection – This is the control class from a user’s perspective. It also extends the CStatic MFC class. When the AddElement() function is called, a new CBaseStatic panel is dynamically created and added to the control. It tracks a vector of these panels, and determines the dynamic width of each panel by dividing its client width by the number of displayed panels. It also tracks the left-most displayed panel and uses the DisplayBases() function to handle rendering the control.

CDynamicWidgetDlg – This is the parent dialog used primarily for example purposes. It contains an instance of the CBaseCollection control, a CEdit control to provide feedback on handled messages, and buttons to allow scrolling back and forth.

Blocking sessions can occur when two transactions try to update the same set of data. The first transaction will attain a lock on the data and will become the “blocking” transaction. If a second transaction attempts to update that same data, it will not be able to complete until the first transaction terminates (via a COMMIT or ROLLBACK). Below is a simple example of such a scenario:
Transaction 1

Notice that this command does not return. It is blocked. Transaction 1 will either need to complete or be killed in order to allow transaction 2 to complete.

There are a number of different queries that will help find blocking sessions. Depending on the type of underlying block, only some of these will return the session that needs to be killed. Here are some of the common ones I use:

This one is interesting as it helped me find a session preventing a package from compiling. I could not get any “standard” blocking session queries to return. The root issue was a job that was hung and then killed using DBMS_SCHEDULER.DROP_JOB with the FORCE parameter set to true. The job was no longer present in the job views, but the cache pin remained. This query is adapted from The Oracle Community.

In Oracle, it is possible to update multiple columns with data from a single subquery. The syntax is only slightly different than the typical update statement. The columns being assigned values must be enclosed by parentheses. An example follows:

In queries, I commonly need to extract only the top of a sorted list of results. As time has progressed, ROW_NUMBER() and the OVER() clause have proven essential in writing simple and fast queries to meet this need. Below is an example. In this case, we have an table that captures unit pricing of products by product name and date. We want to write a function that simply returns a text version of the price for display and “NO PRICE FOUND” if nothing is available.

The get_latest_price() function does a good job of quickly extracting the latest available price for the passed product. You might argue that this is the most readable approach to extracting the required data. However, there are two points of get_latest_price() that make it slightly complicated:

1) The query that has two layers: an inner query to extract a list of of prices and their associated row numbers ordered by date, and an outer query to select only the first row of the inner query.
2) Since a NO_DATA_FOUND is an expected condition, an EXCEPTION block is needed to handle the return.

Below is an example of using the KEEP and DENSE_RANK analytic functions to achieve the same behavior with a single-layer query and no EXCEPTION block. Depending on your personal preference, you may not find this version easier to read. I also do not believe there is any cost/benefit in terms of speed.

Two previous posts have discussed the topic of LISTAGG() and the ORA-01489: result of string concatenation is too long error. This occurs when aggregations of strings exceed the 32767 limit of the VARCHAR2 datatype. The first article, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, provides a way to “truncate” aggregations being passed to LISTAGG() to show as much data as possible without exceeding the VARCHAR2 size limit. The second, ORA-01489 – Aggregating using CLOBs to handle long strings, provides a user-defined aggregation function that works on CLOBs. This approach addresses the size limitations of VARCHAR2, but suffers heavily in performance due to CLOB processing. This article presents one additional alternative. This user-defined aggregate function is designed to operate on VARCHAR2 datatypes, but relies on CLOBs internally to move beyond the VARCHAR2 size limitation. A significant performance improvement is realized thanks to faster processing and appending of VARCHAR2s when available. CLOBs are only used once the length of data exceeds the VARCHAR2 limit.

When aggregating 500 rows, this revised version runs approximately 2.5 times faster than the CLOB-only function of the previous article. When aggregating 1000 rows, it runs approximately twice as fast. An example of usage follows: