Tuesday, May 18, 2010

ASP.NET: How to prevent Duplicate Record Insertion on Page Refresh


This is a common problem in any web based application. Certainly, many developers might have banged their heads to solve "How do I prevent previously submitted form data from being reinserted into the database when the user presses the browser's Refresh/F5 buttons?"

The purpose of this article is to prevent the data from being reinserted into the database in a simple way when the browser's Refresh button is pressed.

Due to the nature of web pages and posted data, the form values are still held by the browser. A refresh of the page will post the data again and the data will wind up in your database an additional time.
This has nothing to do with ASP.NET in particular; it is a built-in browser behavior. The same thing can happen with PHP, ASP, and any other type of web page.

On goggling, perhaps you will get many suggestions to prevent this:
LiKE:
  1. Clear the caching of the page after the Action
  2. Disable the IE Refresh and F5 buttons.Etc… many more …..
Above solutions are purely depends on your requirement and involved a coding overhead.

Here is simplest and great solution that worked for me like a gem.
Add the following piece of code after your Database action.

Response.Redirect(Request.Url.ToString(), False)


Above technique simply redirect your page to the same page which could technically prevent the re-posting the previous action on Refresh.

Using Request.Url.ToString() as the first parameter of Response.Redirect will cause both the URL and the page's Querystring to be included in the redirect. The use of false as the second parameter will suppress the automatic Response.End that may otherwise generate a ThreadAbortedException.

NOTE: The only disadvantage of this approach is that any ViewState that had been built up will be lost.



Wednesday, May 12, 2010

JavaScript: Implementing OOPS in JavaScript

Implementing Object-oriented methodology in JavaScript:

I have just found this wonderful article on the NET….Just sharing with you…very useful.

Packaging: javascript to create the object model, individuals think through the closure to be on the up are the true meaning of the package, so first of all, our first a brief closure, watch the following examples:


<script type="text/javascript">

function myInfo(){

var name ="Old fish ",age =27;

var myInfo = "my name is" + name + "i am" + age +"years old";

function showInfo(){

alert(myInfo);

}

return showInfo;

}

var oldFish = myInfo();

oldFish();

</script>


So are not very familiar? Yes, it is actually a simple application of the closure. Easy to explain: myInfo function above defined variables, embedded in its function showInfo is accessible (this a very good understanding), but when we put the embedded function return value to a variable reference oldFish, this time function showInfo at myInfo function in vitro are called, but can access to the same definition of variables in the function body. oh yeah!

Summarize the principle of closure: the function is running at the definition of their role in the domain instead of calling them the role of the domain. In fact, an embedded function back closure is the most commonly used to create a way!

If you feel that too abstract to explain the above, then join us in reshaping the above function, take a look at this a number of distinct levels:


<script type="text/javascript">

var ioldFish = function(name,age){

var name = name,age = age;

var myInfo = "my name is" + name + "i am" + age +"years old";

return{

showInfo:function(){

alert(myInfo);

}

}

}

ioldFish("Old fish ",27).showInfo();

</script>


Example on the ext yui coding style is the more commonPublic-private distinction at a glance . Through the closure, we can easily put some do not wish to be the external direct access to the hidden things, you want to visit the definition of function variables, only through a particular method can only be a visit to that directly from the external visit is to visit not, writeVery tired , Rao has finally come to a circle, and package them, do not put that do not wish to be like other people to see things hidden them! ... Ha ha ...

Example, if converted on JQ's style, he should have written the following example, this model belong to the package open door model, which are defined variable can be external access to the (the following example, if you first instantiate an object , and then access objects in the external function name or age of the property can be read to) this mode of course, we can set up some "unspoken rules" so that team members understand what variables are private, often our man-made At the private variables and methods before the underscore "_", logo warning signals! In order to achieve "package"!


<script type="text/javascript">

var ioldFish = function(name,age){

return ioldFish.func.init(name,age);

};

ioldFish.func = ioldFish.prototype ={

init:function(name,age){

this.name = name;

this.age = age;

return this;

},

showInfo:function(){

var info = "my name is" + this.name +"i am " +this.age+"years old";

alert(info);

}

};

ioldFish.func.init.prototype = ioldFish.func;

ioldFish(" Old fish ",27).showInfo();

//var oldFish = new ioldFish("Old fish ",27);

