]]>https://brainfizzle.wordpress.com/2016/04/29/script-disable-fk-constraints-referencing-a-table/feed/0zebforneyOracle PL/SQL – Exceptions and Native Dynamic SQLhttps://brainfizzle.wordpress.com/2016/02/17/oracle-plsql-exceptions-and-native-dynamic-sql/
https://brainfizzle.wordpress.com/2016/02/17/oracle-plsql-exceptions-and-native-dynamic-sql/#respondThu, 18 Feb 2016 03:22:51 +0000http://brainfizzle.wordpress.com/?p=122]]>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:

]]>https://brainfizzle.wordpress.com/2016/02/17/oracle-plsql-exceptions-and-native-dynamic-sql/feed/0zebforneyMFC – Dynamically Creating Interactive Panelshttps://brainfizzle.wordpress.com/2015/09/27/mfc-dynamically-creating-interactive-panels/
https://brainfizzle.wordpress.com/2015/09/27/mfc-dynamically-creating-interactive-panels/#respondSun, 27 Sep 2015 21:33:47 +0000http://brainfizzle.wordpress.com/?p=91]]>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.

]]>https://brainfizzle.wordpress.com/2015/09/27/mfc-dynamically-creating-interactive-panels/feed/0zebforneyMFC Dynamically Creating Interactive Panels - Main WindowMFC Dynamically Creating Interactive Panels - ScrollingMFC Dynamically Creating Interactive Panels - Left ClickMFC Dynamically Creating Interactive Panels - Right ClickOracle – Find Blocking Sessionshttps://brainfizzle.wordpress.com/2015/06/02/oracle-find-blocking-sessions/
https://brainfizzle.wordpress.com/2015/06/02/oracle-find-blocking-sessions/#respondWed, 03 Jun 2015 01:30:04 +0000http://brainfizzle.wordpress.com/?p=84]]>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.

]]>https://brainfizzle.wordpress.com/2015/06/02/oracle-find-blocking-sessions/feed/0zebforneyOracle – Update Multiple Columns with a Single Subqueryhttps://brainfizzle.wordpress.com/2015/02/24/oracle-update-multiple-columns-with-a-single-subquery/
https://brainfizzle.wordpress.com/2015/02/24/oracle-update-multiple-columns-with-a-single-subquery/#respondWed, 25 Feb 2015 04:03:00 +0000http://brainfizzle.wordpress.com/?p=81]]>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:

]]>https://brainfizzle.wordpress.com/2015/02/24/oracle-update-multiple-columns-with-a-single-subquery/feed/0zebforneyORACLE – Using DENSE_RANK and KEEP to simplify multi-layer analytic querieshttps://brainfizzle.wordpress.com/2014/12/06/using-dense_rank-and-keep-to-simplify-multi-layer-analytic-queries/
https://brainfizzle.wordpress.com/2014/12/06/using-dense_rank-and-keep-to-simplify-multi-layer-analytic-queries/#respondSun, 07 Dec 2014 04:14:44 +0000http://brainfizzle.wordpress.com/?p=77]]>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.

]]>https://brainfizzle.wordpress.com/2014/12/06/using-dense_rank-and-keep-to-simplify-multi-layer-analytic-queries/feed/0zebforneyOptimized approach to addressing LISTAGG() and the ORA-01489 errorhttps://brainfizzle.wordpress.com/2014/11/17/optimized-approach-to-addressing-listagg-and-the-ora-01489-error/
https://brainfizzle.wordpress.com/2014/11/17/optimized-approach-to-addressing-listagg-and-the-ora-01489-error/#respondTue, 18 Nov 2014 03:55:00 +0000http://brainfizzle.wordpress.com/?p=75]]>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:

]]>https://brainfizzle.wordpress.com/2014/11/17/optimized-approach-to-addressing-listagg-and-the-ora-01489-error/feed/0zebforneyOracle LISTAGG(), ORA-01489 – Aggregating using CLOBs to handle long stringshttps://brainfizzle.wordpress.com/2014/11/05/oracle-listagg-ora-01489-aggregating-using-clobs-to-handle-long-strings/
https://brainfizzle.wordpress.com/2014/11/05/oracle-listagg-ora-01489-aggregating-using-clobs-to-handle-long-strings/#commentsWed, 05 Nov 2014 23:00:00 +0000http://brainfizzle.wordpress.com/?p=70]]>In a previous post, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, I wrote about long string concatenations exceeding the 32767 limit for VARCHAR2s. This article provided a query that limited the total length of the concatenation to cut itself off at 32767. This allows the user to use the advantages of LISTAGG() while returning as much data as possible.

The goal of this article is to provide an alternative that will make use of CLOBs to avoid character limits. Below is a user-defined aggregate function that is loosely based on askTom’s STRAGG() function. This function allows aggregation of very long strings, but note that this is an aggregate function, not an analytic function. It does not support the user-defined delimiter, WITHIN GROUP() clause and PARTITION BY clause that make LISTAGG() quite useful. The order in which strings are aggregated cannot be specified by the user.

As a note of interest, STRAGG() was originally introduced to fill a void in string aggregation prior to the introduction of the LISTAGG() function. It gained widespread use throughout the Oracle community.

]]>https://brainfizzle.wordpress.com/2014/11/05/oracle-listagg-ora-01489-aggregating-using-clobs-to-handle-long-strings/feed/2zebforneyOracle – Handling PL/SQL errors in a static and dynamic contexthttps://brainfizzle.wordpress.com/2014/11/02/oracle-handling-plsql-errors-in-a-static-and-dynamic-context/
https://brainfizzle.wordpress.com/2014/11/02/oracle-handling-plsql-errors-in-a-static-and-dynamic-context/#respondSun, 02 Nov 2014 16:39:51 +0000http://brainfizzle.wordpress.com/?p=67]]>PL/SQL error handling is done using the EXCEPTION block of the PL/SQL procedure. The construct of this block is fairly straightforward and well documented. The intent of this article is not to explain this block, but to present two approaches to error handling. The first is a “static” approach, in which possible error codes are defined at compile time. This is the most commonly used approach. However, sometimes it is useful to define procedures that can be passed dynamic error codes to be handled. This is sometimes useful for library functions where a single block of code my raise different errors based on the conditions in the calling function. The calling function may pass in an error code that is expected into the function where it can be handled. The example below encapsulates both of these approaches:

The div_by_zero procedure exists only as an example error to be handled. The error code for divide by zero is -1476, which will be of interest in the following paragraphs. Let’s first focus on the handle_static_error procedure. As the name suggests, this function handles a static error code defined at compile time. In the declaration block of the procedure, note the div_ex EXCEPTION; and PRAGMA EXCEPTION_INIT( div_ex, -1476 ); statements. The first simply defines an EXCEPTION variable and gives it a name. The call to EXCEPTION_INIT is key in that it maps the error code of interest to our EXCEPTION variable. With this information in hand, it is time to look to the EXCEPTION block of the handle_static_error procedure. The WHEN div_ex THEN statement defines where the divide by zero error will be captured and handled. The DBMS_OUTPUT statement simply writes to the console as an error handler. Defining EXCEPTION variables like this is the most common approach to error handling.
The handle_dynamic_error procedure demonstrates an alternative approach to error handling. This procedure takes an error code as a parameter. In the EXCEPTION block of this procedure, note that no EXCEPTION variable is referenced. Instead, the IF SQLCODE = expected_ex THEN uses a a special PL/SQL function. SQLCODE returns the code of the most recently raised error, which is then compared to the error code passed in as a parameter to the procedure. If a match exists, the DBMS_OUTPUT line is called as the error handler.
Generally speaking, the use of EXCEPTION variables and the PRAGMA EXCEPTION_INIT statement is the preferable way to implement error handling. However, the above does provide an alternative when dynamic error codes are necessary.

]]>https://brainfizzle.wordpress.com/2014/11/02/oracle-handling-plsql-errors-in-a-static-and-dynamic-context/feed/0zebforneyOracle – Writing a BLOB to an operating system filehttps://brainfizzle.wordpress.com/2014/10/26/oracle-writing-a-blob-to-an-operating-system-file/
https://brainfizzle.wordpress.com/2014/10/26/oracle-writing-a-blob-to-an-operating-system-file/#respondMon, 27 Oct 2014 01:19:01 +0000http://brainfizzle.wordpress.com/?p=65]]>Earlier, I wrote about reading a BLOB from file in an Oracle PL/SQL procedure. Here is an example of writing a BLOB out to file. Just as before, the user will need access to a DIRECTORY object that points to the OS file destination of choice. See the article on loading BLOBs for more information on this.

The write_blob_to_file procedure in the example below provides the functionality to write to file. It has been wrapped in an anonymous block to demonstrate usage: