Oracle ile Veri Şifreleme

Herkese Selam,

Bu yazıda Oracle’ın veri şifreleme desteğinden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

Oracle, yazılım geliştiricelere veri şifreleme konusunda 2 farklı paket sunuyor. Bunlar;

  • DBMS_CRYPTO (Oracle 10g ile geldi.)
  • DBMS_OBFUSCATION_TOOLKIT (Oracle 8i ile geldi.)

DBMS_CRYPTO daha güncel olması nedeni ile DBMS_OBFUSCATION_TOOLKIT’den daha fazla ve daha geniş özelliklere sahip. Aşağıda temel olarak bu 2 paket arasındaki farkları görebilirsiniz. 

DBMS_CRYPTO literatürdeki bir çok şifreleme ve hashleme algoritmalarını başarı ile desteklemekte. İhtiyacınız olan algoritmaları kolay bir şekilde seçerek istediğiniz veriyi şifreleyebilir/hashleyebilir veya şifrelenmiş bilgileri açabilirsiniz.

DBMS_CRYPTO Paketinin temel fonksiyonlarına aşağıdan göz atabilirsiniz.

Şimdi anlaşılması ve kullanılması kolay olan bu paket ile karmaşık bir ihtiyacı gideren bir örnek yapalım.

Senaryo: Kendisine parametre olarak gelen değeri DES (Cipher olarak CBC, Padding Olarak PCKS5 ayarları seçilmeli) algoritmasını kullanarak şifreleyip base64 formatında bir çıktı üreten şifreleme metodu yapalım? (Aynı yöntem ile şifrelenmiş veriyi açma işlemini yapacak bir metot daha yazalım.)

Cipher Mode : CBC

Padding: PCKS5

Şifreleme için kullanılacak key: 52AB32;^$!ER94988OPS3W21

Şifreleme için kullanılacak initialization vector (IV): TY54ABCX

Not: Initialization Vector şifreleme ve şifrelenmiş veriyi açmada zorunlu olan parametrelerden değil ancak biz örneği karmaşıklaştırmak için bu parametreyi de kullanıyoruz.

DBMS_CRYPTO paketinin ENCRYPT fonksiyonunun imzası;

DBMS_CRYPTO.ENCRYPT(
                            dst IN OUT NOCOPY BLOB,
                            src IN CLOB CHARACTER SET ANY_CS,
                            typ IN PLS_INTEGER,
                            KEY IN RAW,
                            iv  IN RAW DEFAULT NULL);

Kodu yazmadan önce şifrelemede kullanacağım KEY ve IV bilgisini hexadecimal’e çevirip bir tabloda saklamak istiyorum. Daha sonra Encryption ve Decryption adımlarında bu bilgileri yarattığım tablodan okuyacağım.

CREATE TABLE ALGPARAMETERS
(
  NAME   VARCHAR2(100 BYTE),
  VALUE  NVARCHAR2(100)
);

INSERT INTO ALGPARAMETERS
   SELECT 'key' NAME,
          RAWTOHEX ('52AB32;^$!ER94988OPS3W21') VALUE
     FROM DUAL
   UNION
   SELECT 'iv' NAME, RAWTOHEX ('TY54ABCX') VALUE FROM DUAL;

COMMIT;

SELECT * FROM ALGPARAMETERS;


Şimdi şifrelemeyi yapacak fonksiyonumuzu yazalım.

CREATE OR REPLACE FUNCTION F_ENCRYPT (p_input VARCHAR2)
   RETURN VARCHAR2
AS
   v_encrypted_raw     RAW (2000);
   v_key               RAW (320);
   v_encryption_type   PLS_INTEGER
      :=   DBMS_CRYPTO.DES_CBC_PKCS5;
   v_iv                RAW (320);
