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?

My Investment Plan

Its been a while since I have been thinking of investing some money in stocks. In the mean time the tough times came in and I just thanked my stars for not investing at that time. But now since I feel the market is not too high or not too low I am planning to invest in some stocks. I would like to pick some say 5 stocks and see how they perform till the end of this year. At the end of this year I would like to take out all the money and spend the profit for my sister's wedding. Well here the assumption is that I would make some profit. Even if I don't make any I think I have some saving which I can use on the occasion but I am putting that as my target because if there is a chance that I get a good profit I might get greedy and not pull out the money. Well the point is that I am investing for an occasion and I want to see if I can get anything out of my investments. I am penning this to look back and see how I perform.

Dropping Constraints.

I was working on dropping a primary key constraint on a table today and when I was recreating the constraint back my create statement was failing saying that an object already exists with that name. When I checked I found that the index on the table used for the primary key was not dropped when I dropped the constraint. This I thought was a little strange. So I tried to retest why this was happening, but the next time I created and dropped the primary key constraint both the constraint and the index were dropped. When I googled for the possible cause of this kind of phenomenon I found that when the table is created in oracle 9 and we are tying to drop the constraint in oracle 10 the indexes are not dropped. To ensure that the index is also dropped below statement can be used.

alter table X drop constaint x_pk drop index;

Updating partition key column

Recently i was working on a generating some test data into a large table which is partitioned by year. I was supposed to generate some data for the current year. And i thought i would just update the date part to current year and i would have the data. But since the table was partitioned and i was trying to update the partitioned column i got the below error and the update failed.
ORA-14402: updating partition key column would cause a partition change
Taking a look at the error it looked more like a warning and less a error. So i googled to find how i could fix the issue.
Below is the alter statement that I applied on the table.
alter table table_name enable row movement;
I did my update and restored the table back by applying the below statement.
alter table table_name disable row movement;

Malireddy Look-alike Meter

MyHeritage: Family tree - Genealogy - Celebrity - Collage - Morph

PL/SQL: numeric or value error: host bind array too small

I was getting this error today when i was debugging one bug in my code. On goggling i found that this is related to the dbms_output package that is being called in my code. The error turns out to be that when dbms_output.put_line proc is execute with a parameter whose length is more than 255 characters then the below error is generated.

SQL> exec dbms_output.put_line('123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890');

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1


So when ever you execute dbms_output take care to see that the parameter is not more than 255 chanracter. I ran this on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0.

The Affluent Society

I happen to come across this link and found these quotes really intresting.

"Popularity is not always a test of needed intelligence"

"There remains always the possibility, even the probability, that they(Books) do more for the self-esteem of the author than for the fate of the world"

Hope fully i will get to read this book some time soon.

Oracle Article Links.

The below links have listed some of the commonly used linux commands.
Linux Commands - Part 1
Linux Commands - Part 2

Merge and Invalid Identifier Error.

Today early in the morning my boss called me and gave me a requirement to load a file into a table. And this new table has to be merged with the existing table in the database. i.e if it has any new rows then insert into the existing table and for the existing rows if there are new additions then update the existing table. I was so happy that i knew the oracle's merge command and thought I would finish my work in no time. My happiness lasted only till i encountered the "ORA-00904: invalid identifier" error.

Well here is what happened.

SQL> desc ms_zip_code_dtl_main;
Name Null? Type
----------------------------------------- -------- ------------------

COUNTRY_CD NOT NULL CHAR(2)
STATE_ID NOT NULL CHAR(2)
ZIP NOT NULL VARCHAR2(30)
CITY NOT NULL VARCHAR2(35)
COUNTY_NM VARCHAR2(50)
ENTRY_DT NOT NULL DATE
ENTRY_USR NOT NULL CHAR(8)

SQL> drop table ms_zip_code_dtl_main_temp;

Table dropped.

Elapsed: 00:00:01.09
SQL> CREATE TABLE ms_zip_code_dtl_main_temp AS
2 SELECT *
3 FROM ms_zip_code_dtl_main;

Table created.

SQL> merge
2 into ms_zip_code_dtl_main a
3 using ms_zip_code_dtl_main_temp b
4 on(a.zip = b.zip
5 and a.country_cd = b.country_cd
6 and a.state_id = b.state_id
7 and a.city = b.city)
8 when matched then
9 update
10 set a.country_cd = b.country_cd,
11 a.state_id = b.state_id,
12 a.city = b.city,
13 a.county_nm = b.county_nm,
14 a.entry_dt = b.entry_dt,
15 a.entry_usr = b.entry_usr
16 when not matched then
17 insert(a.country_cd,
18 a.state_id,
19 a.zip,
20 a.city,
21 a.county_nm,
22 a.entry_dt,
23 a.entry_usr)
24 values(b.country_cd,
25 b.state_id,
26 b.zip,
27 b.city,
28 b.county_nm,
29 b.entry_dt,
30 b.entry_usr)
31 /
and a.country_cd = b.country_cd
*
ERROR at line 5:
ORA-00904: "A"."COUNTRY_CD": invalid identifier

And then i saw this invalid identifier error and then i was thinking why the hell is it giving me this error. Because country_cd is very well a column in both the table. And then after some juggling i went on the all answers site Google and found this link which opened my eyes.

The problem in my case is that i am updating the columns that i am using in the join between the two tables. Actually if you think it really doesn't make any sense updating the columns when you got the rows on the condition that both the columns are equal.

What next, i made the change and my code works just fine.
SQL> ed
Wrote file afiedt.buf

