Script – Disable FK Constraints Referencing a Table

Below is an Oracle SQL script to generate DISABLE and ENABLE commands for all foreign key constraints referencing a table. For more info, see Finding table dependencies (foreign key constraints) in Oracle – walking the dependency tree.

table_dep.sql

REM ###########################################################
REM Generates enable and disable constrains for all tables
REM with FK dependencies on the specified table.
REM USAGE: table_dep <owner> <name>
REM ###########################################################

SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF

COLUMN cmdpar1 NEW_VALUE table_owner NOPRINT
COLUMN cmdpar2 NEW_VALUE table_name NOPRINT

SET TERMOUT OFF
SELECT 
 UPPER( '&1' ) cmdpar1, 
 UPPER( '&2' ) cmdpar2
FROM dual;
SET TERMOUT ON

PROMPT #########################################
PROMPT DISABLE SCRIPTS
PROMPT #########################################
SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' DISABLE CONSTRAINT ' || c.constraint_name || ';'
FROM dba_constraints r, dba_constraints c
WHERE r.owner = c.r_owner
 AND r.constraint_name = c.r_constraint_name
START WITH r.owner = '&TABLE_OWNER' AND r.table_name = '&TABLE_NAME'
CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 AND r.table_name = PRIOR c.table_name;
 
PROMPT
PROMPT
 
PROMPT #########################################
PROMPT ENABLE SCRIPTS
PROMPT #########################################
SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' ENABLE CONSTRAINT ' || c.constraint_name || ';'
FROM dba_constraints r, dba_constraints c
WHERE r.owner = c.r_owner
 AND r.constraint_name = c.r_constraint_name
START WITH r.owner = '&TABLE_OWNER' AND r.table_name = '&TABLE_NAME'
CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 AND r.table_name = PRIOR c.table_name;


SET FEEDBACK ON
SET HEADING ON
SET VERIFY ON

UNDEFINE &table_owner
UNDEFINE &table_name

Oracle PL/SQL – Exceptions and Native Dynamic SQL

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:

SQL> CREATE TABLE test_tab
  2  (
  3    id NUMBER
  4  );

Table created.


SQL> CREATE OR REPLACE
  2  PROCEDURE error_proc
  3  IS
  4  BEGIN
  5
  6    INSERT INTO test_tab ( id )
  7    VALUES ( 1 );
  8
  9    RAISE_APPLICATION_ERROR( -20001, 'BOOM!' );
 10
 11  END;
 12  /

Procedure created.

Now, an example of exception propagation during a normal procedure call. Notice that, although the exception is thrown, the executed DML remains intact.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    error_proc;
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

        ID
----------
         1


SQL> ROLLBACK;

Rollback complete.

Finally, an almost identical example using Dynamic SQL. Notice that this time the DML is lost to a rollback incurred by the EXECUTE IMMEDIATE.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    EXECUTE IMMEDIATE 'BEGIN error_proc; END';
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

no rows selected

MFC – Dynamically Creating Interactive Panels

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:
MFC Dynamically Creating Interactive Panels - Main Window

Notice the change in panels below as the user has selected to scroll right by one panel (by clicking the button labeled Right):
MFC Dynamically Creating Interactive Panels - Scrolling

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:
MFC Dynamically Creating Interactive Panels - Left Click

Finally, the image below shows the progression of a right-click, including the pop-up menu and events indicated by the text control:
MFC Dynamically Creating Interactive Panels - Right Click

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.

The code is listed below.
BaseStatic.h:

#pragma once

// static controls do not fire a right button click, so we'll post this message 
// to the CBaseCollection encapsulating us
static int WM_BASERIGHTCLICK = RegisterWindowMessage(_T("BASERIGHTCLICK"));

class CBaseStatic : public CStatic
{
	DECLARE_DYNAMIC(CBaseStatic)

public:
	CBaseStatic(COLORREF col);
	virtual ~CBaseStatic();
	void SetHighlight(bool highlight);
	afx_msg void OnRButtonUp(UINT nFlags, CPoint point);

protected:
	DECLARE_MESSAGE_MAP()
	afx_msg void OnPaint();

private:
	COLORREF m_color;	// The color used to paint this panel
	bool m_highlight;	// True if this panel should draw itself as highlighted
};

BaseStatic.cpp:

#include "stdafx.h"
#include "DynamicWidget.h"
#include "BaseStatic.h"

IMPLEMENT_DYNAMIC(CBaseStatic, CStatic)

CBaseStatic::CBaseStatic(COLORREF col)
{
	m_color = col;
	m_highlight = false;
}

CBaseStatic::~CBaseStatic()
{
}

BEGIN_MESSAGE_MAP(CBaseStatic, CStatic)
	ON_WM_PAINT()
	ON_WM_RBUTTONUP()
END_MESSAGE_MAP()

// Sets the index to be highlighted (selected)
// It will be surrounded by a 3D rectangle
void CBaseStatic::SetHighlight(bool highlight)
{
	m_highlight = highlight;
}

// Paint handler
void CBaseStatic::OnPaint()
{
	CPaintDC dc(this);
	CBrush brush;
	CRect rect;

	GetClientRect(&rect);

	// This is unneeded as we are filling this entire control
	// with a color block. However, for most applications, the
	// background will need to be erased before redrawing.
	brush.CreateSolidBrush(::GetSysColor(COLOR_3DFACE));
	dc.FillRect(rect, &brush);

	// REPLACE THIS BLOCK WITH YOUR DESIRED CONTROL
	brush.CreateSolidBrush(m_color);
	dc.FillRect(rect, &brush);
	// END REPLACE

	// Paint a 3D rectangle around this control if it is selected
	if (m_highlight)
	{
		dc.Draw3dRect(rect, RGB(255, 255, 255), RGB(0, 0, 0));
	}

	CStatic::OnPaint();
}

// Right mouse button handler.
// CStatics fire a button click event, but not a right-button click.
// This supports our DIY right-button behavior
void CBaseStatic::OnRButtonUp(UINT nFlags, CPoint point)
{
	CPoint *pPoint = new CPoint(point);

	// We'll be displaying a pop-up menu, which
	// is displayed using Screen coordinates
	ClientToScreen(pPoint);

	// Send a message to the CBaseCollection class
	// with a the pop-up menu point and the CtrlID
	// for this particular panel. Both will be needed
	// by the parent CBaseCollection class.
	WPARAM wparam = (WPARAM)pPoint;
	LPARAM lparam = (LPARAM)GetDlgCtrlID();
	GetParent()->PostMessageW(WM_BASERIGHTCLICK,wparam,lparam);

	CStatic::OnRButtonUp(nFlags, point);
}

BaseCollection.h:

#pragma once
#include 
#include "BaseStatic.h"

// These two events are key to integrating our collection control with a program
// WM_ELEMCLICK = Left button click on an element
// WM_ELEMRIGHTCLICK = Right pop-up menu was selected for element
static int WM_ELEMCLICK = RegisterWindowMessage(_T("ELEMCLICK"));
static int WM_ELEMRIGHTCLICK = RegisterWindowMessage(_T("ELEMRIGHTCLICK"));

class CBaseCollection : public CStatic
{
	DECLARE_DYNAMIC(CBaseCollection)

public:
	CBaseCollection();
	virtual ~CBaseCollection();
	void SetNumVisibleElems(unsigned int numElems);
	CBaseStatic* AddElement(COLORREF col);
	int GetVisibleIndex();
	void SetVisibleIndex(int index);

protected:
	DECLARE_MESSAGE_MAP()
	afx_msg void OnElemClicked(UINT nID);
	afx_msg LRESULT OnElemRightClick(WPARAM wParam, LPARAM lParam);
	afx_msg void OnRightMenu();

private:
	typedef std::vector BASESET;

	BASESET m_bases;		// vector of CBaseStatic classes
	unsigned int m_visibleIndex;	// The index of the left-most, currently visible CBasicStatic panel in m_bases
	unsigned int m_visibleWidth;	// The width of each CBaseStatic panel, dynamically calculated
	unsigned int m_numVisibleElems;	// The number of elements to be displayed in the control at one time
	unsigned int m_selectedIndex;	// The currently selected index

	void DisplayBases();
	void SetHighlight();
};

BaseCollection.cpp:

#include "stdafx.h"
#include "DynamicWidget.h"
#include "BaseCollection.h"

// This is a range of control IDs reserved for the
// individual elements populating the collection.
#define BASE_MIN_CTRLID 5000
#define BASE_MAX_CTRLID 6000

IMPLEMENT_DYNAMIC(CBaseCollection, CStatic)

