Mennan On Writing...

Developers always use time related data types in their applications to audit information. Oracle give additional functionality supplied functions and data types. One of my previous posts i mentioned how to use interval data types. Now i want to give some examples how to use some of them.

First it is possible to convert interval data from string values:

SQL> SELECT to_yminterval('02-10') FROM dual;

TO_YMINTERVAL('02-10')
----------------------
+000000002-10

SQL> SELECT to_dsinterval('02 01:00:30') FROM dual;

TO_DSINTERVAL('0201:00:30')
---------------------------
+000000002 01:00:30

Second, i think more powerful, convert datetime strings from external programing languages such as C# or Java, Oracle timestamps and manipulate easiliy with them. Suppose you want to convert format of a datetime string. ( '23:12:54.899 +02:00 Thu Nov 29 2001' to '011129' )With oracle's supplied functions you are not need to make conversions such find-replace. Look at example below:

SQL> SELECT tz_offset('Asia/Istanbul') FROM dual;

TZ_OFFSET('ASIA/ISTANBUL')
--------------------------
+02:00

SQL> SELECT to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY') FROM dual;

TO_TIMESTAMP_TZ('23:12:54.899+'
-------------------------------------------------
29-NOV-01 11.12.54.899000000 PM +02:00

SQL> SELECT to_char( to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY'), 'YYMMDD') FROM dual;

TO_CHAR(TO_TIMESTAMP_TZ('23:12'
------------------------------
011129

A few days ago a question was asked in oracleturk mail group. It was about how to convert rows into columns. In my previous post, i have showed how to convert rows to columns. Now i studied SQL Model clause(you can find a quick start here)

Demonstration below shows how to achieve it with Oracle's Model Clause.

One of the features of Oracle database is to create global triggers called system triggers. They are executed in spesific situations. For more information check docs.

Below a simple demonstration that shows how to create this type of triggers. A LOGON trigger created to audit logins of users:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL>
SQL> drop table user_logs;

Table dropped

SQL> create table user_logs( osuser varchar2(32), datetime date );

Table created

SQL> CREATE OR REPLACE TRIGGER log_on_trg
2 AFTER logon ON hr.SCHEMA
3 BEGIN
4 INSERT INTO user_logs
5 VALUES
6 ((SELECT distinct ss.OSUSER FROM v$session ss WHERE ss.SID = userenv('sid')), SYSDATE);
7 END;
8 /

Trigger created

SQL> show err;
No errors for TRIGGER SYS.LOG_ON_TRG

SQL> conn hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> conn sys/0000@XE as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> SELECT * FROM user_logs;

OSUSER                            DATETIME
-------------------------------- -----------
DELL-\Administrator               28.11.2006
DELL-\Administrator               28.11.2006

SQL>

In my previous post I have mentioned sample deadlock scenario in Oracle database.

For data integrity, oracle locks the row which you tend to update with FOR UPDATE keywords in SELECT statement. There is an undocumented feature of oracle that skips locked rows. It can be useful to pevent deadlocks. Look at example below:

In Oracle database users are allowed to manipulate and store binary objects like images or files. It is a very powerful side of Oracle that use SQL and PL/SQL language to play with binary data. In large systems only small data types such as VARCHAR2 are not satisfy requirements. Only 4000 bytes of characters can be stored in Oracle in CHAR types. If requirements are met, it can be possible to use extended data types as objects.

There is a small demostration that shows simply how to play with BLOBs:

When working on aggregate functions, you must be aware of rownum. Resultset firstly taken with rownum and then aggregate functions are processed.

Look at example below:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop table testtab;

Table dropped

SQL> CREATE TABLE testtab AS  SELECT o.object_name n, mod(rownum, 5) i, rownum r FROM all_objects o WHERE rownum < 11;

Table created

SQL> SELECT * FROM testtab;

N                                       I          R
------------------------------ ---------- ----------
ICOL$                                   1          1
I_USER1                                 2          2
CON$                                    3          3
UNDO$                                   4          4
C_COBJ#                                 0          5
I_OBJ#                                  1          6
PROXY_ROLE_DATA$                        2          7
I_IND1                                  3          8
I_CDEF2                                 4          9
I_PROXY_ROLE_DATA$_1                    0         10

10 rows selected

SQL> SELECT * FROM testtab WHERE i = 0;

N                                       I          R
------------------------------ ---------- ----------
C_COBJ#                                 0          5
I_PROXY_ROLE_DATA$_1                    0         10

SQL> SELECT max(r) FROM testtab WHERE i = 0 and rownum = 1;

    MAX(R)
----------
         5

SQL> SELECT max(r) FROM testtab WHERE i = 0;

    MAX(R)
----------
        10

SQL> SELECT * FROM (SELECT max(r) FROM testtab WHERE i = 0)  WHERE rownum = 1;

    MAX(R)
----------
        10

SQL>

In large datawarehouse systems queries take long time to execute, generally. To solve these problems Oracle has improved its queries and tools. One of them is materialized views as called MVs. Main idea of this MVs are to pre calculate or join tables. Data has formed and stored inside MVs in database, like an object such as  a table.

Suppose that you want to get average of a table that has two millions of records. As you predict, it is a long query. When you create a MVs to store averages, it will be very esay to read from MV instead of calculating all two millions of data. When a modification has performed on that table, MV has to update itself. This is a very comon use of MVs in Oracle.

Query rewrite takes place on calling this precalculated average. When you specify on create materialized view statement to enable query rewrite, before execution, it will be checked whether it can be rewrited. If so, instead of calculating again, precalculated table -MV- will selected.

For joining you may create a materialized view log to access the tables records.

To make more clear, there is demonstration below:

Oracle has introduced a very powerful mechanism called SQL Model to improve performance on SQL statements. Main idea of SQL Model clause is, to make possible some complex queries via SQL statements.

Model is based on arrays. MEASURES keyword identifies which arrays will be used. Indice of arrays are iddentified in DIMENSION BY statement. As a result, you can make array-based calculations in SQL easily.

In Model clause, business rules are taken into SQL. It is possible not to use a procedural language with model clause. One example of this can be found at the end of this entry with fibonacci numbers.

To make clear understanding of Model clause it'd better make some exercises:

It is possible to make an outer join with partitions in Oracle. Suppose that you have a table that contains workers' work days. If a worker did not work, there is no record in that table, working_time table. If your boss want you to prepare a sheet that contains all workers' working times. You have to put all day entries. If a worker did not work, you simple add "0" working hours for worker. How can you achieve this situation?

Follow the example....

Yesterday, I have post Changing Data Type of Non Empty Column In Oracle

Tonguc made me to investigate with DBMS_REDEFINITION supplied package that you make online DDL modifications with his post. I made a simple example:

When you change the data type of non-empty column you will get an error. To overcome this you can make a trick as shown below:

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as sysadm

SQL>
SQL> drop table z;

Table dropped

SQL> create table z( i number);

Table created

SQL> INSERT INTO z VALUES( 4);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table z modify i varchar2(12);

alter table z modify i varchar2(12)

ORA-01439: column to be modified must be empty to change datatype

SQL> desc z;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
I NUMBER Y

SQL> alter table z add(ii number);

Table altered

SQL> update z set ii = i;

1 row updated

SQL> SELECT * FROM z;

I II
---------- ----------
4 4

SQL> update z set i = null;

1 row updated

SQL> alter table z modify i varchar2(12);

Table altered

SQL> update z set i = ii;

1 row updated

SQL> alter table z drop column ii;

Table altered

SQL> desc z;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
I VARCHAR2(12) Y

SQL> drop table z;

Table dropped

SQL>

/****************************************************************************
****************************************************************************
* Author :
* Date : 15.04.2006
* Purpose : This program reads 5X5 matrix, find maximum element of matrix,
* get the biggest digit of maximum element and searches if the
* biggest digit exists in matix.
****************************************************************************
****************************************************************************/

/* include files */
#include <stdio.h>

/*Macros*/
#define SQUARE_SIZE 5

int main()
{
/*Declerations*/
int matrix[SQUARE_SIZE][SQUARE_SIZE];
int i, j, temp, maximum_element, biggest_value;

maximum_element = 0;
biggest_value = 0;

/*Fill The Matrix*/
for(i = 0; i < SQUARE_SIZE; i++){
printf("Enter %d row of matrix : ", i + 1 );
for(j = 0; j < SQUARE_SIZE; j++){
scanf("%d", &matrix[i][j]);
/*Find Maximum Element*/
if( matrix[i][j] > maximum_element ){
maximum_element = matrix[i][j];
}
}
}
printf("\n\n\nResults:\n\n");
printf("The Matrix : \n");
/*Write The Matrix*/
for(i = 0; i < SQUARE_SIZE; i++){
for(j = 0; j < SQUARE_SIZE; j++){
printf("%6d", matrix[i][j] );
}
printf("\n" );
}
printf( "\n\nThe Maximum Number is % d\n", maximum_element );
/*Fİnd The Biggest Digit*/
while( maximum_element > 0 ){
temp = maximum_element % 10;
maximum_element /= 10;
if( temp > biggest_value ){
biggest_value = temp;
}
}
printf( "\nThe Maximum Digit is % d\n", biggest_value ) ;
printf( "Matrix is searching....\n");
printf( "Results:\n");
/*Search The Matrix*/
for(i = 0; i < SQUARE_SIZE; i++){
temp = 0;
for(j = 0; j < SQUARE_SIZE; j++){
/*Find Biggest Digit*/
if( matrix[i][j] == biggest_value ){
temp++;
}
}
/*Found...*/
if( temp > 0 ){
printf( "%d row has %d elements\n", i + 1, temp);
}
}
printf("\n\n\nTo Exit Press Any Key....");
getch();
return 0;
}