//alert(oldFish.name);

</script>


Some people might ask, what model is good? This how I say that? Two methods have advantages and disadvantages, combined with the use of chant! All in all a matter of principle, we must be an external object can not directly access anything on the package it with closure. "Must must" words are profound, and constantly practice in order to understand the true meaning!

Succession: the mention of the time, the way want to add one more point: closure of a disadvantage package is not conducive to sub-class of derivatives, so there is the risk of closure, packages need to be cautious! Visual clarity, the following examples of ways to create objects using "open door-type" model.

At javascript in succession are generally divided into three ways: "category-type succession," "prototype inheritance", "element-type doped." Following is a brief introduction about the principle of three types of inheritance.

A. Class-style inherit: This is now commonly used in the mainstream framework of inheritance, facie Example:


<script type="text/javascript">

var Name = function(name){

this.name = name;

};

Name.prototype.getName = function(){

alert(this.name);

};

var Fish = function(name,age){

Name.call(this,name);

this.age = age;

};

Fish.prototype = new Name();

Fish.prototype.constructor = Fish;

Fish.prototype.showInfo = function(){

alert(this.age);

}

var ioldFish = new Fish("Old fish ",27);

ioldFish.getName();

</script>


Fish in the above-mentioned sub-class no getName method definition, but examples of Fish subclass object ioldFish still call to this method, This is because sub-class inherits Fish Superclass Name as defined in the getName method. Explain here the prototype subclass Fish refers to the superclass of an instance in the subclass Medium Fish did not affirm the getName method, but in accordance with the principle of the prototype chain will point to the prototype object of go on a search whether there is the method If not find the method, will always search for the initial prototype of the object. In fact, this is the principle of succession. Here in particular to explain, Fish.prototype.constructor = Fish; this, because of the default sub-class of the prototype should be a point to itself, but before the prototype point to put the super-class instance object, so here it is set to come back. Of course you can put related code through a function to organize themselves to play the role of camouflage extend

B. Prototype inheritance, speaking from memory performance is better than category-style inheritance.


<script type="text/javascript">

function clone(object){

var F = function(){};

F.prototype = object;

return new F();

};

var Name = {

name:"who's name",

showInfo:function(){

alert(this.name);

}

};

var Fish = clone(Name);

//Fish.name = "Old fish ";

Fish.showInfo();

</script>


Obviously, the prototype of the succession of the core is the clone function, are the prototype chain of the same principle, different clones are directly super-category, then sub-category on the super-class inherits all the attributes and methods. In particular, look, this kind of inheritance and Do not need to create constructor, only the words necessary to create an object variable, the definition of the appropriate properties and methods, and then in the sub-class only through the necessary dots "." symbols to invoke properties and methods of it.

C. Yuan doped categories: put some more commonly used generic methods function in a unified package, and then through the following functions assigned to use these methods to the class. Can also be for different types of selective transmission needs.


<script type="text/javascript">

function agument(receveClass,giveClass){

if(arguments[2]){

var len = arguments.length;

for(i=2;i<len;i++){

receveClass.prototype[arguments[i]] = giveClass.prototype[arguments[i]];

}

}

else{

for(method in giveClass.prototype){

if(!receveClass.prototype[method]){

receveClass.prototype[method] = giveClass.prototype[method];

}

}

}

};

var Name = function(){};

Name.prototype ={

sayLike:function(){

alert("i like oldfish");

},

sayLove:function(){

alert("i love oldfish");

}

}

var Fish = function(){};

var ioldFish = new Fish();

agument(Fish,Name,"sayLove");

ioldFish.sayLove();

ioldFish.sayLike();

</script>


Polymorphism: personally feel that the somewhat abstract, it is difficult to explain in words, so following on from the heavy load and coverage both in terms of easy to elaborate.

Overloading: agument this example above, the initial function with two parameters, but behind the call, agument (Fish, Name, "sayLove") into the same could be any number of parameters, javascript overloaded, are at function through the operation by the user's own arguments to the implementation of the property.

Coverage: This very simple, it is defined in sub-class methods with the class from the super-inherited methods of the same name, on the coverage of this method (in this case is not covered by the method of super class, pay attention to you), here is not a cumbersome !