CBaseCollection::CBaseCollection()
{
	// Set our rendering variables
	// to defaults. These will all
	// be dynamically set as the 
	// user interacts with the control.
	m_visibleIndex = 0;
	m_visibleWidth = 200;
	m_selectedIndex = 0;
}

CBaseCollection::~CBaseCollection()
{
	// Cleanup all our CBaseStatic panels
	for (BASESET::iterator it = m_bases.begin(); it != m_bases.end(); it++)
	{
		delete *it;
	}
}

BEGIN_MESSAGE_MAP(CBaseCollection, CStatic)
	ON_CONTROL_RANGE(BN_CLICKED, BASE_MIN_CTRLID, BASE_MAX_CTRLID, OnElemClicked)
	ON_REGISTERED_MESSAGE(WM_BASERIGHTCLICK, OnElemRightClick)
	ON_COMMAND(ID_RIGHTMENU, OnRightMenu)
END_MESSAGE_MAP()

// Sets the number of elements visible at any one time in our collection control.
// This should be called by the main program. The elements will be equally sized
// across the width of the collection control.
void CBaseCollection::SetNumVisibleElems(unsigned int numElems)
{
	CRect clientRect;

	GetClientRect(clientRect);

	m_numVisibleElems = numElems;
	m_visibleWidth = clientRect.Width() / numElems;
}

// This appends one new element to the end of our control set.
// This function should be altered to work with more than simply a color.
CBaseStatic* CBaseCollection::AddElement(COLORREF col)
{
	CBaseStatic *newBase;
	CRect clientRect;

	GetClientRect(clientRect);

	// Each new element will start scaled to the size of our collection widget.
	// DisplayBases() will resize elements based on the number of visible elements.
	// Each control gets a unique CTRLID for event handling.
	// Notice the SS_NOTIFY attribute. This is needed to fire the messages
	// indicating mouse clicks.
	newBase = new CBaseStatic(col);
	newBase->Create(NULL, WS_CHILD | SS_NOTIFY, clientRect, this, BASE_MIN_CTRLID + m_bases.size());
	m_bases.push_back(newBase);

	DisplayBases();

	return newBase;
}

// Returns the left-most visible element
int CBaseCollection::GetVisibleIndex()
{
	return m_visibleIndex;
}

// Sets the left-most visible element
void CBaseCollection::SetVisibleIndex(int index)
{
	if (index = (int)(m_bases.size()-m_numVisibleElems))
	{
		m_visibleIndex = m_bases.size() - m_numVisibleElems;
	}
	else
	{
		m_visibleIndex = (unsigned int)index;
	}

	DisplayBases();
}

// Renders all the elements
void CBaseCollection::DisplayBases()
{
	CRect clientRect, baseRect;

	GetClientRect(clientRect);

	// Loop through all the added elements
	unsigned int index = 0;
	for (BASESET::iterator it = m_bases.begin(); it != m_bases.end(); it++, index++)
	{
		// m_visibleElements indicates the first visible element.
		// If the current index is within the visible indexes, show it.
		// Otherwise, hide it.
		if (index >= m_visibleIndex && index MoveWindow(baseRect, FALSE);
			(*it)->ShowWindow(SW_SHOW);
		}
		else
		{
			(*it)->ShowWindow(SW_HIDE);
		}
	}

	Invalidate();
}

// This tells the selected element to highlight itself.
void CBaseCollection::SetHighlight()
{
	int index = 0;
	for (BASESET::iterator it = m_bases.begin(); it != m_bases.end(); it++, index++)
	{
		(*it)->SetHighlight(m_selectedIndex == index);
	}
}

// Left-click handler fired by an individual element.
// This function highlights the selected element and
// posts an event to the parent for the click event.
void CBaseCollection::OnElemClicked(UINT nID)
{
	int index = 0;
	for (BASESET::iterator it = m_bases.begin(); it != m_bases.end(); it++, index++)
	{
		if (nID == (*it)->GetDlgCtrlID())
		{
			m_selectedIndex = index;
			GetParent()->PostMessage(WM_ELEMCLICK, NULL, (LPARAM)m_selectedIndex);
		}
	}

	SetHighlight();
	Invalidate();
}

