How to store rounded number in Oracle ?

If you have a requirement to store the numbers rounded to nearest hundreds , what would be the easy way ?

You can  satisfying this requirement by defining the column as number ( xxx, -2) ...
Here is an example ...

SQL> drop table t;

Table dropped.

SQL> create table t ( col1 number( 5,-2) ) ;
Table created.

SQL> insert into t values ( 34579) ;
1 row created.


SQL> insert into t values ( 987562.25) ;
1 row created.


SQL> select * from t ;


COL1
----------
34600
987600

By declaring at the table level , we can ensure the value entered is always rounded to nearest hundreds , no matter what the application is.

This may not be easily achievable in other DBMS ...


This is yet another reason for the applications to be database dependent . 

For the "pro - database independent"  application developers / architects.. do you have any suggestions ?

Sequences in SQL Server .

SQL Server 2011 has introduced sequences that has been in Oracle for years .
In Oracle , the sequence was used to generate unique numbers to maintain ( surrogate ) primary keys.

SQL Server / Sybase have used identity column to generate unique numbers.
Unlike identity column , sequence is not tied to a table .

It eases the pain , when migrating the data from Oracle to SQL Server or vice versa.Wonder , if Oracle would come up with 'identity column' in the future releases.

See the examples below ...

C:\>sqlcmd -S ZMOHIDEEN\SQL11
1> use test2011
2> go


Changed database context to 'test2011'.


1> CREATE TABLE T (
2> TID int,
3> FName varchar(50) NOT NULL,
4> LName varchar(50) NOT NULL,
5> );
6> GO

1>
2> select * from t;
3> go

TID      FName        LName
------ -------------- -------------------
(0 rows affected)


1> CREATE SEQUENCE SEQ_T
2> START WITH 2000
3> INCREMENT BY 1
4> MAXVALUE 5000
5> CACHE 20
6>
7> GO

1> INSERT INTO T (
2> TID, FName, LName
3> ) VALUES (
4> NEXT VALUE FOR SEQ_T, 'Zahir', 'Mohideen'
5> );
6> go


(1 rows affected)


1> select * from t;
2> go


TID            FName                    LName
----------- ---------------------- ------------------------------
2000            Zahir              Mohideen
(1 rows affected)


1> INSERT INTO T (
2> TID, FName, LName
3> ) VALUES (
4> NEXT VALUE FOR SEQ_T, 'N', 'Mohideen'
5> );
6> go
(1 rows affected)

1> select * from t;
2> go

TID             FName                         LName
----------- -------------------------------   --------------
2000        Zahir                             Mohideen
2001        N                                 Mohideen

(2 rows affected)

Interval Partitioning

Oracle 11g has a new feature that will make life easier for the DBAs. Prior to this release , the DBAs must make sure they create a new partition to accommodate the new incoming dataset .

 For example , to facilitate year end processing , they should create additional partitions to move the new year’s data into the database . Failing to do so will create “abend” jobs . They have to reactively create new partitions and re-run the job.


Let me see with an example.

Let us create a table , partition by year .

SQL> CREATE TABLE t_partTable created.

2 (
3 salyear INTEGER ,
4 salmount NUMBER(5,2) ,
5 saldesc VARCHAR2(40)
6 )
7 partition BY range
8 (
9 salyear
10 )
11 (
12 partition p_2001 VALUES less than (2001),
13 partition p_2002 VALUES less than (2002),
14 partition p_2003 VALUES less than (2003),
15 partition p_2004 VALUES less than (2004)
16 );



Let us insert few records.

SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;Let us add a record for the year 2004. As you can see , there is an error as we have not created a partition for the year 2004.

1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.


SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.


SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');

PL/SQL procedure successfully completed.


SQL> SELECT table_name,
2 partition_name,
3 num_rows
4 FROM user_tab_partitions
5 WHERE table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------- -------------- -------
T_PART      P_2001         1
T_PART      P_2002         1
T_PART      P_2003         2
T_PART      P_2004         2


 
SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;

insert into t_part values ( 2004 , 550.00 , 'French DVD')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

We can mitigate the above issue by creating a default partition .
Here is the second version of the table.

SQL> drop table t_part purge ;

Table dropped.


SQL> create table t_part
2 (
3 salyear integer ,
4 salmount number(5,2) ,
5 saldesc varchar2(40)
6 )
7 partition by range(salyear)
8 (
9 partition p_2001 values less than (2001),
10 partition p_2002 values less than (2002),
11 partition p_2003 values less than (2003),
12 partition p_2004 values less than (2004),
13 partition p_max values less than (MAXVALUE)
14 );


Table created.

SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;
1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.


SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.


SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.


SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;
1 row created.


SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.


SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';


TABLE_NAME PARTITION_NAME NUM_ROWS


---------   -----------  ----------
T_PART       P_2001           1
T_PART       P_2002           1
T_PART       P_2003           2
T_PART       P_2004           2
T_PART       P_MAX            1



As you can see the issue is mitigated ...
Let us insert a insert for another year .

SQL> insert into t_part values ( 2010 , 950.00 , 'Tamil Blu Ray ') ; SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
--------- ----------- ----------
T_PART      P_2001           1
T_PART      P_2002           1
T_PART      P_2003           2
T_PART      P_2004           2
T_PART      P_MAX            1


The issue is mitigated , but all other data goes into one partition , which lead into other issues ...

Here is where interval partition comes handy.

Let us create third version of the table .

SQL> drop table t_part purge ;

Table dropped.

SQL> create table t_part
2 (
3 salyear integer ,
4 salmount number(5,2) ,
5 saldesc varchar2(40)
6 )
7 partition by range(salyear)
8 interval(1)
9 (
10 partition p_2001 values less than (2001),
11 partition p_2002 values less than (2002),
12 partition p_2003 values less than (2003),
13 partition p_2004 values less than (2004)
14 );

Table created.
SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;

1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;
1 row created.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
--------- ------------- ----------
T_PART       P_2001          1
T_PART       P_2002          1
T_PART       P_2003          2
T_PART       P_2004          2
T_PART       SYS_P62         1

As you can see from the above example , Oracle gracefully accomodated new record and assigned it to a new partition ( system defined ) . Later on , you can rename the partition to user defined one.

SQL> alter table t_part rename partition SYS_P62 to p_2005;

Table altered.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------- ------------- ----------
T_PART       P_2001           1
T_PART       P_2002           1
T_PART       P_2003           2
T_PART       P_2004           2
T_PART       P_2005           1
Let us create another record for the year 2010
 SQL> insert into t_part values ( 2010 , 950.00 , 'Tamil Blu Ray ') ;

1 row created.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------  -------------- ----------
T_PART      P_2001           1
T_PART      P_2002           1
T_PART      P_2003           2
T_PART      P_2004           2
T_PART      P_2005           1
T_PART      SYS_P63          1


Now , Oracle has created another parition for the different  year's data.

Hope you enjoyed this post.



1 row created.