Friday, July 21, 2006

The Lack of Oracle

As of my own experience, i can told that i found some lack of Oracle. Previously i am a MySQL database user.

Here is a lack of Oracle :
1. There is no LIMIT feature like a MySQL databases.
2. If you want to change a field in a database like insert a new field between 2 field, you can not do it. It always added on the last field.

For the first lack I do this statement :

In MySQL :
----------
"SELECT DIVISION_CODE, DIVISION_NAME, LKP_NAME FROM T_DIVISION T1, TSYS_LKP T2 WHERE T1.IS_ACTIVE = T2.LKP_CODE AND LKP_TYPE = 'IS_ACTIVE_TYPE' ORDER BY 1 ASC LIMIT 0, 30"

In Oracle :
-----------
"SELECT DIVISION_CODE, DIVISION_NAME, LKP_NAME, NO_RECORD FROM (SELECT DIVISION_CODE, DIVISION_NAME, T2.LKP_NAME, ROW_NUMBER() OVER (ORDER BY DIVISION_CODE ASC) NO_RECORD FROM T_DIVISION T1, TSYS_LKP T2 WHERE T1.IS_ACTIVE = T2.LKP_CODE AND LKP_TYPE = 'IS_ACTIVE_TYPE' ) WHERE NO_RECORD BETWEEN 1 AND 30 ORDER BY 1 ASC"

For the second, you must drop and recreate table with a new structure.

2 comments:

psonica said...

Great site loved it alot, will come back and visit again.
»

ekp said...

LIMIT functionality is possible with Oracle.

Have a look at www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

And search for ROWNUM on Ask Tom site (asktom.oracle.com).