BEGIN
   SELECT VALUE
     INTO v_key
     FROM algparameters
    WHERE name = 'key';

   SELECT VALUE
     INTO v_iv
     FROM algparameters
    WHERE name = 'iv';

   v_encrypted_raw :=
      DBMS_CRYPTO.encrypt (src   => UTL_I18N.STRING_TO_RAW (p_input, 'AL32UTF8'),
                           typ   => v_encryption_type,
                           key   => v_key,
                           iv    => v_iv);
   RETURN UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.base64_encode (v_encrypted_raw));
END;

Şimdi şifrelenmiş veriyi açan fonksiyonumuzu yazalım.

DBMS_CRYPTO paketinin DECRYPT fonksiyonunun imzası;

DBMS_CRYPTO.DECRYPT(
                             dst IN OUT NOCOPY BLOB,
                             src IN            BLOB,
                             typ IN            PLS_INTEGER,
                             key IN            RAW,
                             iv  IN            RAW          DEFAULT NULL);  

 

CREATE OR REPLACE FUNCTION F_DECRYPT (p_input VARCHAR2)
   RETURN VARCHAR2
AS
   v_decrypted_raw     RAW (2000);
   v_key               RAW (320);
   v_encryption_type   PLS_INTEGER := DBMS_CRYPTO.DES_CBC_PKCS5;
   v_iv                RAW (320);
BEGIN
   SELECT VALUE
     INTO v_key
     FROM algparameters
    WHERE name = 'key';

   SELECT VALUE
     INTO v_iv
     FROM algparameters
    WHERE name = 'iv';


   v_decrypted_raw :=
      DBMS_CRYPTO.DECRYPT (
         src   => UTL_ENCODE.base64_decode (UTL_RAW.CAST_TO_RAW (p_input)),
         typ   => v_encryption_type,
         key   => v_key,
         iv    => v_iv);


   RETURN UTL_I18N.RAW_TO_CHAR (v_decrypted_raw, 'AL32UTF8');
END;

Şifreleme işlemini gerçekleştiren ve şifrelenmiş bilgiyi açan fonksiyonlarımızı yazdık. Sıra geldi bu fonksiyonları test etmeye.

SELECT 'TEST123TEST' INPUT, 
        F_ENCRYPT('TEST123TEST') ENCRYPTED_RESULT,
        F_DECRYPT(F_ENCRYPT('TEST123TEST')) DECRYPT_RESULT 
FROM DUAL;

Sonuçtan da görüldüğü gibi, ihtiyaçlarımız doğrultusunda şifreleme ve şifre açma işlemlerini başarılı ve hızlı bir şekilde yapabildik.

DBMS_CRYPTO paketi ile ilgili daha detaylı bilgiye erişmek için aşağıdaki linki takip edip ihtiyaçlarınıza uygun çözümü hızlıca uygulayabilirsiniz.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1003081

Advertisements
Posted in Oracle, Uncategorized | Tagged , , , , | Leave a comment

Oracle Data Mining (DBMS_DATA_MINING) ile Clustering Problemi Çözmek

Herkese Selam,

Bu yazıda Oracle Data Mining ile bir clustering problemi çözeceğim. Umarım farkındalık anlamında faydalı bir yazı olur.

Veri Bilimi ve Makine Öğrenmesi gibi konular günümüzde hakkında çokça söz ettiren başlıkların başında gelmekte. Öğrenmesi ve uygulaması uzmanlık gerektiren bu başlıklar için adreslenen problemleri ve yöntemleri günümüzde bir çok ürün veya yazılım ile gerçekleyebilmekteyiz. Oracle’da bu başlıklar altındaki problemlerin çözümüne ilişkin yöntem ve algoritmaları DBMS_DATA_MINING paketi ile desteklemekte.

DBMS_DATA_MINING paketi ile Custering, Classification, Regression, Anomaly Detection, Feature Extraction ve Assosication gibi modeller kurup, verimizi bu modellerden geçirip yorumlayabiliyoruz. Bu modellerden elde ettiğimiz sonuçları ise business kurgularımızda input olarak kullanabilmekteyiz.

