Oracle Merge error handling / dbms_errlog example

The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources.

Works only after Oracle10g Release 2

DROP TABLE catalog1;
CREATE TABLE catalog1 (ID NUMBER(3), item VARCHAR2 (20), price number);
INSERT INTO catalog1 VALUES(1, 'laptop', 800);
INSERT INTO catalog1 VALUES(2, 'iphone', 500);
insert into catalog1 values(3, 'camera', 400);

drop TABLE catalog2;
--To explicitly produce error column price is set to VARCHAR2
CREATE TABLE catalog2 (ID NUMBER(3), item VARCHAR2 (20), price VARCHAR2 (20));
INSERT INTO catalog2 VALUES(1, 'laptop', 100);
INSERT INTO catalog2 VALUES(2, 'iphone', 200);
insert into catalog2 values(3, 'camera', 'ABCDEFG'  );

drop table errlog;

BEGIN
--  dbms_errlog.create_error_log ('catalog1', 'errlog'); -- Use this if you need to use own error table name rather then default name
  dbms_errlog.create_error_log ('catalog1'); 
END;


/* Using dbms_errlog for MERGE*/
MERGE INTO catalog1 s1 USING catalog2 s2 ON (s1.id = s2.id)
WHEN MATCHED THEN UPDATE SET s1.price = s2.price 
--WHEN NOT MATCHED THEN INSERT (ID, item, price) VALUES (s2.ID, s2.item, s2.price)
--LOG errors INTO errlog ('Bad') REJECT LIMIT UNLIMITED
--LOG errors INTO errlog ('Bad') REJECT LIMIT 100
LOG ERRORS ('put comment') REJECT LIMIT UNLIMITED
;

/* Using dbms_errlog for update*/
BEGIN
   UPDATE catalog1
      SET price = 'AAAAAAAAAA'
      where rownum  < 2
      LOG errors ('put comment') REJECT LIMIT UNLIMITED;
END;


SELECT * FROM err$_catalog1; -- use this if you have not used error table name in dbms_errlog.create_error_log

SELECT * FROM errlog; -- use this if you have used error table name in dbms_errlog.create_error_log

Set system profile values from SQL Oracle Apps

Use fnd_profile.save to set system profile at site level from SQL.


Declare
value Boolean;
Begin
value := fnd_profile.save('put profile name code','new_profile_value','SITE');
commit;
End;

Oracle compare dates with timestamp

Dates with timestamp can be compared by using

to_date(DATE_FIELD_VAL, 'DD/MM/YY HH24:MI:SS')

Date from : '07/11/13 01:01:01'
Date To :  '07/11/13 23:59:59'

Note that it's 24Hr format.


to_date(DATE_FIELD_VAL, 'DD/MM/YY HH24:MI:SS') between to_date('07/11/13 01:01:01','DD/MM/YY HH24:MI:SS') and to_date('07/11/13 23:59:59','DD/MM/YY HH24:MI:SS')

ORA-01031: insufficient privileges

ORA-01031: insufficient privileges error is caused due to invalid permission.

When happens on a Select/Insert/Delete/Update/Alter query, check if table has grants issued to the username trying to access.

For package make sure execute permission is given to username trying to access package. Even then if it does't work, use AUTHID CURRENT_USER on package spec.