Error Logging Tables.

Error logging tables are a new feature introduced in oracle 10g release 2. They are used in places where the a unique constraint or a check constraint of a single row can result in the failure of a DML statement involving huge amount of data. For example you are importing data from a temporary table which has say a million records. The insert statement runs for about an hour and then gives a unique constraint error and rolls back all of the data inserted. In these cases wouldn't it be nice if the SQL inserted all the rows except the ones which generated the errors and logged the erroneous records in a separate table. Error logging tables do exactly that.

We can create an error table by using the procedure dbms_errlog.create_error_log.

The following syntax can be used to log the errors into the log table.

LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT {integer|UNLIMITED} ]


Below is the sample code to understand how the new error logging works.

drop table src ;

drop table tgt;

CREATE TABLE src (x,y,z)
AS
SELECT object_id
, object_type
, object_name
FROM all_objects
WHERE ROWNUM <= 5;


CREATE TABLE tgt
AS
SELECT *
FROM src
WHERE ROWNUM <= 3;

alter table tgt add constraint pky primary key (x);

insert into tgt
select * from src;

drop table error_tgt;

exec dbms_errlog.create_error_log(dml_table_name=>'tgt',err_log_table_name=>'error_tgt');

insert into tgt
select * from src
log errors into error_tgt ('log errors testing') reject limit unlimited;

select x , y ,z ,ORA_ERR_TAG$ from error_tgt;


References :
Natalka's Blog on ORAFAQ
Tim's OracleBase site
Databasejournal site
Adrian Billington's site
Mark Rittman's article in Oracle Magazine

SYS.ANYDATA

Sys.anydata is a new datatype introduced in 9i. This datatype can be used to define a column which can be of any datatype. The advantage of this is that if we insert a data into a column defined as anydata datatype, it remains a date. If we insert a number, it remains a number. Below is the list of some example code provided on asktom site .

create table t(x sys.anyData);

insert into t values(sys.anyData.convertNumber(5));

insert into t values(sys.anyData.convertVarchar2('hello world'));

insert into t values(sys.anyData.convertDate(sysdate));

11:54:52 SQL> select * from t;

X()
-----------------------------------------

ANYDATA()
ANYDATA()
ANYDATA()


SQL> create or replace function getData(p_x in sys.anyData) return varchar2
2 as
3 l_varchar2 varchar2(4000);

4 l_rc number;
5 begin
6 case p_x.gettypeName
7 when 'SYS.NUMBER' then
8 l_rc := p_x.getNumber(l_varchar2);
9 when 'SYS.DATE' then

10 l_rc := p_x.getDate(l_varchar2);
11 when 'SYS.VARCHAR2' then
12 l_rc := p_x.getVarchar2(l_varchar2);
13 else
14 l_varchar2 := '** unknown **';
15 end case;
16

17 return l_varchar2;
18 end;
19 /

Function created.

SQL> select getData(x) getdata from t;


GETDATA
____________

5
19-MAR-02
hello world

NLS Date Format.

I was reading an interesting article about SQL Injection and found that we can set the NLS_DATE_FORMAT this way too.

14:14:43 SQL> ALTER SESSION SET NLS_DATE_FORMAT = '"THIS IS A SINGLE QUOTE ''"';


Session altered.

Elapsed: 00:00:00.00
15:46:49 SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------------
THIS IS A SINGLE QUOTE '

Interesting stuff. Apparently we can use this the above setting to inject procedure which do not have any parameters passed too. Read this for me.

what is an Index Organized Table?

Over the last few days I came across this term IOT lot of times. I tried to memorize the abbreviation for it. Index Organized Table, Index Organized Table, Index Organized Table..... But it didn't sink in. Few days passed and today I was reading an article on asktom and came across a question on IOTs. And then I was trying to recollect what an IOT stood for. Well that is when I decided I need to learn about it in a better way than memorizing what IOT's full form was.
Then as anyone would do I goggled and found this wonderful white paper on Index Organized Tables and all the nitty gritty details of it. To summarize an Index Organized Table is a table which is organized based on the primary key Index of the table. This reduces storage requirements and the additional IO required during querying to access the rowid from the index and then the table. There are many added advantages to the Index Organized Tables like enabling parallel processing even tough the table is not partitioned. An IOT is no different from a conventional table in terms of the data types that can be stored except that we can not create object tables as IOTs. All the object features like Object type, VARRAYs, Nested Tables, REF cursors etc. are supported.
I could find only the advantages of using IOTs in the previous article but no disadvantages are listed there. But I found another good article which explained clearly how IOTs are physically organized and in what scenarios a IOT can be a performance drainer. It turns out that when a IOT is created physically the data will be stored in the index as well as the overflow table which contains the data of the overflowed columns. The actual table is just a logical entity which fetches data from the index and the overflow table. The only disadvantage I found was that when we need to do a full table scan on non key tables then we might have to do more physical reads than in the case where the table was a conventional table.
Having read all the articles I am left with the questions:
1) Can I convert all my existing tables into index organized table?
2) Do I have a criteria in selecting the tables which are a good fit to be defined as Index Organized Tables?