DBMS_DATA_MINING paketi Oracle veritabanı üzerinde default kurulu olarak gelmiyor. Bu nedenle bu destekten faydalanabilmek için öncelikle bu paketi kurmak gerekiyor. Aşağıdaki linki takip ederek veritabanınıza Oracle Data Mining’ı kurabilirsiniz.

https://docs.oracle.com/cd/E11882_01/datamine.112/e16807/install_odm.htm#DMADM117

Oracle Data Mining paketi kurulumu ile beraber gelen 3 yeni dictionary tablomuz bulunmakta.

SELECT * FROM  ALL_MINING_MODELS;

SELECT * FROM ALL_MINING_MODEL_SETTINGS;

SELECT * FROM ALL_MINING_MODEL_ATTRIBUTES;

ALL_MINING_MODELS tablosunda Oracle’ın bu alt yapı ile bize sunmuş olduğu tüm modeller listesi ve modeller hakkında bilgiler bulunuyor.

ALL_MINING_MODEL_SETTINGS ve ALL_MINING_MODEL_ATTRIBUTES tablolarında ise bu modeller ile ilgili paramtereler ve spesifik detaylar bulunmakta.

Şimdi örnek bir kümeleme problemi çözmek için anlaşılması kolay bir veri seti hazırlayalım.

CREATE TABLE KMEANSDATA
(
   INSTANCE   NUMBER,
   X_AXIS     NUMBER,
   Y_AXIS     NUMBER
);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (1, 3, 5);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (2, 2, 1);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (3, 1, 1);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (4, 4, 3);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (5, 6, 1);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (6, 7, 5);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (7, 4, 4);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (8, 5, 6);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (9, 3, 8);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (10, 5, 6);

COMMIT;

Problemin anlaşılması adına basit bir veri seti ürettim. Amacım bu veri seti içerisinde bir birine yakın olanları K-Means algoritması ile kümelemek .

Öncelikle kısaca K-Means algoritması hakkında bilgi vermek istiyorum. K-Means verilen veri seti üzerindeki özelliklere bakarak bir birine benzeyen verileri aynı küme içerisinde toplayarak bize verilen veri setindeki benzerlikleri gösteren bir algoritma. K algoritmanın bir parametresi ve veri setinin kaç kümeye bölüneceğini bize söylüyor.

K-Means algoritmasının detaylı anlatımı için aşağıdaki linkler takip edilebilir.

https://sites.google.com/site/dataclusteringalgorithms/k-means-clustering-algorithm

https://docs.oracle.com/cd/E11882_01/datamine.112/e16808/algo_kmeans.htm#DMCON238

Data setimde toplamda 10 adet kayıt bulunmakta. Ben bu 10 kayıdı 3 farklı kümede toplamak istiyorum bu yüzden K değerimi 3 olarak seçeceğim. İlk olarak algoritmamın default parametre ayarlarına bakıyorum.

SELECT *
  FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
 WHERE setting_name LIKE 'KMNS%' OR setting_name like '%CLUS_NUM_CLUSTERS%';;

Evet default ayarlardan görüleceği üzere algoritmanın default K parametresi (CLUS_NUM_CLUSTERS) 10 olarak ayarlanmış. Bunu kendi problemimi çözmek için 3 olarak ayarlayacağım ve bununiçin yeni bir tablo yaratıyorum ve ilgili parametreyi güncelliyorum.

CREATE TABLE kmeanssettings
AS
   SELECT *
     FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
    WHERE setting_name LIKE 'KMNS%' OR setting_name like '%CLUS_NUM_CLUSTERS%';
    
UPDATE kmeanssettings
   SET setting_value = 3
 WHERE setting_name = 'CLUS_NUM_CLUSTERS';

UPDATE kmeanssettings
   SET setting_value = 10
 WHERE setting_name = 'KMNS_ITERATIONS';

COMMIT;

SELECT * FROM kmeanssettings;

Evet görüldüğü üzere ilgili parametreyi güncelledim. Şimdi bu ayarları kullanarak modelimi oluşturacağım.

 

 

