İKİ NOKTA ARASINDAKİ KARAKTER KÜMESİNİ ALMA

Bugün aşağıdaki sorguya ihtiyaç duydum belki ileride birdun sizinde işinize ayrayabilir. Aslında ben sadece nokta(.) karakterine göre kıyaslama yaptım ama siz bunu değiştirebilirsiniz (-) , ( _ ) gibi değişikl karaketerler yapabilirsiniz.

SELECT Substr (i.proc_name,
--ilk noktanın konumunu aldım
(Regexp_instr (i.proc_name, '[:.:]', 1, 1, 0, 'i') + 1),
--ikinci noktanın konumundan ilk noktanın konumunu çıkarakak iki nokta arasında kaç karakter olduğunu buluyorum.
(Regexp_instr (i.proc_name, '[:.:]', 1, 2, 0, 'i')
- Regexp_instr (i.proc_name, '[:.:]', 1, 1, 0, 'i') - 1
) ) package_name
FROM paket_isim_tablom i

--group by ile tekrar eden kayıtları ekliyorum. Çünkü bir pakette birden fazla procedere olduğu için tekrarlamalar sözkonusu oluyor.
GROUP BY Substr (i.proc_name, (Regexp_instr (i.proc_name, '[:.:]', 1, 1, 0, 'i') + 1),
(Regexp_instr (i.proc_name, '[:.:]', 1, 2, 0, 'i')
- Regexp_instr (i.proc_name, '[:.:]', 1, 1, 0, 'i') - 1
) )
ORDER BY 1

buradaki seneryoda paket_isim_tablom da proce_name alanında schema.packege.procederu şeklinde bir karaketer katarım var. Ben buradan package name çıakrtmak için yukarıdaki sql'i kullanabilirim.

ÖRNEK BİR KULLANIM

/* Formatted on 2009/08/18 11:24 (Formatter Plus v4.8.8) */
SELECT Substr ('HR.PAKET_ADI.PROCEDURE_ADI',
( Regexp_instr ('HR.PAKET_ADI.PROCEDURE_ADI',
'[:.:]',
1,
1,
0,
'i'
)
+ 1
),
( Regexp_instr ('HR.PAKET_ADI.PROCEDURE_ADI',
'[:.:]',
1,
2,
0,
'i'
)
- Regexp_instr ('HR.PAKET_ADI.PROCEDURE_ADI',
'[:.:]',
1,
1,
0,
'i'
)
- 1
)
)
FROM dual;

SUBSTR('H
---------
PAKET_ADI


1 row selected.

PL/SQL IF KULLANIMI

DECLARE
--değişkenlerimizi tanımladık.
a NUMBER;
b NUMBER;
BEGIN
a := 15;
b := 15;
IF (a < 10
OR b < 100) THEN
dbms_output.Put_line('küçük');
END IF;
--bu işlemin çıktısı küçük olacaktır.
END;
-----------------------------------------------------------------
DECLARE
a NUMBER;
b NUMBER;
BEGIN
a := 15;
b := 15;
IF (a < 10
AND b < 100) THEN
dbms_output.Put_line('küçük');
END IF;
--bu işlemin çıktısı olmayacaktır.
END;

-----------------------------------------------------------------
DECLARE
a NUMBER;
b NUMBER;
BEGIN
a := 15;
b := 15;
IF (a < 10
AND b < 100) THEN
dbms_output.Put_line('küçük');
--iif else kullanımı için örnek vermek gerekir ise
ELSE
dbms_output.Put_line('büyük');
END IF;
--bu işlemin çıktısı büyük olacaktır.
END;
-----------------------------------------------------------------
DECLARE
a NUMBER;
b NUMBER;
BEGIN
a := 15;
b := 15;
IF (a < 10
AND b < 100) THEN
dbms_output.Put_line('küçük');
--if else kullanımı için örnek vermek gerekir ise
ELSE
dbms_output.Put_line('büyük');
END IF;
--bu işlemin çıktısı büyük olacaktır.
--exception eklemek istersek aşağıdaki kodu eklemek yetelidir.
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20000,SQLERRM);
END;

ORACLE HATALARINI YAKALAMA

-- Veritabani'mizdaki sorgular çalisirken alinan hatalari asagidaki sorgu ile tespit edebiliriz.

-- Warning: compiled but with compilation errors bu uyariyi aldigimizda

-- show errors ile alinan hataliri görebiliriz. Ama asagida anlatmaya çalistigimda ayni isi yapacaktir.

-- Öncelik ile kendimize bir senaryo hazirlayalim. Senaryomuz su sekilde olsun.

-- bir paket yazdik ve içerisinde çesitli tablolar join islemi yapiyor temp tablolar olusturuyor.

