Month: September 2014

PL/SQL Pivot Sorgu (Pivot Table Ozelligi)

CevheriLeave a comment

Merhaba,

Bugünkü yazımızda sql ile pivot sorgu özelliğini yapacağız. Eminim çok seveceksiniz. Excel’den alışkın olanlar bilirler. Çok harika bir özelliktir.

Biz firmaların illere göre dağılımını ele alacağız örnek çıktı şu şekilde olacak

FIRMAADI Konya Ankara İstanbul
Analiz Firması 0 1 0
Bakım 0 0 1
Global 0 0 1
Proje Firması 1 0 0
Satış Firması 0 0 1
Test Firması 0 0 1
Yazılım Firması 0 1 0

Sorgumuz da aşağıdaki gibi olacaktır.

select *
  from (select f.firmaadi, i.adi
          from firma f, ililce i
         where f.ilid = i.plakakodu
           and i.ilcekodu = 0) 
 pivot(count(*) for adi in('Konya', 'Ankara', 'İstanbul'))
 order by firmaadi;

İşte bu kadar basit. Artık bir istatistik verirken daha rahat edeceksiniz.

 

 

Oracle Bağlı Kullanıcıları(session) Topluca düşürme işlemi

Cevheri11 Comments

Sisteminizde yüzlerce bağlı kullanıcı olabilir.

Bazen bir alter, create vb..  Database’de bir işlem yapmak istersiniz;

ama bağlı kullanıcılar sizi LOCK konumuna düşürür ve db işlemleriniz kısıtlanır.

Sisteme bağlanıp bunları tek tek KILL ederiz. 198. useri tam KILL  etmişizdir ki….

27. user sisteme tekrar bağlanmıştır.  Neyse uzatmayalım ve hadi bunları topluca nasıl KILL ederiz ona bakalım.

 

declare
  cursor c_kill is
    select inst_id, sid, serial#
      from gv$session t
     where upper(username) = 'TEST'
        or upper(osuser) = 'TEST-USER'
        or upper(machine) = 'TEST-PC'
        or program = 'Toad.exe'; --vb.... buraya bir çok seçenek eklenebilir.
begin
  for r in c_kill loop
    --örnek Kullanım alter system kill session 'sid,serial#';
    execute immediate 'alter system kill session ' || '''' || r.sid || ',' || r.serial# ||'''';
  end loop;
end;

 

 

Gunluk Doviz Kuru & PL/SQL package

CevheriLeave a comment

 

Gunluk doviz kurlarını httpuritype ve XMLTYPE kullanarak 
nasıl çekildiğine bakacağız...

Bir yerlerde xml varsa; 
bırakın bunu oracle'ın efsane xmltype paketi yapsın. 

ben şimdilik bunu procedure veya function yapmadım. 
kodu sql editorunuzde çalıştırdığınızda output olarak 
o gunku doviz kurlarını ekrana yazacaktır. siz isterseniz bunu tabloya yazın;
isterseniz kontrol mekanizması yapın... size kalmış... '

--******************************************************************

DECLARE

  v_Urlxml httpuritype;
  v_xmltype xmltype;

BEGIN

  v_Urlxml := httpuritype.createuri('http://www.tcmb.gov.tr/kurlar/today.xml');
  v_xmltype := v_Urlxml.getxml();

  dbms_output.put_line(RPAD('Tarih', 15, ' ') || RPAD('Doviz Kodu', 15, ' ') || RPAD('Miktar', 15, ' ') ||
                       RPAD('Alış', 15, ' ') || RPAD('Satış', 15, ' ') || RPAD('E.Alış', 15, ' ') ||
                       RPAD('E.Satış', 15, ' '));
  dbms_output.put_line(RPAD('-------------------', 15, ' ') || RPAD('-------------------', 15, ' ') || RPAD('-------------------', 15, ' ') ||
                       RPAD('-------------------', 15, ' ') || RPAD('-------------------', 15, ' ') || RPAD('-------------------', 15, ' ') ||
                       RPAD('-------------------', 15, ' '));
  FOR r in (SELECT    EXTRACTVALUE(VALUE(x), '/Tarih_Date/@Tarih') tarih,
                      EXTRACTVALUE(VALUE(p), '/Currency/@CurrencyCode') currency_code,
                      EXTRACTVALUE(VALUE(p), '/Currency/Unit') unit,
                      EXTRACTVALUE(VALUE(p), '/Currency/ForexBuying') alis,
                      EXTRACTVALUE(VALUE(p), '/Currency/ForexSelling') satis,
                      EXTRACTVALUE(VALUE(p), '/Currency/BanknoteBuying') e_alis,
                      EXTRACTVALUE(VALUE(p), '/Currency/BanknoteSelling') e_satis
                 FROM TABLE(XMLSEQUENCE(EXTRACT(v_xmltype, '/Tarih_Date'))) x,
                      TABLE(XMLSEQUENCE(EXTRACT(VALUE(x), '/Tarih_Date/Currency'))) p
                WHERE EXTRACTVALUE(VALUE(p), '/Currency/ForexSelling') IS NOT NULL) LOOP
    dbms_output.put_line(RPAD(r.tarih, 15, ' ') || RPAD(r.currency_code, 15, ' ') || RPAD(r.unit, 15, ' ') ||
                         RPAD(r.alis, 15, ' ') || RPAD(r.satis, 15, ' ') || RPAD(r.e_alis, 15, ' ') ||
                         RPAD(r.e_satis, 15, ' '));
  END LOOP;

END;
/

--******************************************************************

output çıktısıda şu şekilde olacaktır.

Tarih          Doviz Kodu     Miktar         Alış           Satış          E.Alış         E.Satış        
---------------------------------------------------------------------------------------------------------
19.09.2014     USD            1              2.2232         2.2272         2.2216         2.2305         
19.09.2014     CAD            1              2.0272         2.0364         2.0197         2.0441         
19.09.2014     XDR            1              3.3328         
19.09.2014     DKK            1              0.38388        0.38577        0.38361        0.38666        
19.09.2014     SEK            1              0.31093        0.31415        0.31071        0.31487        
19.09.2014     CHF            1              2.3662         2.3814         2.3627         2.385          
19.09.2014     NOK            1              0.34984        0.35219        0.3496         0.353          
19.09.2014     JPY            100            2.0359         2.0494         2.0284         2.0572         
19.09.2014     SAR            1              0.59277        0.59384        0.58832        0.59829        
19.09.2014     KWD            1              7.6959         7.7966         7.5805         7.9135         
19.09.2014     AUD            1              1.9872         2.0001         1.9781         2.0121         
19.09.2014     EUR            1              2.8624         2.8675         2.8604         2.8718         
19.09.2014     GBP            1              3.6402         3.6592         3.6377         3.6647         
19.09.2014     RUB            1              0.05748        0.05823        
19.09.2014     RON            1              0.64644        0.6549         
19.09.2014     PKR            1              0.02153        0.02181        
19.09.2014     IRR            100            0.0083         0.00841        
19.09.2014     CNY            1              0.36003        0.36474        
19.09.2014     BGN            1              1.4553         1.4743

 

PL/SQL Package ile mail gönderme

Cevheri1 Comment

 

Oracle'ı sadece veri depolama sistemi olarak gördüğüm yıllarda, 
tird party programlarla yaptığım buna benzer işler 
aslında plsql ile çok basit olabiliyor... 

işte örnek bir plsql ile mail gönderme metodu. 
Utl_Mail oracle'ın kendi paketlerindendir.

  UTL_MAIL.send(sender     => 'plsqlturksender@plsqlturk.com',
                recipients => 'plsqlturk@plsqlturk.com',
                subject    => 'PL/SQL Türkten Size Mail Var',
                cc         => 'plsqlturkcc@plsqlturk.com',
                mime_type  => 'text/html; charset=iso-8859-9',
                message    => 'PL/SQL Türk Mesaj İçeriği');

(alter system set smtp_out_server = 'mailserver.domain.com';)
Tabi bunu yaparken mail server ayarlarınızın yapılmış olduğunu varsayıyoruz. 

Mail göndermenin smtp, tcp gibi oracle'ın hazır metodları bulunmaktadır. 
İnceleyip işinize hangisi yarıyorsa kullanabilirsiniz.

 

number to_tıme() metodu hazırlama

CevheriLeave a comment

 

Çok defa başıma gelmiş ancak her seferinde farklı kodlarla çözdüğüm bu işi 
metoda dökmek istedim.

Elimizde bir numara var ve biz bunu saniye cinsinden; time formatında bir değere
kolay bir şekilde çevirmek istiyoruz. 
Öncelikle IN parameter olarak numara alan ve geriye tarih ve saat döndüren 
bir metod yazalım ve bunun çağrılışına bir örnek verelim. 