BEGIN
   DBMS_DATA_MINING.CREATE_MODEL (
      model_name            => 'K_MEANNS_MODEL',
      mining_function       => DBMS_DATA_MINING.CLUSTERING,
      data_table_name       => 'KMEANSDATA',
      case_id_column_name   => 'INSTANCE',
      target_column_name    => NULL,
      settings_table_name   => 'KMEANSSETTINGS');
END;

Kodumuz çalıştı ve modelimizi belirlediğimiz güncel ayarlara göre oluşturduk. Şimdi bu modele veri setimizi verelim ve kümeleme sonuçlarını inceleyelim.

BEGIN
   DBMS_DATA_MINING.APPLY (model_name            => 'K_MEANNS_MODEL',
                           data_table_name       => 'KMEANSDATA',
                           case_id_column_name   => 'INSTANCE',
                           result_table_name     => 'KMEANS_RESULT');
END;

Şimdi sonuçlarımızı parametre olarak verdiğimiz tablodan inceleyelim.

SELECT * FROM KMEANS_RESULT;

Sonuç kümemize baktığımızda toplam 30 kayıt görüyoruz. Bunun nedeni, toplam veri setimizde 10 kayıt vardı ve biz algoritmaya küme sayısı olarak 3 değerini verdik. Bu durumda algoritma çıktısında her bir kaydın, farklı her küme için ayrı ayrı olasılığı hesaplandı (3X10 = 30). Bu noktadan sonra tam sonucu görebilmek için her bir elemanının maksimum olasılıklı kaydını bulup listeleyelim.

SELECT t1.instance,
       t1.CLUSTER_ID,
       t1.probability,
       t2.x_axis,
       t2.y_axis
  FROM (SELECT INSTANCE, CLUSTER_ID, PROBABILITY
          FROM (SELECT T.*,
                       MAX (PROBABILITY)
                       OVER (PARTITION BY INSTANCE ORDER BY PROBABILITY DESC)
                          MAXP
                  FROM KMEANS_RESULT T)
         WHERE MAXP = PROBABILITY) t1,
       KMEANSDATA t2
 WHERE t1.instance = t2.instance order by cluster_id;

Veri setimizin 3 farklı kümeye dağıldığını görüyoruz. Son olarak grafik üzerinden son duruma bakalım.

Grafik’ten de anlaşılacağı üzere modelimiz verimizi K-MEANS algoritmasına göre mantıklı bir şekilde kümeledi.

Posted in Oracle, Uncategorized | Tagged , , , , , , , , | Leave a comment

Oracle Hakkında Faydalı Linkler

Herkese Selamlar,

Bir süredir, üniversite öğrencisi olan ve Oracle veritabanı öğrenmek isteyen arkadaşlardan nasıl başlamalıyız? tadında sorular alıyorum. Bu soruları takiben nasıl, nereden ve hangi kaynakları kullanabiliriz gibi sorularda geliyor. Bu sorulara istinaden Oracle kaynaklarını içeren bir liste hazırladım. Bu liste yeni başlayan veya kendisini bu teknolojiler üzerinden geliştirecek arkadaşlara ışık tutar diye düşünüyorum. Linkler ve aklınıza takılan farklı sorular için çekinmeden iletişime geçebilirsiniz.

ORACLE İLE İLGİLİ FAYDALI LİNKLER

Oracle Database Documentation

–        http://docs.oracle.com/cd/E11882_01/index.htm

Database Architecture

–        http://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm

–        http://www.juliandyke.com/Presentations/Presentations.html

–        http://www.juliandyke.com/Internals/Internals.html

SQL

–        http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm

–        http://www.oracle-base.com/articles/sql/articles-sql.php#11g

PL/SQL

–        http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

–        http://psoug.org/reference/library.html  (Morgans Libarary)

–        http://www.stevenfeuerstein.com/

–        http://www.oracle-base.com/articles/plsql/articles-plsql.php#11g

Performance Tuning

–        http://docs.oracle.com/cd/E11882_01/server.112/e41573.pdf