Finally the focus of reference, let me talk about this and the implementation of the context, in front of the package to cite examples, this is that this type of instance where the object itself, but not uniform, an analogy, the definition of property through HTML Event handle code, see the following code:


<script type="text/javascript">

var Name = function(name) {

this.name = name;

this.getName = function () {

alert(this.name);

}

};

var ioldFish = new Name("Old fish "),

btn = document.getElementById('btn');

btn.onclick = ioldFish.getName;

//btn.onclick = function(){ioldFish.getName.call(ioldFish)};

</script>


Example on the mid-point of the button after the pop-up box does not show examples of object property, which is the implementation of this because the context has changed, he is now where the context should be the input of the HTML tags, but the tag does not exist getName This property, therefore can not output the property values of the property! From this example we can see that: the implementation of context are only identified in the implementation, it can be changed at any time.

Of course you can remove the top of my comment out of that code, call to change this through the implementation of the context, in order to gain the getName method. Ways same can apply to change the implementation of the context of implementation of the function, but in the prototype found in the framework of a more elegant way to achieve bind. Look at the implementation of this method you, great ancestors had to sigh ... ...


Function.prototype.bind = function(obj) {

var method = this,

temp = function() {

return method.apply(obj, arguments);

};

}


 

Monday, May 10, 2010

Oracle: Generate a CSV output of your SQL Query


Recently I have been asked to create a function that could generate a comma separated (CSV) output of a given SQL query. Here I am sharing the source code of that function with a hope that it could be useful to someone for sure. In order to achieve this I am using DBMS_SQL packages.
Dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime. Additionally, DBMS_SQL enables you to parse any data manipulation language (DML) or data definition language (DDL) statement. Therefore, you can parse DDL statements directly using PL/SQL.

NOTE: Before you start using DBMS_SQL, you need to make sure that it is installed and that the appropriate users have access to this package.

For more information on DBMS_SQL package: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql.htm

FUNCTION generate_csv( par_query CLOB ,
                       par_include_header NUMBER DEFAULT 1

                     ) RETURN CLOB

IS

l_cursor PLS_INTEGER;
_rows PLS_INTEGER;
l_col_cnt PLS_INTEGER;

l_desc_tab DBMS_SQL.desc_tab;

l_buffer CLOB;

l_row_data CLOB ;


BEGIN

l_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(l_cursor, par_query , DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);


FOR i IN 1 .. l_col_cnt LOOP

    DBMS_SQL
.define_column(l_cursor, i, l_buffer);

END LOOP;


l_rows := DBMS_SQL.execute(l_cursor);
-- Adding column header.

IF par_include_header = 1 THEN   FOR i IN 1 .. l_col_cnt LOOP
   l_buffer := l_desc_tab(i).col_name ;
   l_row_data := l_row_data || l_buffer ;

   IF i <> l_col_cnt THEN 
      row_data := l_row_data || ',';
   END IF ;

  END LOOP;
l_row_data := l_row_data || CHR(13) ; --Add carriage return
END IF;


-- Appending data.LOOPEXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;
FOR i IN 1 .. l_col_cnt LOOP --Loop through all columns
  DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
  l_row_data := l_row_data || l_buffer ;
  IF i <> l_col_cnt THEN
    l_row_data := l_row_data || ',';
  END IF ;
END LOOP;


l_row_data := l_row_data || CHR(13) ; --Add carriage return

END LOOP;
RETURN l_row_data;

EXCEPTION
WHEN OTHERS THEN

IF DBMS_SQL.is_open(l_cursor) THEN
  DBMS_SQL
.close_cursor(l_cursor);

END IF;


RETURN 'Error :' || SQLERRM || CHR(13) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;

END generate_csv ;

Example:
SELECT generate_csv('SELECT object_id,object_name,object_type FROM user_objects WHERE ROWNUM <=20') data FROM DUAL;

Result :