create or replace Function NumberToTime(p_number in Number) return date is
  v_time   varchar2(8) := '00:00:00'; -- ilk değer atamalarımızı yapalım...
  v_Result date := to_date(trunc(sysdate) ||' '||  v_time, 'dd.mm.yyyy hh24:mi:ss');
begin
  -- gelen parametre dolu ise kodumuz çalışmalı
  -- sıfırdan büyük rakamlar için çalışmalı
  if p_number is not null and p_number > 0  then
    select to_char(trunc(p_number / 3600), 'FM9900') || ':' || 
           to_char(trunc(mod(p_number, 3600) / 60), 'FM00') || ':' ||
           to_char(mod(p_number, 60), 'FM00')
      into v_time
      from dual;
  end if;

  v_Result := to_date(trunc(sysdate) ||' '|| v_time, 'dd.mm.yyyy hh24:mi:ss');
  Return(v_Result);
Exception
  When Others Then
    -- herhangi bir hata olursa, 
    -- sysdate dönecektir.
    Return(v_Result);
end NumberToTime;


------------------------------------
-- call
select NumberToTime(121) from dual;
20.09.2014 00:02:01

------------------------------------

Metodumuza 121 diye bir rakam gönderdim; geriye 2 dakika 1 saniye olarak dönmüş oldu.
Bu şekilde bir numaranın, nasıl date time'a çevrildiğini öğrenmiş olduk.

 

Oracle Logon Trigger

CevheriLeave a comment 
Bir Database'in olmazsa olmazı,
Loglama, Güvenlik...

Bu yazımızda oracle logon triggerından bahsedeceğiz. 
Yapı itibariyle diğer triggerlardan çokta farkı yok aslında(syntax aynı) 
sadece tablo yerinde database'e bağlandığında bir iş yapmasını istiyoruz. 
Yazımı aşağıdaki şekildedir.

create or replace trigger trg_logonDB
after logon on database
declare
  Cursor c is
    Select * from v$session where audsid = userenv('sessionid');

  cp         c%rowtype;
  rp         logonhistory%rowtype;
  v_ipadress varchar2(1000);
  v_host     varchar2(1000);
  v_module   varchar2(1000);
begin
  -- Cevheri 13.09.2014
  -- db'ye login olan tum userlari takip edelim

  open c;
  fetch c
    into cp;
  close c;

  -- oracle'in kendi baglantilarini es gecelim..... 🙂
  if cp.type != 'BACKGROUND' then

      ---------------------------------------------------------------------
      select SYS_CONTEXT('USERENV', 'IP_ADDRESS') into v_ipadress from dual;
      select SYS_CONTEXT('USERENV', 'HOST')       into v_host     from dual;
      select SYS_CONTEXT('USERENV', 'module')     into v_module   from dual;



      ---------------------------------------------------------------------
      -- Rapor icin log tutmaya gerek yok
      -- Forms icin log tutmaya gerek yok
      if upper(v_module) not in('FRMWEB.EXE', 'REPORTS.EXE') then

        rp.kullaniciad      := cp.username;
        rp.username         := cp.osuser;
        rp.ip_address       := v_ipadress;
        rp.host_name        := v_host;
        rp.operating_system := v_module;
        rp.language         := userenv('LANG');
        rp.time_zone        := null;
        rp.logondate        := sysdate;
        rp.logoutdate       := null; -- logof triggerinda dolduracağız 🙂
        rp.dbusername       := user;
        rp.sid              := cp.sid;

        insert into logonhistory values rp;
        commit;

      end if;
  end if;

-- Güvenlik kısmına geçelim. 🙂 istediğine izin ver 
begin
  if sys_context('userenv', 'ip_address') ='192.168.1.1' then
    DbHata('Giriş Yasak .....');
  elsif sys_context('userenv', 'session_user') in ('PATRON') then
    DbHata('Şifreniz Bloke Olmuş :(');
  end if;
end;


exception
  when others then
    DbHata(sqlerrm);
end tr_logonDB;

işte bu kadar... logon triggerımız artık db'ye kim bağlanırsa bağlansın
loglama, güvenlik işlemlerini yapacaktır.

 

PL/SQL DE TRIGGER KULLANIMI

CevheriLeave a comment