-- Bir çesit ETL paketi diyebiliriz. Bu paket çesitli tablolardan select yapiyor yada içerisinde dinamik SQL'ler var

-- Kod dynamik sql e girdigi zaman hata aldi ve siz bu hatalari listelemek yada görmek istiyorsunuz.

-- Bu raporu hazirlamak için iki adet oracle View'ini joinlemek yeterli olacaktir.

-- öncelik ile all_source'den (USER_SOURCE,DBA_SOURCE de olur yetkinize göre artik.) hata alan kodu aliyoruz.

-- bunu daha sonra error'lerin USER_ERRORS (DBA_ERRORS,USER_ERRORS)oldugu görünüm ile joinliyoruz.

SELECT a.owner,

       a.TYPE,

       To_char(a.line)

       ||' - '

       ||a.text

       ||' - '

       ||b.text error

FROM     dba_source a,

         user_errors b

--çalisan isi burada belirtiyoruz.

--hangi isin hata aldigini bilmiyorsak bu kisimi açiklamaya çevirebiliriz.

WHERE a.NAME = 'AA'

         AND a.owner = b.owner

         AND a.NAME = b.NAME

         AND a.TYPE = b.TYPE

         AND a.line = b.line

ORDER BY a.NAME,

         a.line;

--bu isin aynisini show erros ile de yapabilirsiniz. Derleme isinden sonra hatalari bize gösterecektir.

--ÖRNEK BIR UYGULAMAYI HEMEN YAPALIM.

--hatali çalisacak bir procedure hazirlayalim.

SET SERVEROUTPUT ON;


CREATE OR REPLACE PROCEDURE Aa

IS

--burada degiskenimizi açiklama satiri yaptik. bu sebepten dolayi

--PLS-00201: identifier 'v_dynsql' must be declared

--degisken tanimlanmalidir. hatasini alacagiz.

--v_dynsql varchar2(200);

BEGIN

  v_dynsql := 'bu bir ekran ciktisidir.';

  

  dbms_output.Put_line(v_dynsql);

END;
SELECT   a.owner,

         a.TYPE,

         To_char(a.line)

         ||' - '

         ||a.text

         ||' - '

         ||b.text error

FROM     dba_source a,

         dba_errors b

WHERE    a.NAME = 'AA'

         AND a.owner = b.owner

         AND a.NAME = b.NAME

         AND a.TYPE = b.TYPE

         AND a.line = b.line

ORDER BY a.NAME,

         a.line;

--yukaridaki sorgu ile hatayi ve alinan hatanin geçtigi kod'u görenibiliriz.

--kodumuz düzeltip tekrar çalistirdigimizda

SET SERVEROUTPUT ON;


CREATE OR REPLACE PROCEDURE Aa

IS

  v_dynsql  VARCHAR2(200);

BEGIN

  v_dynsql := 'bu bir ekran ciktisidir.';

  

  dbms_output.Put_line(v_dynsql);

END;

--bu kez hatasiz derlenecegi için herhangibi bir hata almayacaktir.

--sorgu sonucuda null dönecektir.

PARTITION BAZLI KAYIT EKLEME

DECLARE

  dyn_sql       VARCHAR2(2000);

   -- KAYNAK TABLONUN BULUNDUGU SCHEMA

   v_owner  VARCHAR2(30) := 'HR';

   -- kaynak tablonun adi.

   v_table  VARCHAR2(30) := 'BU_TABLODAN';

   --partitionun tasinacagi (eklemenin yapilacigi partitionun oldugu) schema adi

   v_dest_owner  VARCHAR2(30) := 'SCOTT';

   --insert yapilacak olan tablonun adi.

   v_dest_table  VARCHAR2(30) := 'BU_TABLOYA';

BEGIN

  --database'deki butun partitionlar bizim tablomuza ait olan partitionlarin isimlerini çekiyoruz.

  --burada her iki partition isimlerinin de ayni oldugu varsayiliyor.

  --partition isimlerinin farkli oldugu durumlarda kodda düzenlemeye gitmek gerekebilir.

  --all_tab_partitions da HIGH_VALUE üzerinde i?lem yap?labilir.

  --high_value long b?r alan oldu?u için dönü?türme yapmak gerebilir.

  FOR i IN (SELECT partition_name

            FROM   all_tab_partitions

            WHERE  table_owner = v_owner

                   AND table_name = v_table)

  LOOP

    dyn_sql := ' INSERT /*+ append */ INTO '

               ||v_dest_owner

               ||'.'

               ||v_dest_table

               ||' PARTITION ('

               ||i.partition_name

               ||')
SELECT /*+ parallel (aa,16) */ *
FROM '

               ||v_owner

               ||'.'

               ||v_table

               ||' partition ('

               ||i.partition_name

               ||') aa ';

    

    dbms_output.Put_line(dyn_sql);

    

    --EXECUTE IMMEDIATE dyn_sql;

    COMMIT;

  END LOOP;

END;

GRANT EXECUTE YETKİSİ

--kendi semamizda proceduremizi olusturalim ve içerisinde yine kendi semamizdaki bir tabloyu silelim.

--tablomuzu yaratalim.

CREATE TABLE deneme_silsene (

  a NUMBER);


--procedure yaratalim

CREATE OR REPLACE PROCEDURE Deneme_sil

AS

  v_dyn_sql  VARCHAR2(200) DEFAULT NULL;

BEGIN

  v_dyn_sql := 'drop table deneme_silsene';

  

  EXECUTE IMMEDIATE v_dyn_sql;

  

  COMMIT;

END;

/


--baska bir semadan (userdan "hedefowner") baglanarak olusturdugumuz procedureyi çalistiralim.

BEGIN

  hedefowner.deneme_sil;

  

  COMMIT;

END;

/


--yetkiniz yok hatasi alacagiz bu hatayi asmak için yetki verelim.

--bu yetkiyi kendi semamiza baglanarak kendi schemamiz altinda vermemiz gerkiyor.

GRANT EXECUTE ON deneme_sil TO hedefowner;


-- procedure normal bir sekilde çalisti ve kendi tablomuzu sildi.

-- burada tablomuz için diger user'a yetki vermedik ama yine de tablomuz üzerinde islem yapabildi.

-- grant execute yetkisi sikintili bir yetki oldugunu buradan anlayabiliriz.

--söyle bir deneme de yapalim isterseniz.

--simdi procedureyi çalistiracagimiz schemada da ayni isimde bir tablo yaratalim ve hangi tabloyu silecegine bakalim.

-- bu konu hakkinda fikri olmayan arkadaslariniz ile iddaya girerek ögle yemegi kazanabilirsiniz.

--simdi su sekilde oldu son durum.

SELECT *

FROM   hedefowner.deneme_sil;


SELECT *

FROM   buradansiliyom.deneme_sil;


--iki farkli schemada ayni isimde tablom var

BEGIN

  hedefowner.deneme_sil;

  

  COMMIT;

END;

/


-- burada bu procedure'yi çagirir isem hangi tabloyu silecek. procedure schema adini belirtmedim. procedurenin yaratildigi yerdeki tabloyu mu silecek

-- yoksa calistirdigimiz yerdeki tabloyu mu silecek?

--iste bu sorunun cevabi procedurenin oldugu create edildigi schemadaki tabloyu silecektir.

--bu durumu asmak için yada kafa karistirmasina , olurda yanlis schema altinda islemler yapmasini engellemek için

--"schema_adi"."tablo_adi" seklinde çalismakta fayda var. bunu aliskanlik edinmek lazim.

DROP PROCEDURE hedefowner.deneme_sil;

ALTERNATİF SEQUENCE KULLANIMI

EĞER CREATE SEQUENCE Yetkiniz yok ise ve artan bir alan kullanmak zorunluluğunuz var ise aşağıdaki yöntemi kullanarak kendize bir sequence yaratabilirsiniz. Bu yöntem büyük databaselerde yada büyük tablolarda kullanmak mantıklı değildir. Max değeri bulmak sizde ciddi performans kaybına sebeb olacaktır.

--sequence kullanmaktan kaçmanın bir yöntemide budur diyebiliriz.
--tablomuzu yaratalım.
create table tablom as select * from all_objects where rownum<50;

--en buyuk değeri bularak değerini select cumleciğinde bir arttırıp değişkene atıyoruz.
--sonrada yeni değerimizde bu değişkeni kullanıyoruz.
DECLARE
en_buyuk_deger NUMBER;
BEGIN
SELECT MAX (object_id) + 1
INTO en_buyuk_deger
FROM tablom;
INSERT INTO tablom
(object_id,object_name
)
VALUES (en_buyuk_deger,'ali'
);
commit;
END;

ORACLE PL /SQL İLE ATTACHMENT MAİL GÖNDERME

ORACLE ile mail atmak için aşağıdaki yöntemi izleyebilirsiniz. Size attachment'li mail atma desteği verecektir.


CREATE OR REPLACE PROCEDURE smtp_att_mail (
pin_table_name IN VARCHAR DEFAULT 'CRE_OR_DEL_OBJECT'
)
IS
--gönderen kısmında yazacak olan yaz bu alan gecerli olmak zorunda değildir.