OBJECT_ID,OBJECT_NAME,OBJECT_TYPE
74516,AA_CU_VW,VIEW
74517,AA_LC_VW,VIEW
89245,AA_PO,VIEW
89246,AA_PO1,VIEW
74336,ACTIVITY_ROLLFORWARD_SUMMARY,VIEW
73833,APPSCREENS,TABLE
73834,APPSCREENSSCREENNAME,INDEX
73835,APPUSERS,TABLE
73836,APPUSERSUSERNAME,INDEX
197790,BANK,TABLE
197791,BANKBANKNUM,INDEX
202243,BENEY,TABLE
202244,BENEYBENEYACCT,INDEX
195188,BIN$f9BuwtUHsjDgQ6wQEUKyMA==$0,INDEX
195189,BIN$f9BuwtUIsjDgQ6wQEUKyMA==$0,INDEX
195190,BIN$f9BuwtUJsjDgQ6wQEUKyMA==$0,INDEX
195191,BIN$f9BuwtUKsjDgQ6wQEUKyMA==$0,INDEX
195192,BIN$f9BuwtULsjDgQ6wQEUKyMA==$0,INDEX
195193,BIN$f9BuwtUMsjDgQ6wQEUKyMA==$0,INDEX
195194,BIN$f9BuwtUNsjDgQ6wQEUKyMA==$0,INDEX





Thursday, May 6, 2010

JavaScript: Generic functions



function toggle(control)
{
    var    element = document.getElementById(control);
    if (element.style.display == "none" )
        element.style.display = "";
    else
        element.style.display = "none";
    };
    


function setdisplay(control, display)
{

var    element1 = document.getElementById(control);

alert(element1.name);

if (display == "true" )

{
     element1.style.display = "";
     }

else

{
     element1.style.display = "none";
     }


    };
    
function uppercase()
{
key = window.event.keyCode;

if (key == 13)


return
false; //Ignore the enter key




if ((key > 0x60) && (key < 0x7B))

window.event.keyCode = key-0x20;


};




function DisableEnterKey()
{
key = window.event.keyCode;



if (key == 13)


event.returnValue = false; //Ignore the enter key

};




function uppercase1()
{
key = window.event.keyCode;

if ((key > 0x60) && (key < 0x7B))

window.event.keyCode = key-0x20;
}


function setdisplaycheck(control1, chkbox) {
    var    element1 = document.getElementById(control1);
    var    chkbox = document.getElementById(chkbox);
alert(element1.id);
     if (chkbox.checked == "true" )
     {
         element1.style.display = "";
         }
     else
     {
         element1.style.display = "none";
         }


    };
    
    
function setvisibility(control, visiblity)
{
    var    element = document.getElementById(control);
    element.visible == visiblity;
    };
        
function setvisibilitytoggle(control) {
    var    element = document.getElementById(control);
    if (element.visible == "true" )
        element.visible = "false";
    else
        element.visible = "true";
    };
    
/*
<input name="number" onKeyPress="return numbersonly(event, false)"/>
*/    
function numbersonly(e, AcceptDecimals) {
var key;
var keychar;


if (window.event)
{
key = window.event.keyCode;
}
else
if (e)

{
key = e.which;
}
else
{

return
true;

}
if (key == 13)

return
false; //Ignore the enter key



keychar = String.fromCharCode(key);
if (isNaN(keychar))

if ((AcceptDecimals == 'true') && (keychar == '.'))


return
true;


else


return
false;

else

return
true;

};


/*
<input type=text name=en onKeyup="isInteger(this.value)"> */
function IsInteger(s)
{

var i;

    s = s.toString();

for (i = 0; i < s.length; i++)

{

var c = s.charAt(i);


if (isNaN(c))

     {
        alert("Given value is not a number");
        return
false;

     }
}

return
true;

};




/*
<input type="text" onkeypress="return onlyNumbers();">
*/
function onlyNumbers(evt)
{

var e = event || evt; // for trans-browser compatibility


var charCode = e.which || e.keyCode;


if (charCode == 13)


return
false; //Ignore Enter Key


else
if (charCode > 31 && (charCode < 48 || charCode > 57))


return
false; //Ignore if it is not a number


else


return
true;

};




/*
Function for displaying a popup NON-Model window
*/
function openWindow(url,pLeft, pTop)
{
var _Left ;
var _Top ;
var _Height = '230';
var _Width = '250';



if (pLeft === undefined )

_Left = '300';

else

_Left = pLeft;



if (pTop === undefined )

_Top = '310';

else

_Top = pTop;


window.open(url, 'OpenerWindow', 'height=' + _Height + ',width=' + _Width + ',left=' + _Left + ',top=' + _Top + ',toolbar=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=no,modal=yes');
};


function SetWaitCursor()
{
document.body.style.cursor = 'wait';

return
true;

} ;