Merhaba,
Oracle pl/sql ile trigger kullanımı inceleyelim. Öncelikle trigger nedir ne işe yarar buna bir göz atalım. Trigger(tetkikleyici) sistem tarafından belirli durumlarda otomatik olarak çalıştırılan bir programdır. Neden bir triggera ihtiyaç duyarız ? Örnek olarak tabloya bir kayıt eklediğimizde otomatik olarak kayıt tarihi olarak sistem tarihinin atması ya da tabloda bir güncelleme veya silme yaptığımızda ilgili kayıt için başka bir tabloya log kaydı atması gibi çeşitli sebepler ve kontroller için triggerlara ihtiaç duyarız. Tablolarda yaptığımız “insert , update , delete” işlemlerinde trigger çalışır ve bu çalışma “before , after” olmak üzere ikiye ayrılır. Before zamanında çalışan trigger bizim yaptığımız işlemden önce devreye girer ve ilgili trigger içine yazdığımız kontrol kodlarını ya da programı çalıştırır. After zamanında çalışan trigger ise tabloya yaptığımız işlemden sonra devreye girer ve içinde bulunan kontrolleri çalıştırır. Trigger(lar)’ın çalışma zamanlarını ve işlemleri daha iyi anlamak için örneklerimize bir göz atalım.
Öncelikle triggerlarımızı yazacağımız ve kontrolleri test edeceğimiz bir tabloya ihtiyacımız var ve hemen bu tabloyu oluşturalım. Trigger içerisinde bu tablo ile ilgili işlem yaparken sequence kullanacağımız için tablodan hemen sonra sequence imizi de yaratalım.

create table ORNEKTABLO
(
  kayitid   NUMBER,
  aciklama  VARCHAR2(250),
  kayittar  DATE,
  gunceltar DATE
);

create sequence ORNEKTABLO_SEQ
minvalue 0
maxvalue 999999999
start with 1
increment by 1
nocache;

Bu tablomuzun üzerine sırası ile before ve after zamanında çalışacak insert, update, delete triggerlarımız yazacağız. Trigger oluştuktan sonra her birisinin içerisine ilgili zamanda çalışacak olan kontrol kodlarımızı yazağız.
Birinci senaryomuz : Tabloya bir kayıt attığımızda tablodaki “kayitid” alanına sequence dan sıradaki değeri alıp ve “kayittar” alanına da günün tarihini ekleyen basit bir kontrol için insert işleminin before zamanında çalışacak olan bir trigger yazalım.

create or replace trigger ornektablo_before_ins
  before insert on ornektablo
  for each row
declare
  --lokal değişkenler tanımlanır.
begin

  if :new.kayitid is null then
    select ornektablo_seq.nextval into :new.kayitid from dual;
  end if;

  :new.kayittar := sysdate;

end ornektablo_before_ins;

Yukarıdaki kod bloğunda yazılı olan “before insert on ornektablo” tanımlaması hazırladığımız triggerın hangi tablo üzerinde, hangi işlem sırasında ve hangi zamanda çalışağını belirtmek için kullanılır. Ayrıca kod bloğunda görülen “:new” belirteci ise tabloya insert anında gelen ilgili kolon içeriğini belirtir. Şimdi yazığımız kodların çalışmasını test edelim. Tablomuza bir kayıt insert edelim ve bu işlem sırasında triggerda yazığımız konrtollerin çalışıp tabloyadaki “kayitid” alanına sequence deki sıradaki değeri ve “kayittar” alanına da günün tarihini atıp atmadığına bakalım. Bu işlem için aşağıdaki insert sqlini kullanabiliriz.

insert into ornektablo
  (kayitid, aciklama, kayittar, gunceltar)
values
  (null, 'Tabloya Kayıt Ekleme:Before', null, null);
commit;

Yukarıdaki sqlde de görüldüğü gibi sadece “aciklama” alanına değer ekliyoruz. Diğer atamalar trigger ile yapılacak. Bu kod çalıştıktan sonra tabloyu sorgulayıp sonucu görelim.

select * from ornektablo;
K.ID ACIKLAMA K.TAR G.TAR
1 Tabloya Kayıt Ekleme:Before 13.05.2013 20:07:01

Sorgu sonucunda da görüldüğü gibi tabloya insert işleminin before aşamasında yazdığımız kontroller çalışarak bizim null olarak atama yaptığımız alanlara ilgili değerleri ekledi. Bu şekilde birinci senaryomuzu tamamlamış olduk.
Continue reading →