v_from VARCHAR2 (80) := 'OBJE.BILGILERI@aliates.com';
v_recipient VARCHAR2 (80) := 'aliatesh@hotmail.com';
v_subject VARCHAR2 (80) := 'olusan ve silinenler nesneleri listeler.';
v_mail_host VARCHAR2 (30) := 'mailserver';
v_mail_conn UTL_SMTP.connection;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
--mesaj VARCHAR2 (4000) NULL;
--dosyamızın içerisinde yazacakları bir değişkende topluyorum.
--burada VARCHAR2 kullandım ama long da kullanılabilinir.
mesaj VARCHAR2 (30000) := '||';
BEGIN
--burada tabloyu değişkene aktarıyorum.
--dikkat etmek gereken konu burada mail içeriğine uygun olması gerektiğidir.
--bunun için biraz denemeler yapmaız gerekebilir.
--ben substr ile istemediğim alanları kesitim. Siz daha orijinal bir çözüm üretirsiniz artık.
FOR i IN (SELECT *
FROM user_objects where rownum<10)
LOOP
mesaj :=
mesaj
|| ''
|| i.owner
--burada ";" kullanıyorum ki Excell bunu otomatik olarak algılasın ve sütün sütün göstersin.
--bu parametreyi control panelden de değiştirenbiliyorsunuz. isterseniz burada "|" yada "," kullanabilirsibiz.
|| ';'
|| i.object_name
|| ';'
|| i.object_type
|| ';'
|| i.status
|| crlf;
END LOOP;

mesaj := SUBSTR (mesaj, 3, (LENGTH (mesaj) - 2));
--burada 25. portu kullanarak mail gönderme parametlerini veriyorum.
v_mail_conn := UTL_SMTP.open_connection (v_mail_host, 25);
UTL_SMTP.helo (v_mail_conn, v_mail_host);
UTL_SMTP.mail (v_mail_conn, v_from);
UTL_SMTP.rcpt (v_mail_conn, v_recipient);
UTL_SMTP.rcpt (v_mail_conn, 'aliatesh@hotmail.com');
--buraya birden fazla mail adresi ekleyebiliriz.
--bu fonksiyon için parametre alan bir dongu yazılabilir.
UTL_SMTP.rcpt (v_mail_conn, 'aliatesbilgisayar@yahoo.com');
--Mail içeriği burada aslında
UTL_SMTP.DATA
(v_mail_conn,
'Date: '
|| TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
|| crlf
|| 'From: '
|| v_from
|| crlf
|| 'Subject: '
--başlığımızı belirtiyoruz.
|| v_subject
|| crlf
--burada TO : yazmasını istediğimiz yazıyoruz.
--dongu ile buraya otomatik parametre eklenebilir.
|| 'To: adi bende sakli'
|| crlf
|| 'MIME-Version: 1.0'
|| crlf
||
'Content-Type: multipart/mixed;'
|| crlf
|| ' boundary="-----SECBOUND"'
|| crlf
|| crlf
|| '-------SECBOUND'
|| crlf
--içeriğin tipinide burada girdik.
|| 'Content-Type: text/plain;'
|| crlf
|| 'Content-Transfer_Encoding: 7bit'
|| crlf
|| crlf
|| 'Bu bir bilgilendirme mailidir. Günlük olarak silinen ve yeni olusturulan database objelerini bildirir.'
|| crlf
--mesaj içerisinde yazmak istediğiklerimiz.
||
'Dosya ektedir Bilginize'
|| crlf
|| crlf
|| '-------SECBOUND'
|| crlf
|| 'Content-Type: text/plain;'
|| crlf
|| ' name="excel.csv"'
|| crlf
|| 'Content-Transfer_Encoding: 8bit'
|| crlf
|| 'Content-Disposition: attachment;'
|| crlf
--oluşan dosyaya vereceğimiz isim.
|| ' filename="silinen_olusan.csv"'
|| crlf
|| mesaj
|| crlf
|| '-------SECBOUND--'
);
UTL_SMTP.quit (v_mail_conn);

EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
raise_application_error (-20000, 'Unable to send mail: ' || SQLERRM);
END;
/

BÜTÜN PARTITION'LARIN COUNT'UNU BİR SEFERDE ALMA

Bir tabloda bulunan partitionların count'larını teker teker almaktansa hepsini bir seferde alabiliriz. bunun için yapıalcak islem basit.

öncelik ile procederure yaratalım.

CREATE OR REPLACE PROCEDURE part_count (pin_table_name IN VARCHAR2)
IS
CURSOR s1
IS
SELECT /*+ parallel(t,8) */
partition_name AS v_part_name
FROM user_tab_partitions t
WHERE table_name = pin_table_name;