// Right-click handler fired by an individual element.
// This function fires a left-click event and presents a pop-up menu
LRESULT CBaseCollection::OnElemRightClick(WPARAM wParam, LPARAM lParam)
{
	CMenu popup;
	CPoint *point = (CPoint*)wParam;
	int ctrlID = lParam;

	// we just selected an element, so fire the click event
	// this may be undesirable in some applications
	OnElemClicked(ctrlID);

	// display a pop-up menu
	popup.CreatePopupMenu();
	popup.AppendMenu(MF_STRING, ID_RIGHTMENU, _T("Click Me!"));
	popup.TrackPopupMenu(TPM_LEFTALIGN | TPM_RIGHTBUTTON, point->x, point->y, this);

	return TRUE;
}

// Pop-up menu click handler. Passes an event to the parent.
void CBaseCollection::OnRightMenu()
{
	GetParent()->PostMessage(WM_ELEMRIGHTCLICK, NULL, (LPARAM)m_selectedIndex);
}

DynamicWidgetDlg.h:

#pragma once
#include "BaseCollection.h"

class CDynamicWidgetDlg : public CDialogEx
{
public:
	CDynamicWidgetDlg(CWnd* pParent = NULL);

#ifdef AFX_DESIGN_TIME
	enum { IDD = IDD_DYNAMICWIDGET_DIALOG };
#endif

protected:
	DECLARE_MESSAGE_MAP()
	virtual void DoDataExchange(CDataExchange* pDX);
	virtual BOOL OnInitDialog();
	afx_msg LRESULT OnCollectionClick(WPARAM wParam, LPARAM lParam);
	afx_msg LRESULT OnCollectionRightClick(WPARAM wParam, LPARAM lParam);
	afx_msg void OnClickedLeft();
	afx_msg void OnClickedRight();

private:
	CBaseCollection m_baseCollection;	// Our new control
	CEdit m_text;				// Used to indicate to the user that an event occurred
	CButton m_left, m_right;		// Buttons to allow scrolling left and right in the CBaseCollection control
};

DynamicWidgetDlg.cpp:

#include "stdafx.h"
#include "DynamicWidget.h"
#include "DynamicWidgetDlg.h"
#include "afxdialogex.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif

CDynamicWidgetDlg::CDynamicWidgetDlg(CWnd* pParent)
	: CDialogEx(IDD_DYNAMICWIDGET_DIALOG, pParent), m_baseCollection()
{
}

void CDynamicWidgetDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialogEx::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_IMG, m_baseCollection);
	DDX_Control(pDX, IDC_OUTTEXT, m_text);
	DDX_Control(pDX, IDC_LEFT, m_left);
	DDX_Control(pDX, IDC_RIGHT, m_right);
}

BEGIN_MESSAGE_MAP(CDynamicWidgetDlg, CDialogEx)
	ON_REGISTERED_MESSAGE(WM_ELEMCLICK, OnCollectionClick)
	ON_REGISTERED_MESSAGE(WM_ELEMRIGHTCLICK, OnCollectionRightClick)
	ON_BN_CLICKED(IDC_LEFT, OnClickedLeft)
	ON_BN_CLICKED(IDC_RIGHT, OnClickedRight)
END_MESSAGE_MAP()

BOOL CDynamicWidgetDlg::OnInitDialog()
{
	CDialogEx::OnInitDialog();

	m_text.SetReadOnly(TRUE);

	// set up our collection widget, only 3 elements are visible at a time
	m_baseCollection.SetNumVisibleElems(3);
	
	// loop through 5 elements and add them, making each a random color
	// you'll probably want to do something more interesting than this
	for (int i = 0; i < 5; i++)
	{
		m_baseCollection.AddElement(RGB(rand() % 255, rand() % 255, rand() % 255));
	}

	return TRUE;
}

// Program-level button click handler fired by clicking on a single element
LRESULT CDynamicWidgetDlg::OnCollectionClick(WPARAM wParam, LPARAM lParam)
{
	int selectedIndex = (int)lParam;

	wchar_t sz[500];
	wsprintfW(sz, _T("Dialog captured left click for index %d!"), selectedIndex);
	m_text.SetWindowTextW(sz);

	return TRUE;
}

// Program-level right-click handler fired by clicking on a single element
LRESULT CDynamicWidgetDlg::OnCollectionRightClick(WPARAM wParam, LPARAM lParam)
{
	int selectedIndex = (int)lParam;

	wchar_t sz[500];
	wsprintfW(sz, _T("Dialog captured pop-up menu click for index %d!"), selectedIndex);
	m_text.SetWindowTextW(sz);

	return TRUE;
}