–        http://jonathanlewis.wordpress.com/

–        https://blogs.oracle.com/optimizer/

–        http://blog.tanelpoder.com/

Oracle Forum (Konular-Sorular-Cevaplar) / Community

–        https://community.oracle.com/community/developer/english

–        http://asktom.oracle.com/pls/apex/f?p=100:1:0

–        http://www.oaktable.net/main

Takip Edilmesi Faydalı Bloglar/Sayfalar

–        http://asktom.oracle.com/pls/apex/f?p=100:1:0

–        http://jonathanlewis.wordpress.com/

–        http://www.stevenfeuerstein.com/

–        http://blog.tanelpoder.com/

–        http://arup.blogspot.com.tr/

–        http://kamranagayev.com/

–        http://kerryosborne.oracle-guy.com/

–        http://juliandontcheff.wordpress.com/

–        http://www.oaktable.net/main

–        www.troug.org

–        http://www.ukoug.org/home/

Okunması Faydalı Olacak Kitaplar

–        Expert One-on-One Oracle “Thomas Kyte”

–        Expert Oracle Database Architecture “Thomas Kyte”

–        Oracle PL/SQL Programming “Steven Feuerstein”

–        Oracle Essentials, Oracle Database 11g  “Rick Greenwald”

–        Troubleshooting Oracle Performance “Christian Antognini”

Posted in Oracle, Uncategorized | Tagged , , , , , , , , , | Leave a comment

Oracle 12c R2 – Partitioning İyileştirmeleri

Herkese Selam,

Bu yazıda Oracle 12c R2 ile beraber gelen partitioning konusundaki iyileştirmelerden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

Partitioning bilindiği üzere Oracle’ın en temel ve en çok beğendiğim özelliklerinden biri. Özellikle büyük hacimli tablolardaki verilere ulaşmada oldukça işimizi kolaylaştıran ve genel sorgu performansımızı arttıran bir veritabanı özelliği.

Oracle 12c R2’den önce bir tabloyu sadece yaratma aşamasında fiziksel olarak partitionlı bir şekilde yaratabiliyorduk. Bunun dışında partitionsız yaratılmış ve hızla büyüyen bir tabloyu partitionlı bir hale getirmek istediğimizde ne yazık ki bu işlemi gerçekleştiremiyorduk ve devamında veriye erişim performanslarımızda düşüşler yaşıyorduk.

Oracle 12c R2’den önce davranışın ne olduğuna bakmak gerekirse;

//db_version: 11g R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;

COMMIT;

ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707))

ORA-14006: geçersiz bölüm adı

Script Terminated on line 21.

Yukarıda partitionsız yaratmış olduğum tabloya Oracle 11g R2’de partitionlı bir yapıya getirmek istediğimde, bu işlemi doğrudan tablo üzerinde online olarak yapamamaktayım.

Şimdi bu operasyonu Oracle 12c R2 ile deneyelim.

//db_version: 12C R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;
   
   COMMIT;
   
ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707)) ONLINE;

Table PARTITIONTEST created.

99.999 rows inserted.

Commit complete.

Table PARTITIONTEST altered.

Şimdi partitionları kontrol edelim.

SELECT table_name, partition_name, high_value
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONTEST';

 

 

 

 

 

 

Evet görüldüğü üzere tabloyu partitionsız yaratıp daha sonra içine data attıktan sonra tabloyu fiziksel olarak partitionlı hale getirebildim. Bu işlemi yapmak için yazmış olduğum “ALTER TABLE … MODIFY” cümlesinin sonunda kullanmış olduğum “ONLINE” komutu tablonun fiziksel olarak partitionlama işlemi sürerken, tabloya eş zamanlı gelmesi muhtemel DML operasyonlarının da işlenmesine olanak sağladı. Böylelikle tabloda büyük bir fiziksel değişim yaparken herhangi bir downtime yaşamamış oldum.