rec_s1 s1%ROWTYPE;
v_table_name VARCHAR2 (30);
v_dynsql VARCHAR2 (200);
v_sonuc VARCHAR2 (200);
BEGIN
v_table_name := pin_table_name;
OPEN s1;
LOOP
FETCH s1
INTO rec_s1.v_part_name;
EXIT WHEN s1%NOTFOUND;
v_dynsql :=
'select /*+ parallel(t,8) */ count(1) as v_sonuc from '
|| v_table_name
|| ' partition ('
|| rec_s1.v_part_name
|| ') t';

EXECUTE IMMEDIATE v_dynsql
INTO v_sonuc;

DBMS_OUTPUT.put_line (v_dynsql || ' --' || v_sonuc);
--DBMS_OUTPUT.put_line (v_sonuc);
-- DBMS_OUTPUT.put_line ('DENEME');
END LOOP;

CLOSE s1;
END;
/
--partitionlu yapıya sahip bir tablomuz olması gerekiyor. Bunuda hemen yaratalım.

create table part_deneme

(OWNER VARCHAR2(30 BYTE) NOT NULL,

OBJECT_NAME VARCHAR2(30 BYTE) NOT NULL,

SUBOBJECT_NAME VARCHAR2(30 BYTE),

OBJECT_ID NUMBER NOT NULL,

DATA_OBJECT_ID NUMBER,

OBJECT_TYPE VARCHAR2(19 BYTE),

CREATED DATE NOT NULL,

LAST_DDL_TIME DATE NOT NULL,

TIMESTAMP VARCHAR2(19 BYTE),

STATUS VARCHAR2(7 BYTE),

TEMPORARY VARCHAR2(1 BYTE),

GENERATED VARCHAR2(1 BYTE),

SECONDARY VARCHAR2(1 BYTE)

)

partition by range (object_id)

(partition part_01 values less than (20000) tablespace users,

partition part_02 values less than (40000) tablespace example,

partition part_03 values less than (60000) tablespace users,

partition part_04 values less than (maxvalue) tablespace users)

tabloyu ayrattık şimdi içine verileri ekleyelim.

insert into part_deneme select * from all_objects

--sonra bu nu çalıştıralım.
DECLARE
PIN_TABLE_NAME VARCHAR2(200);

BEGIN
PIN_TABLE_NAME := NULL;

HR.PART_COUNT ('PART_DENEME');
COMMIT;
END;

VİRGÜL İLE AYRILMIŞ DOSYADAN KAYITLARI TABLOYA AKTARMA

--öncelik ile yükleme yapacağımız tabloyu yaratalım.

create table kisiler(adi varchar2(30),
soyadi varchar2(30),
yasi number);

--Küçük bir not ekleyelim hemen araya. burada directory  adını verirken buyuk harf ile vermeyi

--unutmayalım. dosyadan okuduğu için buyuk hart vermeniz gerekecektir.

--paketimizi yaratalım.

CREATE OR REPLACE PROCEDURE dosyadan_yukleme
AS
v_line VARCHAR2 (2000); -- dosyadan okunacak her bir satır
v_file UTL_FILE.file_type; -- Verilerin olduğu dosya
v_dir VARCHAR2 (250); -- dosyanın bulunduğu dizin
v_filename VARCHAR2 (50); -- dosyamızın adı
v_1st_virgul NUMBER; --ilk virgulun olduğu yer
v_2nd_virgul NUMBER; --ikinci virgülün olduğu yer
v_adi VARCHAR2(30); --alanlarımız için değişkenler ADI
v_soyadi VARCHAR2 (30); --alanlarımız için değişkenler SOYADI
v_yasi NUMBER; --alanlarımız için değişkenler YASI
BEGIN
v_dir := 'dosya_yolum';
v_filename := 'kisiler.txt';
v_file := UTL_FILE.fopen (v_dir, 'kisiler.txt', 'r',1000);