1 merge
2 into ms_zip_code_dtl_main a
3 using ms_zip_code_dtl_main_temp b
4 on(a.zip = b.zip
5 and a.country_cd = b.country_cd
6 and a.state_id = b.state_id
7 and a.city = b.city)
8 when matched then
9 update
10 set a.county_nm = b.county_nm,
11 a.entry_dt = b.entry_dt,
12 a.entry_usr = b.entry_usr
13 when not matched then
14 insert(a.country_cd,
15 a.state_id,
16 a.zip,
17 a.city,
18 a.county_nm,
19 a.entry_dt,
20 a.entry_usr)
21 values(b.country_cd,
22 b.state_id,
23 b.zip,
24 b.city,
25 b.county_nm,
26 b.entry_dt,
27* b.entry_usr)

And time and again it thought me not to assume things.

sys_connect_by_path

The function sys_connect_by_path is a analytical function used in hierarchical queries. This was explained nicely in one of Tom's articles in Oracle Magazine. Here is the link
http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html

Serially Reusable Packages

Serially Reusable packages can be used in scenarios where we dont want the packages to store the global variable through the session. The below code demonstrates the use of PRAGMA SERIALLY_REUSABLE clause in packages. More information is available at Oracle Documentation

SQL> create or replace package test_serially_reusable as
2
3 var1 number;
4 var2 number;
5 procedure setvar1(p_num number);
6 procedure setvar2(p_num number);
7 end test_serially_reusable ;
8 /

Package created.

SQL>
SQL> create or replace package body test_serially_reusable as
2
3 procedure setvar1(p_num number) is
4 begin
5 var1:=p_num ;
6 dbms_output.put_line('var1->'||var1);
7 dbms_output.put_line('var2->'||var2);
8 end;
9 procedure setvar2(p_num number) is
10 begin
11 var2:=p_num ;
12 dbms_output.put_line('var1->'||var1);
13 dbms_output.put_line('var2->'||var2);
14 end;
15 end test_serially_reusable ;
16 /

Package body created.

SQL> show err
No errors.
SQL>
SQL> exec test_serially_reusable.setvar1(100);
var1->100
var2->

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_serially_reusable.setvar2(200);
var1->100
var2->200

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace package test_serially_reusable as
2 pragma serially_reusable;
3 var1 number;
4 var2 number;
5 procedure setvar1(p_num number);
6 procedure setvar2(p_num number);
7 end test_serially_reusable ;
8 /

Package created.

SQL>
SQL> create or replace package body test_serially_reusable as
2 pragma serially_reusable;
3 procedure setvar1(p_num number) is
4 begin
5 var1:=p_num ;
6 dbms_output.put_line('var1->'||var1);
7 dbms_output.put_line('var2->'||var2);
8 end;
9 procedure setvar2(p_num number) is
10 begin
11 var2:=p_num ;
12 dbms_output.put_line('var1->'||var1);
13 dbms_output.put_line('var2->'||var2);
14 end;
15 end test_serially_reusable ;
16 /

Package body created.

SQL>
SQL>
SQL> exec test_serially_reusable.setvar1(100);
var1->100
var2->

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_serially_reusable.setvar2(200);
var1->
var2->200

PL/SQL procedure successfully completed.

begin
test_serially_reusable.setvar2(200);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
test_serially_reusable.setvar1(400);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/

begin
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/

begin
test_serially_reusable.setvar2(500);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
test_serially_reusable.setvar1(600);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/


SQL> spool off

Group by

Group by is a requirement for aggregate functions but that is not true the other way around. It is not necessary to have a aggregate functions to have group by in the query.

Eg :

select empno , count(*)
from emp
group by empno

To use count(*) or sum or any other aggregate function we need to use the group by clause.

select empno
from emp
group by empno

We need not require a aggregate function to use a group by clause. The above query just returns all the distinct empnos.

Defining Variables in sqlplus

The following is an example of how variables can be declared in sqlplus sessions.

var col1 char(2000)
var col2 char(2000)
exec ms_prc(123,123,:col1,:col2);
set pagesize 0
set linesize 10000
spool on
print col1
print col2
spool off

sqlplus - Termout

Termout is one of those sqlplus settings which tend to cause confusion. It only applies to output from running script files.

abc.sql:
set termout off
select 'abc' from dual

and run it like this in sqlplus:
@abc.sql

and no output is generated in the terminal. But this does'nt work for scripts which contain anonymous pl\sql blocks etc.

Multitable Insert.

Multi table insert seems to be a pretty cool method of inserting into multiple tables while selecting from a single source table. Following are some examples that i tried out.

create table emp_exec as
select *
from emp
where 1=3;

create table emp_nonexec as
select *
from emp
where 1=3;

-- This below one is a simple method of inserting into two table based on certain criteria. The all clause is used to execute all the inserts.

insert all
when sal > 3000 then
into emp_exec
when sal <= 3000 then into emp_nonexec select * from emp; -- 14 rows inserted The first clause is used to execute the first part first and when the condition is not met then execute the next part. insert first when sal > 3000 then
into emp_exec
else
into emp_nonexec
select * from emp;

-- 14 rows inserted

create table emp_name as
select empno , ename
from emp
where 1=3;

create table emp_dept as
select empno , deptno
from emp
where 1=3;

insert all
into emp_name values (empno , ename)
into emp_dept values (empno , deptno)
select * from emp;

-- 28 rows inserted

Just a little saying....

It may not be finished in first hundred days. It may not be finished in first thousand days. ... It may not be finished in our lives on this planet. But let us begin

zcat in Bash

zcat is a command to display the contents of a file that is zipped using compress in unix.
Eg:
zcat log
The above commad looks for a file of the format log.Z in the directory and decompresses it and then displays to the standard output.