Oracle 12c R2 ile beraber gelen bu yenilik sayesinde Non-Partitioned tabloları Partitioned hale getirme işleminin operasyonel ve downtime maliyetlerini ortadan kaldırılarak önemli gelişim sağlanmış oldu.

Posted in Oracle, Uncategorized | Tagged , , , , , | 1 Comment

LISTAGG Fonksiyon İyileştirmeleri

Herkese Selam,

Bu yazıda, geliştirdiğimiz kodlar içerisinde sıkça kullandığımız LISTAGG fonksiyonu için Oracle 12c Release 2 ile beraber gelen iyileştirmelerden basedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

LISTAGG fonksiyonu bilindiği üzere, SQL deyiminde verilmiş guruplama kriterine göre datanın ilgili kolonundaki değerleri tek bir satırda toplayan bir fonksiyon. Şimdi mevcut durumda nasıl çalıştığına bakalım.

Elimizde aşağıdaki gibi bir data olsun.

SELECT MOD (LEVEL, 4) grp, 
       LEVEL || '0000' AS col
FROM DUAL
CONNECT BY LEVEL < 10;







Şimdi bu datayı  GRP kolonuna göre gruplayıp, COL kolonundaki değerleri yan yana yazalım. (Bu işlemi gerçekleştirmek için LISTAGG kullanacağız.)

SELECT grp,
LISTAGG (col, '; ') 
WITHIN GROUP (ORDER BY col DESC) col
FROM (SELECT MOD (LEVEL, 4) grp, 
       LEVEL || '0000' AS col
       FROM DUAL
                                CONNECT BY LEVEL < 10)
                         GROUP BY grp;

Evet görüldüğü üzere LISTAGG fonksiyonu ile bu işi basitçe halledebildik. Şimdi sample veri setimizi biraz daha büyütüp sonucun dönüp dönmediğine bakalım.

SELECT grp, LISTAGG (col, '; ') WITHIN GROUP (ORDER BY col DESC) col
    FROM (    SELECT MOD (LEVEL, 4) grp, LEVEL || '0000' AS col
                FROM DUAL
          CONNECT BY LEVEL < 10000)
GROUP BY grp;

ORA-01489: result of string concatenation is too long

Datamızın büyüdüğü yani birleştirme işleminin büyük bir veri kümesi üzerinde yapılması gündeme geldiğinde yukarıdaki hatayı aldık ve işlemimize devam edemedik. İşte Oracle 12c R2 ile beraber gelen LISTAGG iyileştirmesi bu problemlere çareler üretmekte ve bu hataları almamızın önüne geçmekte.

Şimdi aynı sorguya 12c R2 ile beraber gelen ON OVERFLOW TRUNCATE söz deyimini ekleyerek çalıştıralım.

SELECT grp, LISTAGG (col, '; ' ON OVERFLOW TRUNCATE) 
WITHIN GROUP (ORDER BY col DESC) col
FROM ( SELECT MOD (LEVEL, 4) grp, LEVEL || '0000' AS col
FROM DUAL
CONNECT BY LEVEL < 10000)
GROUP BY grp;

Evet sorgumuz başarılı bir şekilde hata almadan sonlandı. Birleştirme sonucunda taşan kısım için … ibaresi koyuldu ve en sona truncate edilen toplan karakter sayısı yazıldı. Şimdi yapılan iyileştirme ile beraber gelen farklı opsiyonlara bakmaya devam edelim.

SELECT grp, LISTAGG (col, '; ' ON OVERFLOW TRUNCATE 'TAŞMA') 
WITHIN GROUP (ORDER BY col DESC) col
FROM ( SELECT MOD (LEVEL, 4) grp, LEVEL || '0000' AS col
FROM DUAL
CONNECT BY LEVEL < 10000)
GROUP BY grp;

Yukarıdaki örnekte ON OVERFLOW TRUNCATE söz deyiminden sonra taşan kısımda hangi ibarenin yazmasını istiyorsak onu belirtiyoruz ve sorguyu çalıştırıyoruz. Sorgu sonucunda belirttiğimiz ibarenin en sonda yer aldığını görmekteyiz.

 

 

 