// "Left" button handler
void CDynamicWidgetDlg::OnClickedLeft()
{
	m_baseCollection.SetVisibleIndex(m_baseCollection.GetVisibleIndex() - 1);
}

// "Right" button handler
void CDynamicWidgetDlg::OnClickedRight()
{
	m_baseCollection.SetVisibleIndex(m_baseCollection.GetVisibleIndex() + 1);
}

Finally, for reference purposes, here is the generated DynamicWidget.rc file:

// Microsoft Visual C++ generated resource script.
//
#include "resource.h"

#define APSTUDIO_READONLY_SYMBOLS
/////////////////////////////////////////////////////////////////////////////
//
// Generated from the TEXTINCLUDE 2 resource.
//
#ifndef APSTUDIO_INVOKED
#include "targetver.h"
#endif
#include "afxres.h"
#include "verrsrc.h"

/////////////////////////////////////////////////////////////////////////////
#undef APSTUDIO_READONLY_SYMBOLS

/////////////////////////////////////////////////////////////////////////////
// English (United States) resources

#if !defined(AFX_RESOURCE_DLL) || defined(AFX_TARG_ENU)
LANGUAGE LANG_ENGLISH, SUBLANG_ENGLISH_US

#ifdef APSTUDIO_INVOKED
/////////////////////////////////////////////////////////////////////////////
//
// TEXTINCLUDE
//

1 TEXTINCLUDE 
BEGIN
    "resource.h\0"
END

2 TEXTINCLUDE 
BEGIN
    "#ifndef APSTUDIO_INVOKED\r\n"
    "#include ""targetver.h""\r\n"
    "#endif\r\n"
    "#include ""afxres.h""\r\n"
    "#include ""verrsrc.h""\r\n"
    "\0"
END

3 TEXTINCLUDE 
BEGIN
    "#define _AFX_NO_SPLITTER_RESOURCES\r\n"
    "#define _AFX_NO_OLE_RESOURCES\r\n"
    "#define _AFX_NO_TRACKER_RESOURCES\r\n"
    "#define _AFX_NO_PROPERTY_RESOURCES\r\n"
    "\r\n"
    "#if !defined(AFX_RESOURCE_DLL) || defined(AFX_TARG_ENU)\r\n"
    "LANGUAGE 9, 1\r\n"
    "#include ""res\\DynamicWidget.rc2""  // non-Microsoft Visual C++ edited resources\r\n"
    "#include ""afxres.rc""      // Standard components\r\n"
    "#if !defined(_AFXDLL)\r\n"
    "#include ""afxribbon.rc""   // MFC ribbon and control bar resources\r\n"
    "#endif\r\n"
    "#endif\r\n"
    "\0"
END

#endif    // APSTUDIO_INVOKED


/////////////////////////////////////////////////////////////////////////////
//
// Icon
//

// Icon with lowest ID value placed first to ensure application icon
// remains consistent on all systems.
IDR_MAINFRAME           ICON                    "res\\DynamicWidget.ico"

/////////////////////////////////////////////////////////////////////////////
//
// Dialog
//

IDD_DYNAMICWIDGET_DIALOG DIALOGEX 0, 0, 453, 153
STYLE DS_SETFONT | DS_FIXEDSYS | WS_POPUP | WS_VISIBLE | WS_CAPTION | WS_THICKFRAME
EXSTYLE WS_EX_APPWINDOW
CAPTION "DynamicWidget"
FONT 8, "MS Shell Dlg", 0, 0, 0x1
BEGIN
    PUSHBUTTON      "Cancel",IDCANCEL,396,132,50,14
    CONTROL         "",IDC_IMG,"Static",SS_BLACKFRAME,19,18,414,87
    EDITTEXT        IDC_OUTTEXT,19,115,208,14,ES_AUTOHSCROLL
    PUSHBUTTON      "Left",IDC_LEFT,234,115,50,14
    PUSHBUTTON      "Right",IDC_RIGHT,287,115,50,14
END


/////////////////////////////////////////////////////////////////////////////
//
// Version
//

VS_VERSION_INFO VERSIONINFO
 FILEVERSION 1,0,0,1
 PRODUCTVERSION 1,0,0,1
 FILEFLAGSMASK 0x3fL
#ifdef _DEBUG
 FILEFLAGS 0x1L
#else
 FILEFLAGS 0x0L
#endif
 FILEOS 0x40004L
 FILETYPE 0x1L
 FILESUBTYPE 0x0L