-- --------------------------------------------------------
--DOSYA SONUNA GELENE KADAR OKU
-- --------------------------------------------------------
LOOP
BEGIN
UTL_FILE.get_line (v_file, v_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;

v_1st_virgul := INSTR (v_line, ',', 1, 1);
v_2nd_virgul := INSTR (v_line, ',', 1, 2);
v_adi := SUBSTR (v_line, 1, v_1st_virgul - 1);
v_soyadi :=
SUBSTR (v_line, v_1st_virgul + 1, v_2nd_virgul - v_1st_virgul - 1);
v_yasi := SUBSTR (v_line, v_2nd_virgul + 1);
DBMS_OUTPUT.put_line (v_adi|| ' - ' || v_soyadi || ' - ' || v_yasi);

-- ------------------------------------------
-- Kayıtları oluşturduğumuz tabloya eklemeyi yapıyoruz.
-- ------------------------------------------
INSERT INTO kisiler
VALUES (upper(v_adi), UPPER (v_soyadi), v_yasi);
END LOOP;

UTL_FILE.fclose (v_file);
COMMIT;
END;
/

--dosyamda aşağıdaki gibi kayıtlar var.
--ali,ates,12
--ali,ateş,25
--nurullah,ünlü,24
--akın,yalçın,30



BEGIN
DOSYADAN_YUKLEME;
COMMIT;
END;

DECODE KULLANARAK HIZLI ÇALIŞAN KOD YAZMAK

-- asagidaki sorgular yavas çalisacaktir.
--
-- sorgu 1
SELECT COUNT (*), SUM (sal)
FROM emp
WHERE deptno = 10 AND ename LIKE 'SMITH%';
-------------------------------
--sorgu 2
-------------------------------
SELECT COUNT (*), SUM (sal)
FROM emp
WHERE deptno = 30 AND ename LIKE 'SMITH%';
-------------------------------
-- Ayni islem için DECODE kullanarak daha hizli sonuç olariz.
--
-------------------------------
SELECT COUNT (DECODE (deptno, 10, '*', NULL)) d10_count,
COUNT (DECODE (deptno, 30, '*', NULL)) d30_count,
SUM (DECODE (deptno, 10, sal, NULL)) d10_sal,
SUM (DECODE (deptno, 30, sal, NULL)) d30_sal
FROM emp
WHERE ename LIKE 'SMITH%';

OTOMATIK TRUNCATE TABLE İŞLEMİ

CREATE OR REPLACE PROCEDURE truncate_table (tbl_adinin_birazi IN VARCHAR2)
IS
v_dyntask VARCHAR2 (200);
trunc_tbl_adi VARCHAR2 (30 BYTE);
BEGIN
FOR trunc_tbl_adi IN
(SELECT table_name
FROM all_tables
WHERE owner ='HR' AND table_name LIKE '%' ||tbl_adinin_birazi|| '%')
LOOP
-- yukarida tablo adininin bir kismini like ifadesine sokarak arama yapiyoruz
-- bunun bu sekilde kullanilmasinin amaci gecici(temp) tablalara TMP_ile basalayabilir.
-- yada musteri ile ilgili tablolar MUS_ ile baslabilir.
-- bu örnekler arttiralibilir.
v_dyntask := 'truncate table ' || trunc_tbl_adi.table_name;

-- execute immediate ile çalistirlacak olan katar belirlendi.
EXECUTE IMMEDIATE v_dyntask;
-- commit leyerek tabloyu kaydediyoruz.
COMMIT;
-- burada döngü içerisine sokmamizdaki amaç birden fazla tablo olabilir
-- truncate ediledecek olan toplarin hepsinini bu isleme tabi olmasi için döngü içerisinde yapiyoruz.
END LOOP;
END truncate_table;

bu procedureyi kullanamak için aşağıdaki script işimizi görecektir.

DECLARE
tbl_adinin_birazi VARCHAR2 (30 BYTE) := 'ALI';
BEGIN
truncate_table (tbl_adinin_birazi);
END;

aman dikkat truncate table tehlikeli bir işlemdir. Yanliş ellere geçmesin :))

ayrıca yukaridaki kodda sadece truncate işlemi yapılmaz ayrıca drop işlemi de yapabilirsiniz. Drop yapmak için aşağıdaki değişikliği yapmanız yeterli oalcaktir.

v_dyntask := 'DROP table ' || trunc_tbl_adi.table_name;

LONG TİPİNDEKİ BİR SÜTÜNDA KAÇ KARAKTER OLDUĞUNU BULMAK

LONG bir alanda kaç karakter oldugunu bulmak bazen bizim için gerekli olbilir bunun gerekli olduğu durumlardan bir tanesi LONG bir alanı VARCHAR'2 alana dönüştürmek istediğinizde buraya sığmayabilir. bunun için önceden bir bilgi edinmek için buna ihtiyaç duyarsınız. Daha sonra beklenmedik bir hata alip programın çalışmasında sorun çıkmasın diye

Önce LONG bir sütün içeren tablo yaratalım.

create table LONG_TABLE (long_alanim LONG);

insert into LONG_TABLE values (‘buraaysdşlaksjfiaslkfjlasjfasjfaifkasşlfja’);

select length(long_alanim) from long_table;

bunun ile uzunluğunu bulmaya çalışır isek

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

hatsını alacağiz. Bunu aşmak için