Bir diğer kullanım opsiyonu ise en sonda truncate edilen karakter sayısının yazılmasını engellemek. Bunun içinde WITHOUT COUNT söz deyimini kullanıyoruz.

SELECT grp, LISTAGG (col, '; ' ON OVERFLOW TRUNCATE 'TAŞMA' 
WITHOUT COUNT) WITHIN GROUP (ORDER BY col DESC) col
FROM ( SELECT MOD (LEVEL, 4) grp, LEVEL || '0000' AS col
FROM DUAL
CONNECT BY LEVEL < 10000)
GROUP BY grp;

 

 

 

Evet görüldüğü üzere kesilen karakter sayısı belirtilmeyerek sonuç görüntülendi.

Sonuç olarak LISTAGG kullandığımız durumlarda taşma hataları ile karşılaşmak istemiyorsak, yeni gelen opsiyonları kullanmamız oldukça faydalı olacaktır.

 

Posted in Oracle, Uncategorized | Tagged , , , , | Leave a comment

Kullanımdan Kaldırılmış Metotların İşaretlenmesi

Herkese Selam,

Üst seviye programlama dillerinden alışkın olduğumuz uyarı mekanizmalarından bir tanesi olan “Deprecated” metodlarların (Kullanımdan Kaldırılmış/Geride Kalmış veya Eskimiş) kullanımının uyarılması Oracle 12c R2 ile beraber artık PL/SQL tarafında da sağlanmış durumda. Artık bir metodu deprecated olarak işaretleyip, daha sonra bu metod kullanılmaya çalışıldığında uyarı vermemiz mümkün.

Şimdi bu işlemi nasıl hızlıca halledebiliriz buna bakalım.

Öncelikle compiler’ın üreteceği bu uyarılardan haberdar olabilmek için aşağıdaki uyarı kodlarını session seviyesinde açıyoruz.

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:(6019,6020,6021,6022)';

Evet şuan deprecated olarak işaretleyeceğimiz bir metodu derlemek veya çağırmak istediğimizde gerekli compiler uyarısını verecek alt yapıyı açtık. Şimdi bir metodu nasıl deprecated olarak işaretleyeceğimize bakalım.

CREATE OR REPLACE PROCEDURE kare_hesapla (x NUMBER)
IS
   PRAGMA deprecate (
    kare_hesapla, 'Bu metod eski. Yeni metod kare_hesapla_yeni.'
  );
   v   NUMBER;
BEGIN
   v := x * x;
END kare_hesapla;
/

Yaptığımız örnekten de anlaşılacağı üzere PRAGMA deprecate söz deyimi ile metodun eski bir metod olduğunu sisteme bildirdik. Kodumuzu derlediğimiz de aşağdaki gibi bir uyarıyı sistem tarafından alıyoruz.

Örnek metodumuzu deprecated olarak işaretledik. Şimdi başka bir metod içerisinden bu metodu çağıralım.

CREATE OR REPLACE PROCEDURE KARE_HESAPLA_2
IS
BEGIN
   KARE_HESAPLA (5);
END KARE_HESAPLA_2;

Evet gördüğümüz üzere kodu derledikten sonra gerekli uyarı mesajları compiler tarafından bize gösterildi. Bu mekanizma sayesinde eskiyen metodlarımızın kullanımı konusunda yazılım geliştiricileri kolaylıkla uyarabiliriz ve yeni metodların kullanımı konusunda bilgilendirme kolayca yapabiliriz.

Posted in Oracle, Uncategorized | Tagged , , , | Leave a comment

Oracle Quick SQL Platform

Herkese Selam,

Bu yazıda sizlere Oracle’ın  Qucik SQL Platformundan bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

