How to enable DML logging

DML logging can be used as a workaround if you have a large number of rows to process, and you expect that some of these will fail to be inserted correctly due to data quality problems. In other words, some rows may violate one or more constraints on the receiving table.

Here is a short demonstration that I have recently tested as a workaround for a client.

I have a table called MYTABLE, which looks like this:

 Name                   Null?    Type
 --------------------- -------- -------------------
 ORDER_ID                        NOT NULL NUMBER
 ORG_ORDERID                     NUMBER
 VALUE_ID_TYPE                   VARCHAR2(20)
 COL_PREFIX                      VARCHAR2(80)
 COL_NAME                        VARCHAR2(500)
 COL_SUFFIX                      VARCHAR2(20)

There is a unique constraint on the table:

--create the index
CREATE UNIQUE INDEX C_COL_UIX ON MYTABLE
(ORG_ORDERID, COL_PREFIX, COL_NAME, COL_SUFFIX)
TABLESPACE USERS;

-- create the constraint, using index created above
ALTER TABLE MYTABLE ADD (
  CONSTRAINT C_COL_CONS
  UNIQUE (ORG_ORDERID, COL_PREFIX, COL_NAME, COL_SUFFIX)
  USING INDEX C_COL_UIX);

The actual DML logging is performed by adding the statement

LOG ERRORS INTO error_table ('INSERT') REJECT LIMIT UNLIMITED;

at the end of the INSERT statement.

Before we can do this we need to create the error log table:

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'MYTABLE');
END;
/

The statement above will create a table called ERR$_MYTABLE, where rejected rows will be inserted into.

For this test, I will now verify that the values I intend to use, actually exist in the table:

select count(*)
from mytable
where org_orderid = 122
and col_prefix = 'A'
and col_name = 'B'
and col_suffix = 'X'
;

  COUNT(*)
----------
         1

Any attempt to insert a row into the table with the same values as in the select statement above (122, A, B, X), will violate constraint C_COL_CONS, and logged in the table ERR$_MYTABLE.

Let's execute an INSERT statement that will violate the constraint:

INSERT
INTO mytable (order_id, col_prefix, col_name, col_suffix, col_type)
VALUES(122, 'A', 'B', 'X', 'D')
LOG ERRORS INTO ERR$_MYTABLE ('INSERT') REJECT LIMIT UNLIMITED;

As expected, I get the following error:

ORA-00001: unique constraint (C_COL_CONS) violated

Let's check the log table:

SELECT * FROM ERR$_MYTABLE;

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	ORDER_ID	ORG_ORDERID	VALUE_ID_TYPE	COL_PREFIX	COL_NAME	COL_SUFFIX
1	ORA-00001: unique constraint (C_COL_CONS; violated		I	INSERT	2850841	122	D	A	B	X

If you execute a batchjob with thousands of rows, the offending rows will be caught so they can be corrected later. The non-offending rows will be neatly inserted where they belong.

How to create the SCOTT user

To create the user SCOTT, run the script

$ORACLE_HOME/rdbms/admin/utlsampl.sql

Observe that user SCOTT will be created with the following statement:

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

Note that if the default profile is using a password verification function, the creation will fail.

To work around, exchange default password "tiger" with something different that will honor the verification function, for example "T123G456R".

Remember to set

SET TERMOUT ON
SET ECHO ON

at the top, if you want to see the script output.

Using the procedure SEARCHFILES in DBMS_BACKUP_RESTORE

I found the following great article about how to list files in directory from within sqlplus here, written by David Marco. As pointed out by several oracle bloggers, there is very little documentation around for this package.

It just so happened that a user was in need of such functionality, and his code could be used straight-off.

However, my customer had already ammended a very useful functionality, by adding support for sending the directory name in as a parameter.

The code as it looks now:

CREATE OR REPLACE FUNCTION LIST_FILES( p_directory IN VARCHAR2, p_file_pattern IN VARCHAR2 default null)
RETURN file_array pipelined AS

l_path VARCHAR2(1024);
l_dummy VARCHAR2(1024);

BEGIN

   SELECT directory_path
   INTO l_path
   FROM all_directories
   WHERE directory_name = p_directory;

   sys.DBMS_BACKUP_RESTORE.SEARCHFILES(l_path, l_dummy);

   FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
                     FROM X$KRBMSFT
                     WHERE FNAME_KRBMSFT LIKE '%'|| NVL(p_file_pattern, FNAME_KRBMSFT)||'%' ) LOOP
      PIPE ROW(file_list.file_name);
   END LOOP;

END;
/

The call will thus be:

SELECT * FROM TABLE(LIST_FILES('MY_DIR','%*.dmp%'));

How to write a procedure that lets developers set the time for the database using the FIXED_DATE procedure

The following procedure may be used to let designated database developers change the system time according to their testing needs.

CREATE OR REPLACE PACKAGE admin_pack AUTHID DEFINER
AS
 PROCEDURE set_fixed_date(vv_fixed_date VARCHAR2);
 PROCEDURE unset_fixed_date;
END admin_pack;
/

CREATE OR REPLACE PACKAGE body admin_pack
AS
PROCEDURE set_fixed_date (vv_fixed_date VARCHAR2) IS
 BEGIN
  IF UPPER(vv_fixed_date) = 'NONE' THEN
     EXECUTE IMMEDIATE 'alter system set fixed_date=none';
  ELSE
     EXECUTE IMMEDIATE 'alter system set fixed_date=''' || vv_fixed_date || '''';
  END IF;
END;
PROCEDURE unset_fixed_date IS
 BEGIN
  EXECUTE IMMEDIATE 'alter system set fixed_date=none';
 END;
END admin_pack;
/


CREATE PUBLIC SYNONYM ADMIN_PACK FOR SYS.ADMIN_PACK;
GRANT EXECUTE ON ADMIN_PACK TO SCOTT;

Note the AUTHID clause in the package declaration:

A unit whose AUTHID value is CURRENT_USER is called an "invoker's rights" unit, or IR unit. A unit whose AUTHID value is DEFINER is called a "definer's rights" unit, or DR unit.

The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time. It will check:

• If the context for name resolution is CURRENT_SCHEMA.
• If the privileges checked are those of the CURRENT_USER and the enabled roles.

At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.

Author: Tomasz Gozdz
Documentation for the AUTHID DEFINER SETTING
Documentation for the FIXED_DATE procedure