function SetDefaultCursor()

{
document.body.style.cursor = 'default';
} ;




function OpenMe()
    {
    window.moveTo(0,0);
    if (document.all)
        {
            top.window.resizeTo(screen.availWidth,screen.availHeight);
        }
    else
if (document.layers||document.getElementById)

        {
            if (top.window.outerHeight<screen.availHeight||top.window.outerWidth<screen.availWidth)
                {
                    top.window.outerHeight = screen.availHeight;
                    top.window.outerWidth = screen.availWidth;
                }
        }
    }




function IsEmpty(control,displayalert)
{
alert(control);
    var    element = document.getElementById(control);
    alert(element.value);
    if (element.value == null )
    {
     if(displayalert)
     {
         alert("Please select the date");

return
false;

}
}
    else
     return
true;

};


function isDate (value)
{

//return (!isNaN (new Date (value).getYear () ) ) ;


try

{

if (isNaN(new Date (value).getYear()))

{
alert("Please select the valid date");

return
false ;

}

else


return
true;

}

catch (ex)

{
alert("Please select the valid date - Error : " + ex.message );

return
false ;

}




}




function myIsDate(mystring)
{

var mystring, myresult ;


var mystring = new Date(mystring);

isNaN(mystring)? myresult=false : myresult=true ;

if (myresult == false )

{
alert("Please select the valid date");

return
false ;

}

else


return
true;

}


function ClearTextBoxs()
{

var loTextBox = document.forms[0].elements;


for(var i=0;i <= loTextBox.length ; i++ )

{ if(loTextBox[i].tagName =="input")
loTextBox[i].value = "";
}
}





function ConvertNullToEmptyString(strinput)

{

return (strinput == null ? "" : strinput);

}




function pad(number, length) {



var str = '' + number;


while (str.length < length) {

str = '0' + str;
}



return str;



}


function setSelectedIndex(s, v)
{

var bFound = false;


for ( var i = 0; i < s.options.length; i++ )

{

if ( s.options[i].value == v )

{
s.options[i].selected = true;
bFound = true;

return;

}
}



//If Item not found then set the selected index to 0


if (bFound == false)

s.options[0].selected = true;
}


function setSelectedIndexByText(s, v)
{

var bFound = false;


for ( var i = 0; i < s.options.length; i++ )

{

if ( s.options[i].text == v )

{
s.options[i].selected = true;
bFound = true;

return;

}
}



//If Item not found then set the selected index to 0


if (bFound == false)

s.options[0].selected = true;


}

Oracle: DML Error Logging in 10g


Reference: http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php

 By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. In the past, the only way around this problem was to process each row individually, preferably with a bulk operation using a FORALL loop with the SAVE EXCEPTIONS clause. In Oracle 10g Database Release 2, the DML error logging feature has been introduced to solve this problem. Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors. This article presents an overview of the DML error logging functionality, with examples of each type of DML statement.

Syntax
The syntax for the error logging clause is the same for INSERT, UPDATE, MERGE and DELETE statements.
LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]
The optional INTO clause allows you to specify the name of the error logging table. If you omit this clause, the the first 25 characters of the base table name are used along with the "ERR$_" prefix.

The
simple_expression is used to specify a tag that makes the errors easier to identify. This might be a string or any function whose result is converted to a string.

The
REJECT LIMIT is used to specify the maximum number of errors before the statement fails. The default value is 0 and the maximum values is the keyword UNLIMITED. For parallel DML operations, the reject limit is applied to each parallel server.

Restrictions
The DML error logging functionality is not invoked when:
  • Deferred constraints are violated.
  • Direct-path INSERT or MERGE operations raise unique constraint or index violations.
  • UPDATE or MERGE operations raise a unique constraint or index violation.
In addition, the tracking of errors in LONG, LOB and object types is not supported, although a table containing these columns can be the target of error logging.
Sample Schema
This following code creates and populates the tables necessary to run the example code in this article.
-- Create and populate a source table.
CREATE TABLE source (
id NUMBER(10) NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
CONSTRAINT source_pk PRIMARY KEY (id)
);

DECLARE
TYPE t_tab IS TABLE OF source%ROWTYPE;
l_tab t_tab := t_tab();
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description for ' || TO_CHAR(i);
END LOOP;

