Disabling Constraints with a Stored Procedure in Oracle

Sometimes you need to disable constraints on a Oracle Database. Why might this be? Well image the situation that you are exporting data into an intermediate schema, you only want to import data from a certain date range and due to this you have only a subset of the records. You need this subset for analysis but you don’t care about referential integrity – in fact if it is on then constraints will be violated. How can we do this?

Here’s a stored procedure that disables constraints for tables owned by ‘UserName1′ or ‘UserName2′:

CREATE OR REPLACE PROCEDURE extraction.sp_PrepExtractionDatabase 
    v_Statement VARCHAR(5000);
    FOR const in (CURSOR c_Constraints IS
      SELECT constraint_name, table_name, owner
      WHERE owner IN ('UserName1', 'UserName2')) LOOP
      v_Statement := 'ALTER TABLE ' || const.owner 
|| '.' || const.table_name || ' DISABLE CONSTRAINT '
|| const.constraint_name;
      EXECUTE IMMEDIATE v_Statement;

What’s the key thing here? ‘AUTHID CURRENT_USER’. Without this, running the query itself will work fine, but the stored procedure will find NOTHING in the ALL_CONSTRAINTS view. Run in the context of the current user and then the stored procedure will work fine.


SharpGL 2.0

SharpGL 2.0 has been released – hit the codeplex site to get it: http://sharpgl.codeplex.com/

Some new features:

  • Full support for all OpenGL functions up to OpenGL 4.2
  • Full support for all commonly used OpenGL extensions
  • Support for WinForms applications
  • Support for WPF applications (without resorting to WinForms hosts)
  • A powerful scene graph including polygons, shaders, NURBs and more
  • Many sample applications as starting points for your own projects.
  • Visual Studio Extension with SharpGL project templates for WPF and WinForms.
And a few screenshots:
 The Radial Blur Sample

The New Project Types

WPF Support

Text Rendering


Debugger:: An unhandled non-continuable exception was thrown during process load

The following exception can be a very tricky one to deal with:

Debugger:: An unhandled non-continuable exception was thrown during process load

here’s some tips if you get it.

  1. Are you linking to winmm.lib? If so avoid it – it can cause these problems.
  2. Are you delay-loading the module? If not, try it – this can often resolve this issue if other modules like winmm.lib are interfering with the module that causes this exception.
  3. Are you using C++/CLI for the excepting module? If so, try using #pragma pack around exported class definitions.
If you haven’t specified packing – do so. This is good practice anyway. I’ve used libraries that change the packing (which is very bad behaviour) before and this has caused all sorts of problems, so try and do the following:
// Push packing options, specify the packing.
#pragma pack(push, 1)

//	Exported class
class MY_API MyClass

	//	...etc
// Restore packing options.
#pragma pack(pop)


I have written the second article in my series on Extending Visual Studio. In this article I describe how to create a Visual Studio Addin that allows you to switch between cpp/h files, WinForms code and designer, XAML and codebehind and so on. You can find the article on the CodeProject here:


There is also a direct download page on this blog, you can get Switch from dwmkerr.com by going here: