Mevlüt Becerikli

Soru :

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…

Bu yazıda klasik insert ifadesinden biraz uzaklaşıp nasıl çoklu veri girişi işlemi yapacağımızı göreceğiz. Konunun detayını bende ihtiyaç doğrultusunda öğrendim. Bu özellik Oracle 9i den sonra gelmiş. Farklı varyasyonları var;

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…

SQL *Plus’ ta bir SQL script çalıştırdım. Gayet güzel çalıştı. Ama script dosyası içeriğini okuması kolay olsun diye formatladım. Tekrar çalıştırdığımda hata verdi…

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…

PFILE ve SPFILE, dosyaları içerisinde kayıtlı olan parametreler, veritabanı ayarlarını kapsamaktadır. Bu parametreler ile Oracle nasıl çalışmaya başlayacağına (çalışacağına) karar verir. Bunlar, veritabanı ile ilgili dosyaların yerlerini işaret eden ve Oracle yapıları tarafından kullanılacak hafıza değerleri içeren parametrelerdir.Bunlar parametrelere;

• 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…

ORACLE veritabanımızdaki mevcut bir kullanıcıya, mevcut bir şemadaki tüm tablolara erişim yetkisi (SELECT, UPDATE,INSERT,DELETE …vs.) verilmesini istiyoruz. Bunun tek bir GRANT komutu ile yapamayız. TOAD gibi araçlar ile kolayca yapabilsekte bunu tek tek yapmak da sıkıcı olsa gerek ?
Bu iş için bir PLSQL bloğu yapabiliriz…

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…

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…

Oracle’ da sayısız parametre var ve her geçen gün yeni bir tanesini öğreniyorum.  Az önce de yeni bir tanesini öğrendim ve hemen soluğu burada aldım
Sanırım yazının başlığından da hangi parametre olduğunu tahmin etmek zor olmaz : FIXED_DATE

Sanırım oracle ile biraz uğraşmış birisi SYSDATE ifadesinden haberdardır. Bu parametre  (FIXED_DATE) de SYSDATE değerine içinde bulunduğumuz an yerine sabit bir tarih değeri atamamızı sağlar. Ne zaman ki SYSDATE i sorguladığımızda artık bu sabit (değilmez) değerimizi elde edeceğiz. Özellikle yazdığımız prosedürlerde sık sık SYSDATE kullanıyoruz. FIXED_DATE parametresi ile ilk karşılaştığımda da PLSQL kodlarımızın test edilmesi açısından faydalı olabileceği idi.

Kullanımı faydalı da olsa bu parametrenin oturum (session) bazında olmadığı mevcut Instance için bir değişiklik olduğunu öğrendim. Şu an da siz öğrendiniz

Atama :

ALTER SYSTEM SET FIXED_DATE=’1982-12-26-00:00:00′;
SQL> select sysdate from dual;
SYSDATE
———
12/26/1982 00:00:00 AM

Geri Dönüş :

ALTER SYSTEM SET FIXED_DATE=NONE;
SQL> select sysdate from dual;
SYSDATE
———
3/11/2010 2:09:07 PM

NOT : Bu parametre SYSDATE ile benzer yapıda olan SYSTIMESTAMP ifadesinin değerini etkilemez.

İyi Çalışmalar…

Gene bir forum da sorulan güzel bir soruyu paylaşmak istiyorum.

Yukarıda ki yapıda ve verileri içeren tablo isminde bir tablomuz olsun. Bizden MYID alanı bazında kaç adet Y değerine sahip olduğumuzun istatistiği isteniyor. Bunu nasıl yaparız ? Önce tablomuzu oluşturup içini dolduralım.

CREATE TABLE tablo (myid varchar2(3),col1 VARCHAR2(1),col2 VARCHAR2(1),col3 VARCHAR2(1));

INSERT INTO tablo VALUES ( ‘abc’,'Y’,'Y’,'Y’);
INSERT INTO tablo VALUES ( ‘xyz’,'N’,'Y’,'N’);
INSERT INTO tablo VALUES ( ‘abc’,'Y’,'Y’,'N’);

Şimdi de istenen SQL sorgusunu yazalım.

SELECT myid, SUM(DECODE(col1, ‘Y’, 1, 0) + DECODE(col2, ‘Y’, 1, 0)+ DECODE(col3, ‘Y’, 1, 0)) AS TOTAL
FROM tablo
GROUP BY myid;

Sonuç mu ?

MYID    TOTAL
abc    5
xyz    1

İyi Çalışmalar…

Oracle bildğim kadarı ile 4 versiyona sahiptir.

• Oracle Enterprise Edition (EE)
• Oracle Standard Edition (SE)
• Oracle Standard Edition One (SEO)
• Oracle Express (XE)

Express Edition, bu versiyonların en basit ve kısıtlı olanıdır. Yani Oracle veritabanını öğrenmeye yeni başlayanların ilk adresi olan versiyonudur. Doğal olarak bazı kısıtlara sahiptir. Bu yazı da bu kısıtları ve diğer versiyonlar hakkında edindiğim genel bilgileri paylaşmaya çalışacağım. Öncelikle hangi oracle versiyonunu kullandığımızı bize veren SQL ifadesini paylaşalım;

SELECT *  FROM V$version;

Sonra bu Oracle Database Express Edition kısıtları:

• Express Edition, herhangi bir server da sadece tek bir INSTANCE ile sınırlıdır. Yani bir bilgisayarda sadece tek bir Oracle XE çalışabilir.
• Express Edition, birden çok CPU ya sahip bir server a yüklenebilsede sadece bir işlemciyi kullanıp bu işlemci üzerinde çalışabilir
• Express Edition, sadece 4 GB lık kullanıcı verisini destekler. Buna Express Edition system verisi dahil değildir.
• Express Edition, kullanılabilir RAM hafızasından maksimum 1 GB RAM ına kadar kullanabilir.

Diğer oracle versiyonları kendilerine ne kadar para verdiğiniz ile doğru orantılı olarak özellik olarak da size yol, su, elektrik olarak dönmektedir

Ama genel olarak 3 ana kategoride özellikler değişiklik göstermektedir: Kullanacağı Sunucu Çapı, Özellikler ve kullanabilir opsiyonlar. Bu versiyonlar ile ilgili okuduğum bir yazıyıda paylaşmak isterim.

Understanding Oracle features and options

Ek olarak da kullanmakta olduğunuz Oracle veritabanının özelliklerinin detay bilgisini aşağıdaki SQL ifadesi iel almanızada mümkün :

SELECT *  FROM V$option;

Yada ;

http://www.oracle.com/database/product_editions.html

İyi Çalışmalar…

Okuyup öğrencikçe paylaşmaya devam…
Bir REDO LOG GROUP’ una yeni bir REDO LOG MEMBER’ ı nasıl ekleyeceğimizi görelim :

ALTER DATABASE ADD LOGFILE MEMBER
/DISK2/log1b.rdo’ TO GROUP 1,
‘/DISK2/log2b.rdo’ TO GROUP 2;

Daha detaylı bilgi için aşağıdaki linki incelemenizi öneririm:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/onlineredo.htm#sthref946

İyi Çalışmalar…