BEGIN
    BLOCK "StringFileInfo"
    BEGIN
        BLOCK "040904B0"
        BEGIN
            VALUE "CompanyName", "TODO: "
            VALUE "FileDescription", "DynamicWidget"
            VALUE "FileVersion", "1.0.0.1"
            VALUE "InternalName", "DynamicWidget.exe"
            VALUE "LegalCopyright", "TODO: (c) .  All rights reserved."
            VALUE "OriginalFilename", "DynamicWidget.exe"
            VALUE "ProductName", "TODO: "
            VALUE "ProductVersion", "1.0.0.1"
        END
    END
    BLOCK "VarFileInfo"
    BEGIN
        VALUE "Translation", 0x409, 1200
    END
END


/////////////////////////////////////////////////////////////////////////////
//
// DESIGNINFO
//

#ifdef APSTUDIO_INVOKED
GUIDELINES DESIGNINFO
BEGIN
    IDD_DYNAMICWIDGET_DIALOG, DIALOG
    BEGIN
        LEFTMARGIN, 7
        RIGHTMARGIN, 446
        TOPMARGIN, 7
        BOTTOMMARGIN, 146
    END
END
#endif    // APSTUDIO_INVOKED


/////////////////////////////////////////////////////////////////////////////
//
// AFX_DIALOG_LAYOUT
//

IDD_DYNAMICWIDGET_DIALOG AFX_DIALOG_LAYOUT
BEGIN
    0
END

#endif    // English (United States) resources
/////////////////////////////////////////////////////////////////////////////



#ifndef APSTUDIO_INVOKED
/////////////////////////////////////////////////////////////////////////////
//
// Generated from the TEXTINCLUDE 3 resource.
//
#define _AFX_NO_SPLITTER_RESOURCES
#define _AFX_NO_OLE_RESOURCES
#define _AFX_NO_TRACKER_RESOURCES
#define _AFX_NO_PROPERTY_RESOURCES

#if !defined(AFX_RESOURCE_DLL) || defined(AFX_TARG_ENU)
LANGUAGE 9, 1
#include "res\DynamicWidget.rc2"  // non-Microsoft Visual C++ edited resources
#include "afxres.rc"      // Standard components
#if !defined(_AFXDLL)
#include "afxribbon.rc"   // MFC ribbon and control bar resources
#endif
#endif

/////////////////////////////////////////////////////////////////////////////
#endif    // not APSTUDIO_INVOKED

Oracle – Find Blocking Sessions

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

SQL> DESC test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VALUE                                              VARCHAR2(30)

SQL> UPDATE test SET value = 'VALUE 1';

1 row updated.

Notice the lack of commit. This keeps the transaction open.

Transaction 2

SQL> UPDATE test SET value = 'VALUE 2';

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:

SELECT
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocked.username blocked_username,
  s_blocked.osuser blocked_osuser,
  s_blocked.machine blocked_machine,
  SUBSTR( sql_blocked.sql_text, 1, 50 ) blocked_sql,
  s_blocking.username blocking_username,
  s_blocking.osuser blocking_osuser,
  s_blocking.machine blocking_machine,
  SUBSTR( sql_blocking.sql_text, 1, 50 ) blocking_sql
FROM v$session s_blocked, v$session s_blocking, v$sql sql_blocked, v$sql sql_blocking
WHERE s_blocked.blocking_session = s_blocking.sid
  AND s_blocked.sql_id = sql_blocked.sql_id(+)
  AND s_blocking.sql_id = sql_blocking.sql_id(+)
  AND s_blocked.blocking_session IS NOT NULL;
SELECT 
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocked.username blocked_username,
  s_blocked.osuser blocked_osuser,
  s_blocked.machine blocked_machine,
  SUBSTR( sql_blocked.sql_text, 1, 50 ) blocked_sql,
  s_blocking.username blocking_username,
  s_blocking.osuser blocking_osuser,
  s_blocking.machine blocking_machine,
  SUBSTR( sql_blocking.sql_text, 1, 50 ) blocking_sql
FROM v$lock l_blocking, v$lock l_blocked, v$session s_blocking, v$session s_blocked, v$sql sql_blocking, v$sql sql_blocked
WHERE l_blocking.sid = s_blocking.sid
  AND l_blocked.sid = s_blocked.sid
  AND s_blocking.sql_id = sql_blocking.sql_id(+)
  AND s_blocked.sql_id = sql_blocked.sql_id(+)
  AND l_blocking.block = 1 
  AND l_blocked.request > 0
  AND l_blocking.id1 = l_blocked.id1
  AND l_blocking.id2 = l_blocked.id2;

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.