SQL yazması çok kolay ve insan diline en yakın olması sebebi ile belkide öğrenilmesi ve kodlaması en kolay dillerin belkide başında yer almaktadır. Oracle Quick SQL Platformu ile SQL dilinin doğasında olan bu kolaylığı bir tık daha ileriye taşıyarak çok daha basit bir notasyon ile SQL üreten eden bir yazılım kullanıma açmış durumda. Ben ilk testimde oldukça faydalı olduğunu buldum ve bir kaç deneme yaptıktan sonra oldukça kullanışlı olabileceği kanısındayım. Bu platformun en güzel özelliklerinden biride yazacağınız kurallar çerçevesinde veritabanına insert edilebilecek formatta test datası da üretebiliyor olması.

Bu platformun kullanabilmek için bir Oracle hesabınızın olması yeterli. Eğer bir Oracle hesabınız yok ise linki takip ederek bir Oracle hesabı edinebilirsiniz.

Oracle Quick SQL Platformuna nasıl bir input vererek, nasıl bir çıktı elde edebildiğimiz konusunda hızlı bir fikir vermesi adına bir örnek yapalım.

Yazının başında da söylediğim gibi Oracle’ın belirlemiş olduğu bir notasyon söz konusu, bu notasyonun nasıl olduğuna ortama girdikten sonra menüler aracılığı ile ulaşabilirsiniz. (Oracle Quick SQL Platformuna giriş linkini yazının sonunda paylaşacağım.)

Platforma Verediğimiz Input
departments /insert 4 
   name /nn 
   location 
   employees /insert 1 
      name /nn vc50 
      email /lower 
      cost center num 
      date hired 
      job 
Platformun Ürettiği Çıktı
-- create tables
create table DEPARTMENTS (
    ID               NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                     constraint DEPARTMENTS_ID_PK primary key,
    NAME             VARCHAR2(255) not null,
    LOCATION         VARCHAR2(4000)
)
;

create table EMPLOYEES (
    ID                 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                       constraint EMPLOYEES_ID_PK primary key,
    DEPARTMENT_ID      NUMBER
                       constraint EMPLOYEES_DEPARTMENT_ID_FK
                       references DEPARTMENTS on delete cascade,
    NAME               VARCHAR2(50) not null,
    EMAIL              VARCHAR2(255),
    COST_CENTER        NUMBER,
    DATE_HIRED         DATE,
    JOB                VARCHAR2(4000)
)
;


-- triggers
create or replace trigger EMPLOYEES_BIU
    before insert or update 
    on EMPLOYEES
    for each row
begin
    :new.EMAIL := LOWER(:new.EMAIL);
end;
/


-- indexes
create index EMPLOYEES_i1 on EMPLOYEES (DEPARTMENT_ID);
insert into DEPARTMENTS (
    ID,
    NAME,
    LOCATION
) values (
    1,
    'Customer Satisfaction',
    'Tanquecitos'
);

insert into DEPARTMENTS (
    ID,
    NAME,
    LOCATION
) values (
    2,
    'Finance',
    'Sugarloaf'
);

insert into DEPARTMENTS (
    ID,
    NAME,
    LOCATION
) values (
    3,
    'Office of the CEO',
    'Dale City'
);

insert into DEPARTMENTS (
    ID,
    NAME,
    LOCATION
) values (
    4,
    'Health',
    'Grosvenor'
);

-- load data
insert into EMPLOYEES (
    ID,
    DEPARTMENT_ID,
    NAME,
    EMAIL,
    COST_CENTER,
    DATE_HIRED,
    JOB
) values (
    1,
    3,
    'Gricelda Luebbers',
    'gricelda.luebbers@aaab.com',
    82,
    sysdate - 55,
    'Systems Software Engineer'
);

Platform ile neler yapılabileceğini daha hızlı ve net anlayabilmek için aşağıdaki kısa videoyuda izlemenizi öneriyorum.

Bu platform oldukça pratik bir şekilde SQL ve test datası üretmemize olanak sağlayarak geliştiricilere hız kazandıracak. Oracle Quick SQL Platformuna linki takip ederek ulaşabilirsiniz.

ORACLE QUICK SQL PLATFORM

Posted in Uncategorized | Tagged , , , | Leave a comment