-- For a possible error condition.
l_tab(1000).code := NULL;
l_tab(10000).code := NULL;

FORALL i IN l_tab.first .. l_tab.last
INSERT INTO source VALUES l_tab(i);


COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);

-- Create a destination table.
CREATE TABLE dest (
id NUMBER(10) NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
CONSTRAINT dest_pk PRIMARY KEY (id)
);

-- Create a dependant of the destination table.
CREATE TABLE dest_child (
id NUMBER,
dest_id NUMBER,
CONSTRAINT child_pk PRIMARY KEY (id),
CONSTRAINT dest_child_dest_fk FOREIGN KEY (dest_id) REFERENCES dest(id)
);
Notice that the CODE column is optional in the SOURCE table and mandatory in the DEST table.

Once the basic tables are in place we can create a table to hold the DML error logs for the
DEST. A log table must be created for every base table that requires the DML error logging functionality. This can be done manually or with the CREATE_ERROR_LOG procedure in the DBMS_ERRLOG package, as shown below.

-- Create the error logging table.
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
END;

SQL>
The owner, name and tablespace of the log table can be specified, but by default it is created in the current schema, in the default tablespace with a name that matches the first 25 characters of the base table with the "ERR$_" prefix.
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'TEST';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST DEST USERS
TEST DEST_CHILD USERS
TEST ERR$_DEST USERS
TEST SOURCE USERS

4 rows selected.

SQL>
The structure of the log table includes maximum length and datatype independent versions of all available columns from the base table, as seen below.
SQL> DESC err$_dest
Name Null? Type
--------------------------------- -------- --------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
ID VARCHAR2(4000)
CODE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)


SQL>
Insert
When we built the sample schema we noted that the CODE column is optional in the SOURCE table, but mandatory in th DEST table. When we populated the SOURCE table we set the code to NULL for two of the rows. If we try to copy the data from the SOURCE table to the DEST table we get the following result.
INSERT INTO dest
SELECT *
FROM source;

SELECT *
*
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

SQL>
The failure causes the whole insert to roll back, regardless of how many rows were inserted successfully. Adding the DML error logging clause allows us to complete the insert of the valid rows.
INSERT INTO dest
SELECT *
FROM source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

SQL>
The rows that failed during the insert are stored in the ERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'INSERT';


ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

SQL>
Update
The following code attempts to update the CODE column for 10 rows, setting it to itself for 8 rows and to the value NULL for 2 rows.
UPDATE dest
SET code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE id BETWEEN 1 AND 10;
*
ERROR at line 2:
ORA-01407: cannot update ("TEST"."DEST"."CODE") to NULL

SQL>
As expected, the statement fails because the CODE column is mandatory. Adding the DML error logging clause allows us to complete the update of the valid rows.
UPDATE dest
SET code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE id BETWEEN 1 AND 10
LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;

8 rows updated.

SQL>
The rows that failed during the update are stored in the ERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'UPDATE';


ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

 2 rows selected.
SQL>
Merge
The following code deletes some of the rows from the DEST table, then attempts to merge the data from the SOURCE table into the DEST table.
DELETE FROM dest
WHERE id > 50000;


MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code = b.code,
a.description = b.description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (b.id, b.code, b.description);
*
ERROR at line 9:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")


SQL>
As expected, the merge operation fails and rolls back. Adding the DML error logging clause allows the merge operation to complete.
MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code = b.code,
a.description = b.description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (b.id, b.code, b.description)
LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

SQL>
The rows that failed during the update are stored in the ERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'MERGE';


ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")


SQL>
Delete
The DEST_CHILD table has a foreign key to the DEST table, so if we add some data to it would would expect an error if we tried to delete the parent rows from the DEST table.
INSERT INTO dest_child (id, dest_id) VALUES (1, 100);
INSERT INTO dest_child (id, dest_id) VALUES (2, 101);
With the child data in place we ca attempt to delete th data from the DEST table.
DELETE FROM dest;
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated - child record found



SQL>
As expected, the delete operation fails. Adding the DML error logging clause allows the delete operation to complete.
DELETE FROM dest
LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;


99996 rows deleted.


SQL>
The rows that failed during the delete operation are stored in the ERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A69
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'DELETE';


ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------------------
2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
child record found


2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
child record found




2 rows selected.


SQL>