SELECT
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocking.username blocking_username,
  kl.kglnaobj blocking_objectname,
  CASE kl.kglobtyp
    WHEN 1 THEN 'index'
    WHEN 2 THEN 'table'
    WHEN 3 THEN 'cluster'
    WHEN 4 THEN 'view'
    WHEN 5 THEN 'synonym'
    WHEN 6 THEN 'sequence'
    WHEN 7 THEN 'procedure'
    WHEN 8 THEN 'function'
    WHEN 9 THEN 'package'
    WHEN 11 THEN 'package body'
    WHEN 12 THEN 'trigger'
    ELSE 'others'
  END blocking_objtype,
  s_blocking.program blocking_program,
  sql_blocking.sql_text blocking_sql
FROM x$kglpn kpin, v$session s_blocking, x$kglob kl, v$session_wait seswait, v$sql sql_blocking
WHERE kpin.kglpnuse = s_blocking.saddr
  AND kpin.kglpnhdl = kl.kglhdadr
  AND kl.kglhdadr = seswait.p1raw
  AND seswait.event = 'library cache pin'
  AND s_blocking.sql_id = sql_blocking.sql_id;

Oracle – Update Multiple Columns with a Single Subquery

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:

SQL> DESC test1_tab;
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 VAL1                                     VARCHAR2(20)
 VAL2                                     VARCHAR2(20)
 VAL3                                     VARCHAR2(20)

SQL> SELECT *
  2  FROM test1_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    VAL1_A               VAL2_A
B                    VAL1_B               VAL2_B
C                    VAL1_C               VAL2_C


SQL> DESC test2_tab;
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 VAL1                                     VARCHAR2(20)
 VAL2                                     VARCHAR2(20)
 VAL3                                     VARCHAR2(20)

SQL> SELECT *
  2  FROM test2_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    TEST2_VAL1_A         TEST2_VAL2_A
B                    TEST2_VAL1_B         TEST2_VAL2_B
C                    TEST2_VAL1_C         TEST2_VAL2_C


SQL> UPDATE test1_tab t1
  2  SET ( val2, val3 ) =
  3    ( SELECT val2, val3 FROM test2_tab t2
  4      WHERE t2.val1 = t1.val1 )
  5  WHERE t1.val1 = 'B';

1 row updated.


SQL> SELECT *
  2  FROM test1_tab;

VAL1                 VAL2                 VAL3
-------------------- -------------------- --------------------
A                    VAL1_A               VAL2_A
B                    TEST2_VAL1_B         TEST2_VAL2_B
C                    VAL1_C               VAL2_C

ORACLE – Using DENSE_RANK and KEEP to simplify multi-layer analytic queries

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.

SQL> DESC unit_price
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_NAME                                       VARCHAR2(50)
 PRICE_DATE                                         DATE
 PRICE                                              NUMBER


SQL> SELECT *
  2  FROM unit_price
  3  ORDER BY product_name, price_date;

PRODUCT_NAME                                       PRICE_DAT      PRICE
-------------------------------------------------- --------- ----------
SCREWDRIVER                                        21-DEC-13       6.25
SCREWDRIVER                                        22-DEC-13       6.27
SCREWDRIVER                                        23-DEC-13        6.4
SHOVEL                                             21-DEC-13      10.48
SHOVEL                                             22-DEC-13      10.49
SHOVEL                                             23-DEC-13       10.6

6 rows selected.


SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT TO_CHAR( price )
 13      INTO latest_price
 14      FROM
 15      (
 16        SELECT
 17          price,
 18          ROW_NUMBER() OVER ( ORDER BY price_date DESC ) rn
 19        FROM unit_price
 20        WHERE product_name = prodname
 21      )
 22      WHERE rn = 1;
 23
 24      RETURN latest_price;
 25
 26    EXCEPTION
 27      WHEN no_data_found THEN
 28        RETURN 'NO PRICE FOUND';
 29    END;
 30
 31  BEGIN
 32
 33    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 34    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 35
 36    wrench_price := get_latest_price( 'WRENCH' );
 37    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 38
 39  END;
 40  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

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.

SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT NVL( TO_CHAR( MAX( price ) KEEP ( DENSE_RANK FIRST ORDER BY price_date DESC ) ), 'NO PRICE FOUND' )
 13      INTO latest_price
 14      FROM unit_price
 15      WHERE product_name = prodname;
 16
 17      RETURN latest_price;
 18
 19    END;
 20
 21  BEGIN
 22
 23    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 24    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 25
 26    wrench_price := get_latest_price( 'WRENCH' );
 27    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 28
 29  END;
 30  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

Optimized approach to addressing LISTAGG() and the ORA-01489 error

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.

SQL> CREATE OR REPLACE
  2  TYPE VarCharAggregator
  3  AS OBJECT
  4  (
  5    separator VARCHAR2( 20 ),     -- Delimeter for the fields
  6    separator_length NUMBER,      -- Length of the delimeter
  7    buffer VARCHAR2( 4000 ),      -- Used to aggregate strings until the maximum VARCHAR2 length is exceeded
  8    buffer_length NUMBER,         -- Current length of the data in buffer
  9    aggregate_clob CLOB,          -- The aggregated CLOB value into which the buffer is moved as it exceeds the VARCHAR2 length
 10    has_clob_data VARCHAR2( 1 ),  -- Flag to indicate whether any data has been moved to the aggregate_clob
 11
 12    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
 13      RETURN NUMBER,
 14    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 15      RETURN NUMBER,
 16    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number )
 17      RETURN NUMBER,
 18    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 19      RETURN NUMBER
 20  );
 21  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY VarCharAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
  6    RETURN NUMBER
  7    IS
  8    BEGIN
  9
 10      sctx := VarCharAggregator( ',', LENGTH( ',' ), '', 0, NULL, 'N' );
 11      DBMS_LOB.CREATETEMPORARY( sctx.aggregate_clob, TRUE, DBMS_LOB.CALL );
 12
 13      RETURN ODCIConst.Success;
 14
 15    END;
 16
 17    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 18    RETURN NUMBER
 19    IS
 20      value_length NUMBER;
 21    BEGIN
 22
 23      value_length := LENGTH( value );
 24
 25      -- Move "buffer" into aggregate_clob once appending "value" to "buffer" will exceed the maximum VARCHAR2 length
 26      IF ( self.buffer_length > 0 ) AND ( value_length + self.buffer_length > ( 4000 - self.separator_length ) ) THEN
 27
 28        IF self.has_clob_data = 'Y' THEN
 29          DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.separator ) );
 30        END IF;
 31
 32        DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.buffer ) );
 33        self.has_clob_data := 'Y';
 34        self.buffer := '';
 35        self.buffer_length := 0;
 36
 37      END IF;
 38
 39      -- Insert a separator value if we already have data in the buffer
 40      IF self.buffer_length > 0 THEN
 41        self.buffer := self.buffer || self.separator;
 42      END IF;
 43
 44      -- Append "value" to our buffer
 45      self.buffer := self.buffer || value;
 46      self.buffer_length := self.buffer_length + value_length;
 47
 48      RETURN ODCIConst.Success;
 49
 50    END;
 51
 52    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number)
 53    RETURN NUMBER
 54    IS
 55    BEGIN
 56
 57      returnValue := self.aggregate_clob;
 58
 59      IF LENGTH( self.buffer ) > 0 THEN
 60
 61        IF self.has_clob_data = 'Y' THEN
 62          DBMS_LOB.APPEND( returnValue, self.separator );
 63        END IF;
 64
 65        DBMS_LOB.APPEND( returnValue, self.buffer );
 66
 67      END IF;
 68
 69      RETURN ODCIConst.Success;
 70
 71    END;
 72
 73    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 74    RETURN NUMBER
 75    IS
 76    BEGIN
 77
 78      DBMS_LOB.APPEND( self.aggregate_clob, self.separator );
 79      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 80
 81      RETURN ODCIConst.Success;
 82
 83    END;
 84
 85  END;
 86  /

Type body created.


SQL> CREATE OR REPLACE
  2  FUNCTION VARCHARAGG( input VARCHAR2 )
  3  RETURN CLOB
  4  PARALLEL_ENABLE AGGREGATE
  5  USING VarCharAggregator;
  6  /

Function created.

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:

SQL> SELECT VARCHARAGG( value )
  2  FROM test_strings;

VARCHARAGG(VALUE)
---------------------------------
test1,test2,test3