Converting Rows To Columns In Oracle

Sometimes you can need to convert row values to spesific column values. In Oracle, it is possible to achive this goal by analytical functions. ROW_NUMBER analytical function is gets the number of like rownum  pseudocolumn. The difference is ROW_NUMBER gets partition based row numbers whereas rownum not.
I showed how to convert rows to columns in example below. The question is every class has at most 3 teacher. Give teacher names like TEACHER_1, TEACHER_2, TEACHER_3.


Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table classes;
Table dropped
SQL> create table classes( class_name varchar2(1), teacher_name varchar2(32) );
Table created
SQL> insert into classes values('A','Margaritta Gonson');
1 row inserted
SQL> insert into classes values('A','Eric Billy');
1 row inserted
SQL> insert into classes values('A','Faruq Omar');
1 row inserted
SQL> insert into classes values('B','Antouan Schensez');
1 row inserted
SQL> insert into classes values('B','Michael Gabriella');
1 row inserted
SQL> insert into classes values('C','Haluk Gumuskaya');
1 row inserted
SQL> insert into classes values('D','Gokhan Yavuz');
1 row inserted
SQL> insert into classes values('D','Banu Diri');
1 row inserted
SQL> /
1 row inserted
SQL> SELECT * FROM classes;
CLASS_NAME TEACHER_NAME
---------- --------------------------------
A          Margaritta Gonson
A          Eric Billy
A          Faruq Omar
B          Antouan Schensez
B          Michael Gabriella
C          Haluk Gumuskaya
D          Gokhan Yavuz
D          Banu Diri
D          Banu Diri
9 rows selected
SQL> SELECT cc.class_name, cc.TEACHER_1, cc.TEACHER_2, cc.TEACHER_3
  2    FROM (SELECT MAX(CASE
  3                       WHEN MOD(r, 3) = 1 THEN
  4                        c.teacher_name
  5                       ELSE
  6                        NULL
  7                     END) TEACHER_1,
  8                 MAX(CASE
  9                       WHEN MOD(r, 3) = 2 THEN
 10                        c.teacher_name
 11                       ELSE
 12                        NULL
 13                     END) TEACHER_2,
 14                 MAX(CASE
 15                       WHEN MOD(r, 3) = 0 THEN
 16                        c.teacher_name
 17                       ELSE
 18                        NULL
 19                     END) TEACHER_3,
 20                 c.class_name
 21            FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
 22                         class_name,
 23                         teacher_name
 24                    FROM classes) c
 25           GROUP BY c.class_name
 26           ORDER BY c.class_name) cc;
CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz
SQL> --
SQL> SELECT c.class_name,
  2         MAX(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
  3         MAX(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
  4         MAX(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   GROUP BY c.class_name
 10   ORDER BY c.class_name;
CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz
SQL> --
SQL> SELECT c.class_name,
  2         decode(r, 1, c.teacher_name, NULL) TEACHER_1,
  3         decode(r, 2, c.teacher_name, NULL) TEACHER_2,
  4         decode(r, 3, c.teacher_name, NULL) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   ORDER BY c.class_name;
CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                                                       
A                                           Faruq Omar                      
A                                                                            Margaritta Gonson
B          Antouan Schensez                                                 
B                                           Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                                                        
D                                           Banu Diri                       
D                                                                            Gokhan Yavuz
9 rows selected
SQL> --
SQL> SELECT c.class_name,
  2         MIN(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
  3         MIN(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
  4         MIN(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   GROUP BY c.class_name
 10   ORDER BY c.class_name;
CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz
SQL>
As you can see above you can do this wtih CASE-WHEN or DECODE . The point of interest is using MAX or MIN is not important. Main aim of this aggregation function is to grouping records. If you do not use MIN or MAX, all records are taken and every row has only one teacher in it.

Using INTERVAL DAY TO SECOND Data Type In Oracle

Oracle has INTERVAL data types to store days or years. Sometimes it helps you to do less work. I demonstrate a simple example to show the usage.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> DECLARE
  2    diff INTERVAL DAY TO SECOND;
  3 
  4    d1 DATE := SYSDATE;
  5    d2 DATE := SYSDATE +
  6               (6 + 1 / 24 * 3 + 1 / 24 / 60 * 7 + 1 / 24 / 60 / 60 * 9);
  7 
  8    t1 TIMESTAMP;
  9    t2 TIMESTAMP;
 10  BEGIN
 11    t1 := to_timestamp(d1);
 12    t2 := to_timestamp(d2);
 13 
 14    diff := t2 - t1;
 15 
 16    dbms_output.put_line('Time 2 is ' || to_char(t2));
 17    dbms_output.put_line('Time 1 is ' || to_char(t1));
 18 
 19    dbms_output.put_line('Total time difference is ' || diff);
 20 
 21    dbms_output.put_line('Details:');
 22    dbms_output.put_line('Day is    ' || extract(DAY    FROM diff));
 23    dbms_output.put_line('Hour  is  ' || extract(HOUR   FROM diff));
 24    dbms_output.put_line('Minute is ' || extract(MINUTE FROM diff));
 25    dbms_output.put_line('Second is ' || extract(SECOND FROM diff));
 26  END;
 27  /
Time 2 is 29/09/2006 23:50:39,000000
Time 1 is 23/09/2006 20:43:30,000000
Total time difference is +06 03:07:09.000000
Details:
Day is    6
Hour  is  3
Minute is 7
Second is 9
PL/SQL procedure successfully completed
SQL>

Dynamically Executing Schema Functions And Procedures In Oracle

Oracle has powerful commands to execute SQL commands dynamically. It is possible to use DBMS_SQL package or EXECUTE IMMEDIATE command. I demonstrate a simple example to show how to call a function or procedure in other schema dynamically.
Below there is a function get_hello which defined in SYS schema. I call this function in HR schema by giving name of schema and function name

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE FUNCTION get_hello(lang IN VARCHAR2) RETURN VARCHAR2 IS
  2  BEGIN
  3    IF upper(lang) = 'EN' THEN
  4      RETURN 'Hello';
  5    ELSIF upper(lang) = 'TR' THEN
  6      RETURN 'Merhaba';
  7    ELSE
  8      RETURN 'Hello';
  9    END IF;
 10  END;
 11  /
Function created
SQL>
SQL> DECLARE
  2    lang VARCHAR2(2) := 'Tr';
  3  BEGIN
  4    dbms_output.put_line(get_hello(lang));
  5  END;
  6  /
Merhaba
PL/SQL procedure successfully completed
SQL> grant execute on get_hello to hr;
Grant succeeded
SQL> conn hr/hr
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    lang VARCHAR2(2) := 'Tr';
  3  BEGIN
  4    dbms_output.put_line(sys.get_hello(lang));
  5  END;
  6  /
Merhaba
PL/SQL procedure successfully completed
SQL>
SQL> DECLARE
  2    lang  VARCHAR2(2) := 'Tr';
  3    hello VARCHAR2(16);
  4  BEGIN
  5    EXECUTE IMMEDIATE 'select sys.get_hello(:lang) from dual'
  6      INTO hello
  7      USING lang;
  8    dbms_output.put_line(hello);
  9  END;
 10  /
Merhaba
PL/SQL procedure successfully completed
SQL>
SQL> CREATE OR REPLACE FUNCTION generic_get_hello(lang          IN VARCHAR2,
  2                                               schema_name   IN VARCHAR2,
  3                                               function_name IN VARCHAR2)
  4    RETURN VARCHAR2 IS
  5    res VARCHAR2(16);
  6  BEGIN
  7    EXECUTE IMMEDIATE 'select ' || schema_name || '.' || function_name ||
  8                      '(:lang) from dual'
  9      INTO res
 10      USING lang;
 11    RETURN res;
 12  END;
 13  /
Function created
SQL>
SQL> BEGIN
  2    dbms_output.put_line(generic_get_hello('en','sys','get_hello') );
  3  END;
  4  /
Hello
PL/SQL procedure successfully completed
SQL>

Aggregation Functions and NO_DATA_FOUND Exception In Oracle

here are some important points when using aggregation functions. Although you have no data, this functions(MAX, MIN etc.) return  row and if you add NO_DATA_FOUND exception, exception block never executes.

Aggregation functions al least return one record when your table is empty except

• when you use HAVING clause
• when you use GROUP BY expression

Otherwise they return one NULL record.


I demonstrate a small example to make clear:

DECLARE
a VARCHAR2(12) := '';
b NUMBER;
BEGIN

SELECT COUNT(a.dummy)
INTO b
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('COUNT(a.dummy) without MAX is ' || b);

SELECT COUNT(*)
INTO b
FROM (SELECT MAX(a.dummy)
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T');
dbms_output.put_line('COUNT(a.dummy) with MAX is ' || b);

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('MAX(a.dummy)[No HAVING, No GROUP BY] passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) [No HAVING, No GROUP BY]...');
END;

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
HAVING MAX(a.dummy) = 'T';
dbms_output.put_line('MAX(a.dummy) with HAVING passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) with HAVING...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T'
GROUP BY a.dummy;
dbms_output.put_line('a.dummy with group by passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy with group by...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('a.dummy passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy...');
END;

END;




The output is

COUNT(a.dummy) without MAX is 0
COUNT(a.dummy) with MAX is 1
MAX(a.dummy)[No HAVING, No GROUP BY] passed..
no_data_found exception in MAX(a.dummy) with HAVING...
no_data_found exception in a.dummy  with group by...
no_data_found exception in a.dummy...

CHAR and VARCHAR Diffrences In Oracle

CHAR and VARCHAR types are not the same whereas some thinks they are. When you create a CHAR variable with some size and you do not fill the variable completely, Oracle fills the empty slots with blank characters. So if you want to compare a CHAR string with NULL you can confuse. Follow the ex:

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

SQL>
SQL> DECLARE
  2    char_string    CHAR(5) := '';
  3    varchar_string VARCHAR(5) := '';
  4  BEGIN
  5 
  6    IF char_string IS NULL THEN
  7      dbms_output.put_line('char_string is null');
  8    END IF;
  9 
 10    IF varchar_string IS NULL THEN
 11      dbms_output.put_line('varchar_string is null');
 12    END IF;
 13 
 14    IF rtrim(char_string) IS NULL THEN
 15      dbms_output.put_line('rtrim(char_string) is null');
 16    END IF;
 17 
 18  END;
 19  /

varchar_string is null
rtrim(char_string) is null

PL/SQL procedure successfully completed

SQL>

Storing Collections In Oracle Database

Oracle nin SQL dili olan PL/SQL'de birçok özellik bulunmaktadır. Bunlardan biri de collection'lardır. En temel anlamda collection, dizi veya küme demektir. PL/SQL ile oluşturulan collectionları Oracle üzerinde nested table şeklinde saklayabilirsiniz. Bu, varolan bir tablo içinde başka bir tablo oluşturma anlamına gelmektedir. İsterseniz bu tabloyu, veritabanı içinde ayrı bir yerde isterseniz de o tablo içinde oluşturabilirsiniz.

Collection tiplerini veritabanında tutmak yerine ayrı bir tablo yapıp tutmak da isteyebilirsiniz. Bu, birçok programcının yaptığı durumdur. Bu şekilde DML işlemleirini daha zahmetsiz halledersiniz. Collection tiplerinin faydası, içinde tuttuğunuz verinin düzenini sağlamasıdır. Yani collection içinde ilk elemanınız neyse her zaman ilk elemanınız o olacaktır. Bundan başka collection tiplerinin single-statement fetching ile alınıp daha hızlı işlenceği de belirtilebilir.

Sonuç olarak bunu kullanmak veya kullanmamak sizin elinizde. PL/SQL'in bunu desteklediğini bilmeniz bile size fayda sağlayacaktır.


Aşağıda bir collection tipinin veritabanında saklanması olayının örnek bir senaryosu bulunmaktadır. Kod bloğu, bazı özelliker içermesi bakımından önemlidir:

drop işlemleri:
drop type employee_tab;
drop type employee_obj;
drop table company;

Nesne oluşturulması
CREATE OR REPLACE TYPE employee_obj IS OBJECT
(
  full_name       VARCHAR2(64),
  department_name VARCHAR2(32),
  job_name        VARCHAR2(32)
);  

Nesne dizisi(collection) oluşturulması
CREATE OR REPLACE TYPE employee_tab IS TABLE OF employee_obj;

Collection tipinde bir kolonu bulunan tablonun oluşturulması. Bu collection veritaanı içinde ayrı bir yerde saklanacaktır.
create table company( id number, open_date date, employees employee_tab)
nested table employees store as employees_nt;

Saklandığının gösterilmesi
SELECT * FROM user_objects WHERE object_name = 'EMPLOYEES_NT';

Ekleme işleminin yapılması. Normal ekleme şeklinde değil, collection ları kabul edecek şekilde eklenme
INSERT INTO company
VALUES
  (1,
   SYSDATE,
   employee_tab(employee_obj('Anrew Kill', 'HR', 'HR Director'),
                employee_obj('Maria Born', 'HR', 'HR Asistant'),
                employee_obj('Ted Borry', 'IT', 'IT Manager')));
INSERT INTO company
VALUES
  (2,
   SYSDATE,
   employee_tab(employee_obj('Mariana Polii', 'IT', 'IT Director')));

Tablonun select edilmesi
SELECT employees FROM company WHERE id = 2;
--Mariana Polii    IT    IT Director

Değişiklik yapılması
DECLARE
  ind            NUMBER;
  employees_list employee_tab;
  CURSOR employees_cur IS
    SELECT employees FROM company WHERE id = 2;
BEGIN
  OPEN employees_cur;
  FETCH employees_cur
    INTO employees_list;--Single-statament assignment
  CLOSE employees_cur;

  ind := employees_list.FIRST;--ilk elemanın indisi
  WHILE ind IS NOT NULL LOOP--elemanlar bitinceye kadar
    IF employees_list(ind).department_name = 'IT' THEN
      employees_list(ind).department_name := 'Information Tech';
    END IF;
    ind := employees_list.NEXT(ind);--sonraki elemanın indisi
  END LOOP;
  UPDATE company SET employees = employees_list WHERE id = 2;--güncelleme
END;

Değişikliğin kontrol edilmesi
SELECT employees FROM company WHERE id = 2;
--Mariana Polii    Information Tech    IT Director

Propagation Of Exceptions In PL/SQL

Birçok dilde olduğu gibi PL/SQL'de de exception handling üzerinde dikkatle durulması gereken bir olaydır. Bu hem son kullanıcıları hem de uygulama geliştiricileri yakından ilgilendirir. Son kullanıcıya uygun mesajın gösterilmesi, hata içeriğine dair detaylı bilgilerin uygulama geliştiricilere bildirilmesi gerekir.

Birçok programlama dili exception handling i try catch blokları ile aşmaya çalışır. PL/SQL'de bu durum için exception blokları yazılır, hangi exception oluşmuşsa ona göre reaksiyon alınması sağlanır. Oluşan exception en iç bloktan en dış bloğa doğru fırlatılır. İlk bulunan exception bloğu içinde bu exception unun handle edilip edilmediğine bakılır. Eğer o exception için bir mekanizma yoksa ve global(others) bir mekanizma da tanımlı değilse bir üst bloğa doğru hareket eder. Eğer bir yerde handle edildiyse, ondan sonraki bloktan akış devam eder.

Aşağıda bu durumu modelleyen bir senaryo bulunmaktadır. İçiçe bulunan 4 blok içinden exceptionlar oluşturulmuş, bunların fırlatılma durumları gözlenmiştir.


DECLARE
  custom_exc EXCEPTION;
  i NUMBER;
BEGIN
  dbms_output.put_line('Program Start');

  <>
  BEGIN
    dbms_output.put_line('<>Start');
 
    <>
    BEGIN
      dbms_output.put_line('    <>Start');
   
      <>
      BEGIN
        dbms_output.put_line('        <>Just Before custom_exc');
        RAISE custom_exc;
        dbms_output.put_line('        <>Just After custom_exc');
      EXCEPTION
        WHEN zero_divide THEN
          dbms_output.put_line('        <>Divide by zero exception occured!!!');
        WHEN OTHERS THEN
          dbms_output.put_line('        <>Others exception occured!!!');
      END inner_in_label;
   
      dbms_output.put_line('    <>Just Before zero_divide');
      i := 3 / 0;
      dbms_output.put_line('    <>Just After zero_divide');
      dbms_output.put_line('    <>End');
   
    EXCEPTION
      WHEN custom_exc THEN
        dbms_output.put_line('    <>custom_exc exception occured!!!');
      WHEN zero_divide THEN
        dbms_output.put_line('    <>zero_divide');
     
    END inner_label;
 
    dbms_output.put_line('<>End');
 
  END outer_label;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Others exception occured!!!');
 
END;

Çıktı ise şöyledir:

Program Start
<>Start
    <>Start
        <>Just Before custom_exc
        <>Others exception occured!!!
    <>Just Before zero_divide
    <>zero_divide
<>End

SERIALLY_REUSABLE Pragma On Creating Packages In Oracle

Oracle ile package oluştururken kullanılan bir pragmadır SERIALLY_REUSABLE. Normal şartlarda bir package ile işlem yaparken, o session a ait package veriler UGA içinde oluşturulur. Ve o session için ilgili paket memory içinde tutulur. Bu nedenle bir package daha sonraki çağrılmlarda initialize edilmez ve ilgili bazı değerler baştan yenilenmez. Eğer memory kullanımını daha etkin hale getirmek, her çağrımda packege içindeki değerleri yeniden başlatmak isterseniz bu pragmayı kullanabilirsiniz. Aksi takdirde bir package bir session günlerce de kalabilir.

Bu pragma kullanılmadan bir package içindeki bir cursor bir session içinde kapanmadan daha sonraki çağrımlarda kullanılabilir. Aşağıda bunun bir örneği bulunmaktadır. incerement fonksiyonu bir session içinde çağrımlarda değerini korurken, bu pragma ile değerini korumayıp tekrar başlatılmaktadır. Detaylar için otn e bakabilirsiniz.

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


SQL> set serveroutput on;
SQL> drop package p;

Package dropped

SQL>
SQL> CREATE OR REPLACE PACKAGE p IS
  2    i NUMBER := 0;
  3    PROCEDURE increment;
  4  END p;
  5  /

Package created

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY p IS
  2    PROCEDURE increment IS
  3    BEGIN
  4      i := i + 1;
  5      dbms_output.put_line('i = ' || i);
  6    END;
  7 
  8  END p;
  9  /

Package body created

SQL> drop package p2;

Package dropped

SQL>
SQL> CREATE OR REPLACE PACKAGE p2 IS
  2    PRAGMA SERIALLY_REUSABLE;
  3    i NUMBER := 0;
  4    PROCEDURE increment;
  5  END p2;
  6  /

Package created

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY p2 IS
  2    PRAGMA SERIALLY_REUSABLE;
  3    PROCEDURE increment IS
  4    BEGIN
  5      i := i + 1;
  6      dbms_output.put_line('i = ' || i);
  7    END;
  8  END p2;
  9  /

Package body created

SQL> exec p.increment;

i = 1

PL/SQL procedure successfully completed

SQL> exec p.increment;

i = 2

PL/SQL procedure successfully completed

SQL> exec p2.increment;

i = 1

PL/SQL procedure successfully completed

SQL> exec p2.increment;

i = 1

PL/SQL procedure successfully completed

SQL>

Deterministic Functions In Oracle

Oracle bulunan özelliklerden biri de deterministic fonksiyonlardır. Bir fonksiyonun deterministic olabilmesi için girilen bir değere karşı daima aynı sonucu üretmesidir. Sonucun sysdate e bağımlı bulunan bir fonksiyon deterministic değildir. Deterministic fonksiyonlar, function based index oluştururken gereklidirler. Aksi takdirde oluşturamazsınız. Deterministic fonksiyonların Oracle tarafında cache lendiği söylenmesine rağmen ben bunu gözlemleyemedim testlerimde. Aynı soruyu forums.oracle da sormama rağmen, oracle nin deterministik fonksiyonlar üzerinde nasıl bir yol izlediği konusunda net bir sonuca ulaşamadım.

Jonathan Lewis'in bu konuda yazmış olduğu bir makalede de bu durumdan bahsedilmiş. İsteyenler wiki sayfasına erişebilirler.

Aşağıda  James R Padfield in bu konuda yaptığı örnekte cache den söz edilebiliyor.

SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE FUNCTION format_deptno (
2 p_deptno IN VARCHAR2)
3 RETURN VARCHAR2
4 IS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE ('Department: ' || p_deptno);
7 RETURN 'Department: ' || p_deptno;
8 END;
9 /

Function created.

SQL> SELECT NULL
2 FROM emp
3 WHERE format_deptno (deptno) IS NULL;

no rows selected

SQL> EXEC NULL;
Department: 30
Department: 20
Department: 30
Department: 30
Department: 20
Department: 30
Department: 30
Department: 10
Department: 20
Department: 10
Department: 30
Department: 20
Department: 30
Department: 20
Department: 10

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE FUNCTION format_deptno (
2 p_deptno IN VARCHAR2)
3 RETURN VARCHAR2 DETERMINISTIC
4 IS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE ('Department: ' || p_deptno);
7 RETURN 'Department: ' || p_deptno;
8 END;
9 /

Function created.

SQL> SELECT NULL
2 FROM emp
3 WHERE format_deptno (deptno) IS NULL;

no rows selected

SQL> EXEC NULL;
Department: 30
Department: 20
Department: 10

PL/SQL procedure successfully completed.

SQL>


Ama benim örneğimde bu cache i göremedim.
/*
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as HR
*/

CREATE OR REPLACE FUNCTION with_det_test(i IN NUMBER) RETURN NUMBER
DETERMINISTIC IS
BEGIN
dbms_output.put_line(i);
RETURN i;
END;


CREATE OR REPLACE FUNCTION without_det_test(i IN NUMBER) RETURN NUMBER IS
BEGIN
dbms_output.put_line(i);
RETURN i;
END;

DECLARE
a NUMBER;
i NUMBER := 1;
BEGIN
WHILE i < 10 LOOP
a := with_det_test( MOD(i, 2));
i := i + 1;
END LOOP;
END;
/*
1
0
1
0
1
0
1
0
1*/

DECLARE
a NUMBER;
i NUMBER := 1;
BEGIN
WHILE i < 10 LOOP
a := without_det_test(MOD(i, 2));
i := i + 1;
END LOOP;
END;
/*
1
0
1
0
1
0
1
0
1*/

Parameter Types For PL/SQL Program Blocks

PL/SQL parametreler genel olarak 3 türlüdür.IN, OUT ve INOUT. Programlama dillerinde bulunan pass by value ve pass by reference kavramları PL/SQL için de geçerlidir. IN olarak gelen parametreler referans olarak gelir, veri kopyalanmaz. Diğer iki durumda ise parametrelerin bir kopyası oluşturularak fonksiyona gönderilir.  Bu bakıma, boyutu yüksek veriler parametre olarak aktarılırken verinin bir kopyasının oluşturularak gönderileceği unutulmamalıdır. İstenirse NOCOPY ile bu verilerin kopyalanmaması sağlanmabilmesine rağmen birtakım kullanım kısıtları içermesi bakımından dikkat edilmesi gereklidir. Ayrıca IN ile gönderilen parametreler sadece okunabilirken, OUT ile gönderilen parametreler ise sadece yazılabilir durumdadır.



CREATE OR REPLACE PROCEDURE test(i IN NUMBER,    --Read ONLY,   Pass by ref
                                 k OUT NUMBER,   --WRITE ONLY,  Pass by Val + rereturn the value back
                                 m IN OUT NUMBER --Rad-WRITE,   Pass by Val + rereturn the value back
                                 ) IS
BEGIN

  dbms_output.put_line('In procedure:');
  dbms_output.put_line('i = ' || i);
  dbms_output.put_line('k = ' || k);--It will not contain any value. Write ONLY
  dbms_output.put_line('m = ' || m);

  --i := 100;--Can not modifiy READ ONLY
  k := 1000;
  m := 10000;
END test;



DECLARE
  ii NUMBER := 200;
  kk NUMBER := 2000;
  mm NUMBER := 20000;
BEGIN
  dbms_output.put_line('Before procedure:');
  dbms_output.put_line('i = ' || ii);
  dbms_output.put_line('k = ' || kk);
  dbms_output.put_line('m = ' || mm);

  test(ii, kk, mm);

  dbms_output.put_line('After procedure:');
  dbms_output.put_line('i = ' || ii);
  dbms_output.put_line('k = ' || kk);
  dbms_output.put_line('m = ' || mm);

END;

Çıktı ise


  Before procedure:
  i = 200
  k = 2000
  m = 20000
  In procedure:
  i = 200
  k =
  m = 20000
  After procedure:
  i = 200
  k = 1000
  m = 10000
olacaktır.

Asteriks Character On Creating Views In Oracle

racle içinde bir view oluştururken * karakteri, kolon isimleri ile yer değiştirilerek kaydedilir. Bu nedenle view oluştururken * bırakıp, daha sonra tablo değişimlerinden etkilenmemesini sağlamak mümkün değildir. O nedenle view oluştururken bunu dikkate almak gerekir.

Aşağıda bunun bir örnek senaryosu bulunmaktadır.


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

SQL>
SQL> drop table tt;

Table dropped

SQL> drop view vtt;

View dropped

SQL> create table tt(x number);

Table created

SQL> create view vtt as select * from tt;

View created

SQL> select * from vtt;

         X
----------

SQL> select uv.text from user_views uv where uv.view_name = 'VTT';

TEXT
--------------------------------------------------------------------------------
select "X" from tt


SQL> alter table tt add(y number);

Table altered

SQL> select * from vtt;

         X
----------

SQL> select status from user_objects uo where uo.object_name = 'VTT';

STATUS
-------
VALID

SQL> alter table tt drop column x;

Table altered

SQL> select status from user_objects uo where uo.object_name = 'VTT';

STATUS
-------
INVALID

SQL>

V$FIXED_VIEW_DEFINITION View

Bu view içinde Oracle içinde bulunan v$ dinamik performans view larının açıklamalarını görebilirsiniz. v$ dinamik performans view ları Oracle her start edildiğinde sıfırlanırlar. Bundan başka bir de gv$ ile başlayan view lar da bulunur. Bunlar RAC yapısında çalışan Oracle için instance bilgilerini de içerir.

SELECT * FROM v$fixed_view_definition WHERE view_name = 'V$VERSION'
--VIEW_NAME     VIEW_DEFINITION
--V$VERSION     select BANNER from GV$VERSION where inst_id = USERENV('Instance')
-----
SELECT BANNER FROM GV$VERSION WHERE inst_id = USERENV('Instance')
--
SELECT * FROM GV$VERSION
--
SELECT COUNT(*) FROM V$FIXED_VIEW_DEFINITION
--770

Processing XML Data On Oracle Database

Oracle, bir programlama dilinden beklenebilecek bütün özellikleri beraberinde getirmiştir 10G sürümü ile. Bunlardan biri de XML verileri işlemedir. XML yakın zamanlarda kullanılan bir standart haline gelmesi, dağıtık uygulamalar tarafından kullanılması, düz metinden oluşması popülaritesini arttırmaktadır. Oracle ile XML işlerken elde ettiğim en temel fonksiyonları, bu alanda bir başlangıç yapmak için inceleyebilirsiniz.


Öncelikle bir tablo oluşturalım.
create table students(id number, full_name varchar2(32));

İçine test için veri girelim.
BEGIN
INSERT INTO students VALUES (1889, 'Roberto Carlos');
INSERT INTO students VALUES (1890, 'Arsene Lupin');
COMMIT;
END;




Sorgular, içinde kullanılan fonksiyonların anlamları ve çıktıları belirtilmiştir.
--XMLAGG : Takes parameter an xmltype and aggregates
SELECT XMLELEMENT("STUDENTS", XMLAGG(XMLELEMENT("STUDENT", s.full_name)))
FROM students s
/*

    Roberto Carlos
    Arsene Lupin
*/


--XMLFOREST parametre aldığı değerleri xml e dönüştürür.
SELECT XMLELEMENT("STUDENTS", XMLAGG(XMLELEMENT("STUDENT", XMLFOREST(s.ID, s.full_name))))
FROM students s
/*

    
        1889
        Roberto Carlos
    
    
        1890
        Arsene Lupin
    
*/



SELECT XMLELEMENT("student", XMLATTRIBUTES(ID AS "id"), XMLELEMENT("name", full_name))
FROM students
/*

         Roberto Carlos

         Arsene Lupin
*/

--Sub Query + function
SELECT XMLELEMENT("student", xmlattributes(TO_CHAR((SELECT SYSDATE FROM DUAL), 'DD.MM.YYYY HH24:MI:SS') AS
"xml_create_date"), XMLELEMENT("name", xmlattributes(ID AS "id"), full_name))
FROM students
/*

         Roberto Carlos

         Arsene Lupin
*/

--
SELECT XMLFOREST(id, full_name) FROM students
/*
1889
Roberto Carlos
1890
Arsene Lupin
*/

Oracle Date Formats RR and YY

RR ve YY format belirteçlerini kullanırken dikkat etmek gerekir. Örneğin 2006 yılında 7 yıl önceki bir değeri tarihe dönüştürmek istiyorsunuz. Bunu YY formatında yaparsanız 7 yıl önce yerine 93 yıl ileriye gidersiniz. Aynı yüzyılda olmadığınız anda(1999 ile 2006 aynı yüzyılda RR formatına göre; 2006 2099 aynı yüzyılda YY formatına göre)

Bunu aşağıdaki örnekte görebilirsiniz:



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


SQL>
SQL> select to_date('11.09.96','DD.MM.YY') date_ from dual;

DATE_
-----------
11.09.2096

SQL> select to_date('11.09.96','DD.MM.RR')date_ from dual;

DATE_
-----------
11.09.1996

SQL> select to_date('11.09.06','DD.MM.YY') date_ from dual;

DATE_
-----------
11.09.2006

SQL> select to_date('11.09.06','DD.MM.RR') date_ from dual;

DATE_
-----------
11.09.2006

SQL>

Performance Anlyzes On Different Queries That Returns The Same Results

Kimi zaman bir işi yapan birden fazla yol bulunabilir. Sonuca ulaşmanız, yönteminizin en doğru yol olduğunu göstermez. Bunun bir örnek uygulamasını aşağıdaki sorguda görebilirsiniz.

Aynı sonucu üreten iki sorgudan biri regexp_like ile diğeri ise like ile yapılmış. Aralarındaki fark yaklaşık 7 kat...



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


SQL> set timing on
SQL>
SQL> SELECT *
  2    FROM user_source
  3   WHERE regexp_like(text, '(INSERT INTO |UPDATE )PARAMETER_VALUE ')
  4  /

NAME                           TYPE               LINE TEXT
------------------------------ ------------ ---------- ----------------
......

19 rows selected

Executed in 29,812 seconds

SQL>
SQL> SELECT *
  2    FROM user_source
  3   WHERE text LIKE '%UPDATE PARAMETER_VALUE %'
  4      OR text LIKE '%INSERT INTO PARAMETER_VALUE %'
  5  /

NAME                           TYPE               LINE TEXT
------------------------------ ------------ ---------- ----------------
............

19 rows selected

Executed in 4,219 seconds

SQL>

Calling External Functions Inside PL/SQL

Birçok yerde duymuşsunuzdur PL/SQL içinden işletim sistemine ait veya bir C veya Java program bloklarının çağırılabiliyor olduğunu. En basitinden işletim sistemine ait herhangi bir komutu çağırabilirseniz eğer, istediğiniz harici fıonksiyonları işletim sisteminin anlayacağı şekilde belirtip harici dosyayı çalıştırabilirsiniz. Harici fonksiyonlar, kimi zaman uygulamalarınız için gerekli olabilir. Nitekim bir defasında bana lazım olmuştu.(Ben java ile çözmüştüm.)
forums.oracle ve otn den edindiğim bilgiler doğrultusunda bu işlemin nasıl yapılabileceğini kısaca belirtmeye çalışacağım.

• Bir java class ı yazıp bunun içinden shell komutları çalıştırmak. Bu java source unu da bir PL/SQL ile sarmallayıp işlemi gerçekleştirmek. Aşağıda java tarafında bu işi gerçekleyen komut bulunmaktadır. Tam bir örnek için tıklayınız...

   Runtime rt = Runtime.getRuntime();
   Process p = rt.exec( commandText );

• dbms_scheduler paketi ile. Bu sayede sh uzantılı bir dosya oluşturup yapacağınız işlemi bu jobu çalıştırarak yapabilirsiniz. Job ı create ederken job_type => 'executable' parametresini vermek gerekiyor.

• DBMS_PIPE paketi ile. Bu paket içine ilgili C fonksiyonunu yazıp çağırabilirsiniz. C içinden system() bıilt-in fonksiyonunu çağırarak işleminizi yapabilirsiniz. Daha sonra oracle içinden bir LIBRARY oluşturup bu library yi kullanabilirsiniz.

Ama şunu unutmamak gerekir ki, işletim sistemine ait komutların çağırılması güvenlik açısından sizde sıkıntı oluşturabilir. Çünkü işletim sisteminde komut çağırabilme hakkı, kimi art niyetli kişiler tarafından kullanılabilir.

Using DBMS_ERRORLOG Package

Oracle 10G ile birlikte gelen yeni paketlerden biridir DBMS_ERRORLOG.  Bu sayede yoğun işlemlerininizi, işlemleriniz kesilmeden yapabilirsiniz. Oluşan hataların ayrı bir yere aktarılması ile daha sonra oluşan hataları gözlemleyip, gerekli aksiyonları alabilirsiniz.

Aşağıda yaptığım örnekte 100000 kayıtlık bir insert işleminde olabilecek hatalardan dolayı bu paketin kullanılmasının avantajı belirtilmiştir.


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

SQL>
SQL> drop table error_log_test;

Table dropped

Executed in 0,016 seconds





Tablo Oluşturulur. İçine kayıt eklenir.

SQL> create table error_log_test( i number );

Table created

Executed in 0 seconds

SQL> DECLARE
  2    i NUMBER := 0;
  3  BEGIN
  4    FOR i IN 1 .. 100000 LOOP
  5      INSERT INTO error_log_test VALUES (i);
  6      IF MOD(i, 1000) = 0 THEN
  7        COMMIT;
  8      END IF;
  9    END LOOP;
 10 
 11  END;
 12  /

PL/SQL procedure successfully completed

Executed in 4,031 seconds

SQL> SELECT COUNT(*) FROM error_log_test;

  COUNT(*)
----------
    100000

Executed in 0,016 seconds





Kayıtların ekleneceği demo tablo oluşturulur.

SQL> drop table error_log_test_demo;

Table dropped

Executed in 0,219 seconds

SQL> drop table err$_error_log_test_demo;

drop table err$_error_log_test_demo

ORA-00942: table or view does not exist

SQL> create table error_log_test_demo( x number );

Table created

Executed in 0,016 seconds




Kısıtlar eklenir. Bu sayede hata oluşmasını sağlıyoruz.

SQL> alter table error_log_test_demo add constraint more_than_99990 check( x < 99990 );

Table altered

Executed in 0,015 seconds

SQL> alter table error_log_test_demo add constraint less_than_10 check( x > 10 );

Table altered

Executed in 0 seconds



dbms_errlog.create_error_log prosedürü ile hata esnasında gidilecek tablo oluşturulur.

SQL> BEGIN
  2    dbms_errlog.create_error_log('error_log_test_demo');
  3  END;
  4  /

PL/SQL procedure successfully completed

Executed in 0,016 seconds

SQL> describe err$_error_log_test_demo;
Name            Type           Nullable Default Comments
--------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER         Y                        
ORA_ERR_MESG$   VARCHAR2(2000) Y                        
ORA_ERR_ROWID$  UROWID(4000)   Y                        
ORA_ERR_OPTYP$  VARCHAR2(2)    Y                        
ORA_ERR_TAG$    VARCHAR2(2000) Y                        
X               VARCHAR2(4000) Y                        



dbms_errlog kullanılmadan ekleme işlemi yapılmaya çalışılır.

SQL> insert into error_log_test_demo select * from error_log_test;

insert into error_log_test_demo select * from error_log_test

ORA-02290: check constraint (HR.LESS_THAN_10) violated



dbms_errlog ile ekleme işlemi yapılır. Hatalı kayıtlar err$_error_log_test_demo tablosuna aktarılır.

SQL> INSERT INTO error_log_test_demo
  2    SELECT * FROM error_log_test log errors reject LIMIT unlimited;

99979 rows inserted

Executed in 5,953 seconds

SQL> SELECT COUNT(*) FROM error_log_test_demo;

  COUNT(*)
----------
     99979

Executed in 0,016 seconds



Eklenemeyen kayıtlar ve neden eklenemdikleri buradan görülebilir.

SQL> select ed.ora_err_mesg$, ed.x from err$_error_log_test_demo ed ;

ORA_ERR_MESG$                                                       X
------------------------------------------------------           ----
ORA-02290: check constraint (HR.LESS_THAN_10) violated              1
ORA-02290: check constraint (HR.LESS_THAN_10) violated              2
ORA-02290: check constraint (HR.LESS_THAN_10) violated              3
ORA-02290: check constraint (HR.LESS_THAN_10) violated              4
ORA-02290: check constraint (HR.LESS_THAN_10) violated              5
ORA-02290: check constraint (HR.LESS_THAN_10) violated                6
ORA-02290: check constraint (HR.LESS_THAN_10) violated                7
ORA-02290: check constraint (HR.LESS_THAN_10) violated                8
ORA-02290: check constraint (HR.LESS_THAN_10) violated                9
ORA-02290: check constraint (HR.LESS_THAN_10) violated               10
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99990
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99991
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99992
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99993
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99994
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99995
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99996
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99997
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99998
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99999
ORA-02290: check constraint (HR.MORE_THAN_99990) violated        100000

21 rows selected

Executed in 0,047 seconds

SQL>