yarım kaldi devamı gelecek… :(

declare

a number;

begin

for  j in (select long_alanim from LONG_TABLE) loop

 a:=UTL_RAW.LENGTH (j.LONG_ALANIN);

 dbms_output.put_line(a);

end loop;

end;

/

ORA-39726: unsupported add/drop column operation on compressed tables

Bu hatayı açıklamaya çalışacağım.

Önce örnek bir tablo oluşturalım.

create table sil_sutun (adi varchar2(100),soyadi varchar(122));

Bugün bir tablodaki sütünü (column) silmem gerekti. Bu işlem için

alter table sil_sutun drop column soyadi;

komutunu kullandim. Ve bana

ORA-39726: unsupported add/drop column operation on compressed tables

Hatasını üretti. Hata kodunu googlede araştırmadan önce compressed i görünce dedim bu tablo sıkıştırılmış, normal haline getirirsem düzelir diye düşündüm ama işe yaramadı.

alter table sil_sutun NOCOMPRESS

ufak bir google aramasından sonra çözum yolunu buldum, Drop etmeden önce column u unused konumuna çekiyoruz. Daha sonra drop işlemi gerçekleştiriyoruz.

alter table sil_sutun set unused (soyadi);

işlem tamam komut çalışmaya hazır.

alter table sil_sutun drop column soyadi;

eğer bu da işe yaramaz ise tablomuzun scriptini alıyoruz. Ve tablonun scriptinde ufak bir değişiklik yapıyoruz.

CREATE TABLE SIL_SUTUN
(
ADI VARCHAR2(100 BYTE),
SOYADI VARCHAR2(122 BYTE)
)
TABLESPACE TBS_DATA_TEMP
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 160K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

Bu şekilde yeniden oluşturduktan sonra drop column işlemlerini yapabiliyoruz. Şimdi diyeceksiniz arkadaşım tabloyu yeniden oluşturken o column u yazmam olur biter bana tablo yeniden oluşturmadan lazım. İşte bu durumda benim buldugum çözum saçma, ama ne faydası tabloda column u değiştirme ihtimali var ise nocompress ile yaratmak gerekebilir ama bununda zaafları vardir. Araştırmak lazım bilen varsa yorumlar kısmına yazabilir.

SORGU SONUCUNU DİZİYE AKTARMA

Aşağıda yazmaya çalılşıtım procedure ile tablo sonucunu diziye aktardim ve dha sonra ekrana yazdırdım. Tabloyu diziye aktarma işlemi diyebiliriz.

DECLARE
--cursor tanımla
CURSOR mycursor
IS
SELECT table_name
FROM user_tables;

--diziyi type olarak tanımla
--dizi boyunu iyi belirlemek gerekiyor çok fazla verince hata üretiyor. Az verincede yetmiyor.
TYPE tables_dizi_type IS VARRAY (10000) OF user_tables%ROWTYPE;

-- yeni bir dizi oluştur.
tables_dizi tables_dizi_type;
-- dizide dolaşmak için değişkenler
index1 NUMBER;
index2 PLS_INTEGER;
BEGIN
--başlangıç değerini 0 olrak belirliyoruz. diziler sıfırdan başladığı için.
index1 := 0;
tables_dizi := tables_dizi_type ();

--cursor ile for döngüsü yap sonuna kadar dolaş.
FOR for_cursor IN mycursor
LOOP
--dizi elemanın değerini birer artir.
index1 := index1 + 1;
tables_dizi.EXTEND ();
tables_dizi (index1).table_name := for_cursor.table_name;
--diziye atama işlmemini yap.
END LOOP;

--ekrana yazmak için aşağıdaki yapı yeterlidir.

FOR index2 IN 1 .. tables_dizi.COUNT
LOOP
DBMS_OUTPUT.put_line (tables_dizi (index2).table_name);
END LOOP;
END;

CURSOR KULLANIMI VE BAZI BASİT ÖRNEKLER

DECLARE
CURSOR c1
IS
SELECT ali.country_id, ali.country_name
FROM aliates ali;

-- yeni_country_id ali.country_id%TYPE;

-- yeni_country_name ali.country_name%TYPE;

--burada ali alias ı global olmadıgı için select ifadesi bittiğinden ali değeri gidecektir.

--bu sesberen dolayı tablo adını kullanarak çağırmanız gerekecektir. aksi halde hata alırsınız.
yeni_country_id aliates.country_id%TYPE;
yeni_country_name aliates.country_name%TYPE;
BEGIN
OPEN c1;

LOOP
FETCH c1
INTO yeni_country_id, yeni_country_name;

EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line (yeni_country_id);
END LOOP;

CLOSE c1;
END;

Cursor sonuna gelene kadar ekrana yazmaya devam eder.

//////////////////////////////////////////////////////////////////////////////////////////////////////

DECLARE
sqlstring VARCHAR2 (500);

--sqlstring1 VARCHAR2 (200);
TYPE mycursor_type IS REF CURSOR;

mycursor mycursor_type;
cikti VARCHAR2 (100);
BEGIN
sqlstring := 'select TABLE_NAME from user_tables ';

OPEN mycursor FOR sqlstring;

FETCH mycursor
INTO cikti;

WHILE NOT mycursor%NOTFOUND
LOOP
--sqlstring1 := cikti;
DBMS_OUTPUT.put_line (cikti);

FETCH mycursor
INTO cikti;
END LOOP;
END;

Cursor kullanımı ile ilgili başka bir örnek bu örnekte while kullanılıyor. Sorgunun sonuçları cursor’e atılıyor daha sonar while ile (sonuna kadar gidilerek)cursordaki bütün elemanları ekrana tek tek yazıdırılıyor.

for döngüsü kullanımı

For dongusu kullarak kayıtları cekmek işlemi for döngüsünün başka bir kullanım şeklinide burada görüyoruz.

DBMS_OUTPUT.put_line (c1.owner);

ile ekrana yazdırırken c1 cursor’unun elemenlarını java oldugu gibi belirtmek gerekiyor.

BEGIN

FOR c1 IN (SELECT *

FROM dba_tables)

LOOP

DBMS_OUTPUT.put_line (c1.country_id);

END LOOP;

END;

ZAMANI BİRER BİRER ARTTIRMA

Günleri birer birer arttırmak için aşağıdaki kod güzel bir yöntem
SET SERVEROUTPUT ON

DECLARE
test_date DATE;
BEGIN
test_date := TO_DATE ('28-mar-2009', 'dd-mon-yyyy');

FOR a IN 1 .. 5
LOOP
DBMS_OUTPUT.put_line ( TO_CHAR (test_date, 'dd-Mon-yyyy') );
test_date := test_date + 1;
END LOOP;
END;
/

28-Mar-2009
29-Mar-2009
30-Mar-2009
31-Mar-2009
01-Nis-2009
PL/SQL procedure successfully completed.

şu şekilde de kullanabiliriz.

select to_char(to_date('20080101','yyyymmdd')+50) into test_date from dual;

DECODE İLE SEÇİM YAPMAK VE ALTERNATİFLERİ

DECODE Komutunu kullanırken aşağidaki gibi yazarsanız hem daha okunaklı olacak hemde geliştirmeniz kolay olacaktir.

decode ( :message_type
      , 'NEWBUY',         'NEWBUY'
      , 'REPAIR',         'REPAIR'
      , 'NEWBUY,REPAIR',  ( SELECT  MIN (MESSAGE_TYPE)
                            FROM    xxrb_requisitions_in_stage
                            WHERE   MESSAGE_TYPE IN ('NEWBUY', 'REPAIR')
                           )
      );
yada message_type degerini where koşulu ile şartlayabilirsiniz.

SELECT *
FROM   xxrb_requisitions_in_stage
WHERE  MESSAGE_TYPE IN DECODE (:MESSAGE_TYPE,
                              'NEWBUY', 'NEWBUY',
                              'REPAIR', 'REPAIR',
                              'NEWBUY,REPAIR', MESSAGE_TYPE
                             )
AND    MESSAGE_TYPE IN ('NEWBUY', 'REPAIR')

hic decode kullanmayıp bunun yerine message_type ı LIKE  KOMUTU ile de sorgulayabilir

WHERE  :message_type  LIKE '%' || message_type || '%'

WHERE  ',' || :message_type || ','  LIKE '%,' || message_type || ',%'

////////////////////////////////////////////////////////////////////////////////////////

WHERE   CASE
           WHEN  :message_type = message_type
               THEN  'Okay'
           WHEN  :message_type = 'NEWBUY,REPAIR'
           AND   message_type IN ('NEWBUY', 'REPAIR')
               THEN  'Okay'
       END = 'Okay'
 

Girilen değerin sayi olup olmadiğini kontrol etme

http://forums.oracle.com/ gezinirken çok güzel bir başlik dikkatimi çekti http://forums.oracle.com/forums/thread.jspa?threadID=371057&tstart=30 incelemenizde fayda var. çeşitli çözum önerileri sunulmuş bazıları çürütülmüş. Bana İsmail Hocanın (ismail kadayif) formal dillerde anlattiği konular geldi aklima direk. bunlari yapmaya çalişir duruduk. O zaman derdim hazir fonksiyonlar var yapıyor neden kasıyoruz bu kadar ama şimdi daha iyi anlıyorum o derslerin faydasını. ben bunu görmuştum işte bu işe yarıyor diyor insan.

pl/sql de dönüşüm fonksiyonları

Oracle'de dönüşüm fonksiyonları (Oracle Conversion Functions) tipleri birbiren dönüştürmede ve zaman fonksiyonları içerisinden oynama yapmakta kullanabiliriz.
SELECT TO_CHAR(SYSDATE, 'CC') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DY') FROM dual;

gibi çeşitli örnekler mevcud.

ilgili link