Veritabanımızda bir tablo sütununda farklı (aşağıdaki gibi) formatlarda telefon numaraları var.  Bizden bu verileri tek tip formata çevirmemiz isteniyor.

Mevcut Formatlar :

+1 123 456 7890
+1-123-456-7890
+1-123/456 7890

İstenen :

+1.123.4567890

Çözüm :

SELECT TRANSLATE(’+1 123 456 7890′, ‘ /-’,'…’) FROM DUAL
SELECT TRANSLATE(’+1-123-456-7890′, ‘ /-’,'…’) FROM DUAL
SELECT TRANSLATE(’+1-123/456 7890 ‘, ‘ /-’,'…’) FROM DUAL

Açıklama :

TRANSLATE komutunun kullanımı aşağıdaki gibidir;

TRANSLATE( ilgili_metin , degistirilecekler, eklenecekler )
ilgili_metin : İçeriği değiştirilecek metin
degistirilecekler : İçeriği değiştirilecek metin içerisinden değişecek karakterlerin dizisi
eklenecekler : degiceşekler kısmında her bir karakterin yerine gelecek karakterleriden oluşan dizi

Bu açıklamadan sonra gelelim bizim çözümümüze. Bu komut ile ilgili_metin içinde yer alan ve değiştirmeyi istediğimiz karakterleri sırası ile degistirilecekler kısmına yazıyoruz. Her bir karakterin yerine gelmesini istediğimiz karakterleri gene aynı sırada olmak kaydıyla eklenecekler kısmına yazıyoruz.

Burada önemli; komutun tek bir karakter bazında çalışması (Yani “12″ yerine “34″ yapamıyoruz, “1″ yerine “3″ ve “2″ yerine “4″ olacak şekilde çalışıyor) ve degistirilecekler ile eklenecekler dizilerindeki karakterlerin sıralarının aynı olmasıdır.

NOT : Aynı soruna içiçe REPLACE komutu ile de çözüm üretebiliriz. Ama  TRANSLATE komutu sanırım bu okuması-yazması karışık ve hataya açık durumdan kurtulmak için geliştirilmiş;

SELECT REPLACE(REPLACE(REPLACE(’+1 123 456 7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL
SELECT REPLACE(REPLACE(REPLACE(’+1-123-456-7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL
SELECT REPLACE(REPLACE(REPLACE(’+1-123/456 7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL

İyi Çalışmalar…

1. Koşulsuz çoklu giriş
2. Koşullu çoklu giriş
3. Koşullu ama koşula ilk uyan verinin girişi
4. Pivot Tablo yapısında çoklu veri girişi

1. KOŞULSUZ ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

INSERT ALL
INTO <tablo_ad> VALUES <sutun_degerleri)
INTO <tablo_ad> VALUES <sutun_degerleri)
…
SELECT <sutun_isimleri> FROM <tablo_ad>;

Örnek :

INSERT ALL
INTO tbl_tip VALUES (tip_id)
INTO tbl_marka VALUES (marka_id)
SELECT marka_id, tip_id FROM tbl_arabalar;

SELECT ifadesinden gelen verileri INSERT ALL kısmında ki tablolara ekliyoruz.

2. KOŞULLU ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

INSERT ALL
WHEN (<condition>) THEN
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
WHEN (<condition>) THEN
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
ELSE
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
SELECT <sutun_isimleri> FROM <tablo_ad>;

Örnek :

INSERT ALL
WHEN (marka_id = 1) THEN
INTO tbl_bmw VALUES (plaka)
WHEN (marka_id = 2) THEN
INTO tbl_audi VALUES (plaka)
SELECT marka_id, plaka FROM tbl_arabalar;

SELECT ifadesinden gelen verileri INSERT ALL kısmında ki WHEN koşuluna göre ilgili tablolara ekliyoruz. Burda ilk şarta uysa bile tüm koşullara bakılıyor.

3. KOŞULLU AMA KOŞULA İLK UYAN VERİNİN GİRİŞİ ( INSERT FIRST)

Syntax:

Koşullu INSERT ALL ile aynıdır sadece INSERT ALL yerine INSERT FIRST ifadesi kullanılır. Farkı; ilgili koşulları sırası ile değerlendirir ve istenen şartı ilk sağlayan koşul da veri girişini yapar ve sıradaki diğer şartlara bakmaz.

Örnek:

INSERT FIRST
WHEN (model < 1980) THEN
INTO tbl_hurda VALUES (plaka)
WHEN (model < 2000) THEN
INTO tbl_normal VALUES (plaka)
WHEN (model > 2010) THEN
INTO tbl_lux VALUES (plaka)
SELECT marka_id, plaka FROM tbl_arabalar;

Bu örnekte, araçları modellerine göre ayırıyor ve buna göre farklı tablolara atıyoruz. Burda şartı sağlayan ilk koşulda işlem yapılıp diğer koşullar es geçiliyor.

4. PİVOT TABLO YAPISINDA ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

Koşulsuz veri girişi ile aynıdır. Burda asıl amaç ilişkisel veritabanı mantığına aykırı olan ve yatay olarak genişliğe sahip bir tablonun ilişkisel hale getirilmesi işidir (Belki başka amaç içinde kullanılabilir).

Örnek :

INSERT ALL
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PAZARTESI’,tut_pazartesi)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’SALI’,tut_sali)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CARSAMBA’,tut_carsamba)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PERSEMBE’,tut_persembe)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CUMA’,tut_cuma)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CUMARTESI’,tutCumartesi)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PAZAR’,tut_cumartesi)
SELECT satis_id, urun_ad, tut_pazartesi, tut_sali, tut_carsamba, tut_persembe, tut_cuma, tut_cumartesi, tut_pazar
FROM satis;

Bu örnekte “satis_id / urun_ad / tut_pazartesi / tut_sali / tut_carsamba / tut_persembe / tut_cuma / tut_cumartesi / tut_pazar” alanlarını sahip bir tabloyu “id / urun_ad /gun / tutar” alanlarına sahip bir tabloya atmış oluyoruz

İyi Çalışmalar…

SP2-0042: unknown command “FROM” - rest of line ignored.
SP2-0734: unknown command beginning “SELECT…” - rest of line ignored.

Sorunun kaynağının değişiklik yaparken eklenen YENİ SATIR KARAKTERİ olduğu anlaşıldı. SQL* Plus ortamında bunun da çözümü mevcut. Script i çalıştırmadan önce ;

SQL> set sqlblanklines on

ifadesini çalıştırın…

İyi Çalışmalar…

• DATASTAGE SERVER 7.5
• DATASTAGE PX 7.5
• TALEND OPEN STUDIO 2.4.1
• INFORMATICA 8.1.1
• PENTAHO DATA INTEGRATOR 3.0.0

Link ;

http://www.manapps.tm.fr/pdfETL/ETLBenchmarks_Manapps%20090203.pdf

İyi Çalışmalar…

Raporlarımızda (Özellikle grafiksel) bazı alaları (Dimension) baz alarak istatiksel veri alanlarını (Fact) sıralarız. Ama baz alınan bu alanlarının çokluğu karmaşıklığa yol açar ve müdür direkt şu ifadeyi kullanır;

- Bana NÜFUS açısından ilk 5 İL i getirsin yeter…

Bu gibi bir durumda raporumuzu nasıl tasarlıcaz ona değinmek istiyorum. Bu örneği Cognos 8.3 ve İlişkisel (Relational) bir modelde yaptığımı belirteyim.

Öncelikle rapor sorgumuza (qAltVeri) bir DataItem (SIRALAMA) ekleyerek Expression Definition ekranında şu ifadeyi yazalım;

Rank([NUFUS] desc for report)

Böylece NÜFUS alanına göre azalan yönce veri sonuç kümesini sıralayarak yanına eklediğimiz sütunda tekil (UNIQUE) ve sıralı artan (AUTO INCREMENT) bir alan elde etmiş oluruz.

Bu sorgu bizim temel sorgumuz olacak ve bu sorguyu kullanarak yeni sorgular üretebiliriz. Amacımız olan ilk 5 kayıt için yeni bir sorgu (2. sorgu) oluşturup filtreye de şu ifadeyi yazarız;

[qAltVeri].[SIRALAMA]<=5

Görüldüğü gibi TOP 5 listemizi oluşturmuş olduk. Sorgumuzda IL bazında qAltveri sorgusunu kullanmıştık ve IL alanımızın Expression Definition değeri de aşağıdaki gibiydi;

[qAltVeri].[IL_ADI]

Peki ya ilk dışında kalmayanları da toplu olarak, “DİĞER İLLER” olarak istenirse?

3. bir sorgu oluşturup filtreye de;

[qAltVeri].[SIRALAMA]<=5

yazmamız yeterli olacak mıdır ? HAYIR!!!!

Her İL‘ i aynı saymak için Expression Definition değeri de aynı olmalıdır ki İL DataItem‘ ının Expression Definition değerine “DİĞER İLLER” yazarak bu işten yırtabiliriz. Sonra da 2. ve 3.  sorgularımızı UNION yaparak 4. sorgumuz elde edebiliriz.

İster List, ister Chart, isterseniz de Crosstab…. Bu 4. sorguyu kullanarak istediğiniz gibi raporunuzu oluşturabilirsiniz.

Not : “DİĞER İLLER” i raporda en alt sırada göstermek isteyeceksinizdir. Bunun için de 2. ve 3. sorgulara yeni bir DataItem ekleyerek Expression Definition değerlerine “0″ ve “1″ ekleyerek rapor tipinize göre SORTING özelliğini ASC/DESC yaparak bunu sağlayabilirsiniz…

İyi Çalışmalar…

http://www.udel.edu/cognos/

Bunun dışında elbetteki ürünün sahibi olan IBM firmasının eşsiz kaynağı olan sitesi için tıklayınız

İyi Çalışmalar…

• DESC v$parameter komutunu çalıştırarak
• SQL*Plus dan SHOW PARAMETERS komutnu çalıştırarak
• V$PARAMETER isimli View’ i sorgulayarak

ulaşabilirsiniz. Şimdi bu iki , PFILE ve SPFILE, dosya arasındaki farkları incelemeye çalışalım;

PFILE (Parameter File) :

• Dosyanın ismi Default olarak “init.ora” veya ORACLE_SID ifadesi kullanılarak “initSID.ora” formatındadır.
  

• Dosyanın yeri Windows için “ORACLE_HOME\database” ve Unix için “ORACLE_HOME\dbs” dizinleridir.

• PFILE, metin tabanlı yani NOTEPAD (Windows) veya vi (Unix)…v.b herhangi bir editör ile açılıp içeriği değiştirilebilir bir dosyadır.

SPFILE (Server Parameter File) :

• Dosyanın ismi Default olarak “spfile.ora” veya ORACLE_SID ifadesi kullanılarak “spfileSID.ora” formatındadır.

• Dosyanın yeri Windows için “ORACLE_HOME\database” ve Unix için “ORACLE_HOME\dbs” dizinleridir.

• SPFILE, PFILE aksine İkili (Binary) bir dosyadır. Bir editör aracılığı ile içeriği elle olarak değiştirilemez. Oracle, SPFILE içeriğini değiştirme işlemini “ALTER SYSTEM SET…” komutu ile yapmamızı sağlar.

• PFILE göre en büyük avantajlarından birisi, Kalıcı (Persistent) parametrelerin dinamik olarak veritabanının kapanıp açılmasına gerek duymadan değiştirilmesi ve aktif hale getirilebilmesidir. Veritabanı kapatılıp açıldıktan sonra bile bu değişiklikler devamlılığını sürdürebilmektedir. Bu işlem;ALTER SYSTEM SET <parametre_ismi> = <deger>  SCOPE=SPFILE;

• komutu ile gerçekleştirilir. SCOPE ifadesi 3 değer alabilmektedir;

• • MEMORY : Default seçenektir ve değişiklikler sadece mevcut Instance için geçerli olacaktır.
  • SPFILE : SPFILE’ ı günceller ve değişiklikler bir sonraki STARUP da geçerli olacaktır
  • BOTH : Bu seçenek diğer iki seçeneğin toplamıdı. Çalıştırıldığı andan itibaren geçerli olup SPFILE da kayıtlı olur ve sonraki STARTUP’ larda da geçerli olur.

• Diğer bir avantajı da RMAN ile yedeklenebilmesidir. Veritabanında yapısal bir değişiklik olduğunda ya da veritabanının yedeği her alındığında otomatik olarak RMAN tarafından yedeği alınabilir. Dolayısıyla da Kurtarma (Recover) işlemi de otomatik ve kolayca olur.

• SPFILE’ ın önemli bir diğer avantajı da, Oracle’ ın Otomatik Ayar (Tuning) özellikleri için SPFILE a ihtiyaç duymasıdır.

Oracle STARUP esnasında hangi dosyayı kullanır ???

Sırasıyla;

1. spfileSID.ora
2. spfile.ora
3. initSID.ora
4. init.ora

dosyalarını arar ve ilk bulduğu dosyaya göre açılır. Kendimiz de bu seçimi belirleyebiliriz;

• SQL> startup pfile=/…./initTEST.ora
• SQL> startup spfile=/…./initTEST.ora

Not : PFILE’ dan SPFILE ve SPFILE’ dan PFILE elde ederek SPFILE içeriğini de değiştirebiliriz. Nasıl mı ?

SQL> CREATE PFILE FROM SPFILE;
SQL> CREATE PFILE='/.../initTEST.ora' from SPFILE='/.../spfileTEST.ora';
SQL> CREATE SPFILE FROM PFILE;SQL> CREATE SPFILE='/.../spfileTEST.ora' from PFILE='/.../initTEST.ora';

İyi Çalışmalar…

Cognos raporlarını, Cognos’ un kendi özelliği olan Report Studio‘ dan istediğimiz gibi dizayn edip tasarlayabilir ve geliştirebiliriz. Ama bazen bu raporları bir dosya olarak taşımak gerekebiliyor. Bu durumda, Report Studio (Cognos) bir raporun yapısını XML olarak bize sağlamaktadır.

Bir raporu export etmek için, Report Studio ortamında  “Tools >> Copy Report to Clipboard” menüsünü tıklayarak kopyalayıp, XML formatını destekleyen herhangi bir editore yapıştırarak XML olarak kaydedebilir ve istediğimiz gibi kullanabiliriz.

Bir raporu import etmek için, Raporumuzun XML formatındaki son halini kopyaladıktan sonra Report Studio ortamında “Tools >> Open Report from Clipboard” menüsünü tıkladığımızda raporumuz açlıacaktır.

İyi Çalışmalar…

EGITIM şemasındaki tabloları için MEVLUT kullanıcısını yetkilendirelim;

FOR x IN (SELECT * FROM dba_tables WHERE owner = ‘EGITIM’)
LOOP
EXECUTE IMMEDIATE ‘GRANT SELECT, UPDATE, DELETE, INSERT ON ‘ || x.owner || ‘.’ || x.table_name || ‘ TO MEVLUT’;
END LOOP;

NOT : Tabiki bu script çalıştırıldıktan sonra veritabanında EGITIM şemasında oluşturulan tablolara MEVLUT kullanıcısının yetkisi olmayacaktır. Script kaydedilip düzenli olarak çalıştırılabilir.

Şu an sadece tablo için ve manual olarak konuşuyorum. Ama bunu DDL ifadelerine endeksli bir TRIGGER ile otomatik olarak da yapmamız mümkün. Mesela bir tablo yaratıldığında MEVLUT kullanıcısına SELECT yetkisi verilmesini sağlayan bir TRIGGER yazalım;

CREATE OR REPLACE TRIGGER do_grant
AFTER CREATE ON SCHEMA
DECLARE
l_str varchar2(255);
l_job number;
BEGIN
IF ( ora_dict_obj_type = ‘TABLE’ ) THEN
l_str := ‘EXECUTE IMMEDIATE “GRANT SELECT ON ‘ || ORA_DICT_OBJ_NAME || ‘ TO mevlut”;’;
DBMS_JOB.SUBMIT( l_job, replace(l_str,’”‘,””) );
END IF;
END;

NOT :Burada  ORA_DICT_OBJ_NAME ifadesi, bir DDL  ifadesi (System Event) çalıştırıldığında  oluşan nesne tipidir (Dictionary  Object Type). Burada oluşan tabloyu verir. Bunun gibi bir çok nesne tipi vardır. Bunlar için aşağıdaki linkleri incelemenizi öneririm.

http://www.java2s.com/Tutorial/Oracle/0560__Trigger/ORADICTOBJTYPE.htm
http://psoug.org/reference/system_events.html

İyi Çalışmalar…

]]> http://www.mevlutbecerikli.com/?feed=rss2&p=333 http://www.mevlutbecerikli.com/?p=436 http://www.mevlutbecerikli.com/?p=436#comments Mon, 17 May 2010 11:51:15 +0000 Mevlüt Becerikli http://www.mevlutbecerikli.com/?p=436 SORU :

Tablomuz (TEST_TABLE)

COL1   COL2
1          A
1          B
1          C
2          A
2          C

1       A, B, C
2       A, C

ÇÖZÜMLER :

1. YOL (CONNECT BY) :

SELECT COL1,
SUBSTR(SYS_CONNECT_BY_PATH(COL2, ‘,’), 2) LISTE
FROM (select COL2,
COL1,
COUNT(*) OVER (PARTITION BY COL1) cnt,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) seq
FROM (SELECT * FROM TEST_TABLE))
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR COL1 = COL1;

2. YOL (XML) :

SELECT COL1,
RTRIM(XMLAGG(XMLELEMENT(c, COL2 ||’, ‘)).EXTRACT(’//text()’), ‘, ‘) LISTE
FROM TEST_TABLE
GROUP BY COL1;

3. YOL (WM_CONCAT) :

SELECT COL1,
WM_CONCAT(COL2) LISTE
FROM TEST_TABLE
GROUP BY COL1;

İyi Çalışmalar…