Splere Opsiyonel Input

Uğur Sak isimli arkadaşım tarafından yazılmış açıklamayı tarihe not düşüyorum. Dikkat faydalıdır.

İhtiyaçtan dolayı finansal splerimize opsiyonel input eklememiz gerekti. Benim gibi daha önce bilmeyenler varsa bilgi vermeye çalışalım. Parametrik olarak yarattığınız input gelmediği zaman otomatik null değeri ile doldurulur.

Örnek bir sp ile açıklamaya çalışalım. pi_test3 ve pi_test4 ün opsiyonel olduğu bir sp yaratalım.

CREATE OR REPLACE PROCEDURE Deneme (pi_test1 IN       VARCHAR2,
pi_test2  IN       VARCHAR2,
pi_test3  IN       VARCHAR2 DEFAULT NULL, —-parametrik input
pi_test4  IN       VARCHAR2 DEFAULT NULL) —-parametrik input

Referans Vermeden Çağırma;

Var olan splerimiz de böyle bir input eklerseniz sona eklemeniz gerekmektedir. Sebebi spleri kullandığımız yerde referanssız olarak çağırıyoruz. Ya bu kullanımları düzeltmemiz gerekiyor yada tek input eklemenizi tavsiye ediyoruz.
Çünkü referanssız olarak çağırdığımızda spler input sırasına göre atama yapıyor.

Deneme ( ‘1’,’2’);            olarak çağırabiliriz.
Deneme ( ‘1’,’2’,’3’);       olarak çağırabiliriz.
Deneme ( ‘1’,’2’,’3’,’4’);  olarak çağırabiliriz.
Deneme ( ‘1’,’2’,’4’);       olarak çağıramayız. Çağırırsak  pi_test3 e = 4 değeri atanmış olur.

Referans Vererek Çağırma;

Deneme (pi_test1 => ‘1’,
pi_test2 => ‘2’,
pi_test3 => ‘3’,
pi_test4 => ‘4’)

Deneme (pi_test1 => ‘1’,
pi_test2 => ‘2’

Deneme (pi_test1 => ‘1’,
pi_test2 => ‘2’,
pi_test3 => ‘3’)

Deneme (pi_test1 => ‘1’,
pi_test2 => ‘2’,
pi_test4 => ‘4’)

Birden fazla kolonu IN ile kullanmak

Bu kadar :

SELECT *
FROM hayvanlar h
WHERE

(h.adi, h.ailesi, h.ayak_sayisi)

IN

(SELECT o.adi,o.ailesi,o.ayak_sayisi
FROM hayvan_ozellikleri o
WHERE o.adi = ‘MAYMUN’)

Fakat statik değerler ile kullanmaya izin vermiyor. yani ” IN ((‘TOPAÇ’,’KÖPEK’,’4′),(‘BONCUK’,’AT’,’4′)) ” şeklinde kullanılmıyor.

Oracle’da bir sub procedure dışarıdan kullanılabilir mi ?

Hayır.

OrnekProcedure.AltProcedure(p1,p2);

şeklinde bir kullanım olmuyor.

Oracle – Ref Cursor , Oracle’da sonuç olarak tablo dönmek

Aşağıdaki prosedür out parametresi olarak bir cursor dönecek. Dönen bu cursor diğer bir prosedür içerisinde kullanılabilir.

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS  -- Burada out olarak tanımlandı
BEGIN 
  OPEN p_recordset FOR -- Burada açıldı
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

–

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);

  LOOP  -- bu döngü içerisinde okunmaya başlandı Allah kabul etsin.
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
END;
/

Bu cursor :

• PL/sql içerisinde
• ADO Recordset olarak
• Java Resultset olarak

kullanılabiliyor.

Kaynak : http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

PL/SQL Select Into Hilesi

SELECT INTO kullanırken değer bulunamadığında hata fırlatması engellenmek istiyorum ve begin-exception-end bloğuna almak istemiyorum. O halde :

SELECT MAX(ORNEK_DEGER) INTO P_VALUE FROM ORNEK_TABLO;

Böyle yaptığımda veri dönmese dahi hata fırlatılmayacak.

 

Oracle Dinamik Sorgu

Bir dinamik sorguyu adamakıllı çalıştırma yöntemini araştırırken kaynak kısmında verdiğim makaleyi okudum. Okumak isteyen için güzel bir makale. Özetle :

select num
from (select distinct q.NUM
       from cqqv q 
       where  (q.bcode = :bcode) 
                  and  (1=1 or :lb is null) 
                  and  (1=1 or :type is null) 
                  and  (q.edate> :edate) 
                order by dbms_random.value()) subq 
where rownum <= :numrows

Sonucunu üreten bir dinamik sorgu olşuturuluyor. Parametre değerleri sorgu oluşturulurken yazılmıyor. Sorgu içerisinde bind edilebilecek şekilde düzenleniyor.  Diğer tavsiye edilen yöntemler için aşağıdaki başlığa bakılmasını şiddetle tavsiye ederim. Bir dost.

http://stackoverflow.com/questions/1716590/pl-sql-optional-conditions-in-where-clause-without-dynamic-sql

Oracle’da hata mesajı ne kadar uzağa fırlatılabilir

Çok uzağa değil.

SUBSTR(SQLERRM, 1, 100);

şeklinde çabucak yakalanır. (SUBSTR ile ilk 100 karakteri alınır)

Oracle’da Açık Oturumlar (Session)

Oracle’da bir kullanıcıya ait açık olan oturumları  (session’ları) nasıl mı görürüz ?

SELECT a.spid,
b.sid,
b.serial# as ser#,
b.machine as box,
b.username as username,
b.osuser as os_user,
b.program as program
FROM v$session b,v$process a
WHERE b.paddr = a.addr
AND TYPE = ‘USER’
AND b.username = ‘KULLANICI_ADI’
ORDER BY spid;

Şeklinde görebiliriz.

Kaynak

Oracle Hint

SELECT /*+ index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;

t1 : tablo 1
t1_abc : tablo 1 üzerindeki index

t2 aynen..

Bu sorguda oracle cebren ve hile ile t1_abc indeksini kullanmaya ikna edilmiştir.

Oracle Sistem Tabloları

Aşağıda en çok kullanılan Oracle sistem tabloları alfabetik sıra ile bulunmaktadır.

Sistem Tablosu	Açıklama
ALL_ARGUMENTS	Nesne içerisinde kullanıcı tarafından erişilebilecek argümanlar
ALL_CATALOG	Kullanıcının erişebileceği bütün tablolar, view’lar, synonym’ler ve sequence’ler
ALL_COL_COMMENTS	Erişilebilen tablolara ve view’lara ait yorumlar
ALL_CONSTRAINTS	Erişilebilen tablolardaki kısıtlama tanımları
ALL_CONS_COLUMNS	Kısıtlama tanımlamalarındaki erişilebilen kolonlar ile ilgili bilgi
ALL_DB_LINKS	Kullanıcının erişebildiği veritabanı link’leri
ALL_ERRORS	Kullanıcının depolanan nesneler üzerinde oluşturmaya yetkili olduğu halihazırdaki hatalar
ALL_INDEXES	Kullanıcı tarafından erişilebilen tabloların üzerindeki index tanımları
ALL_IND_COLUMNS	Index’lerde bulunan kullanıcının erişebileceği tablolardaki kolonlar
ALL_LOBS	Kullanıcının erişebildiği tablolardaki LOB tanımları
ALL_OBJECTS	Kullanıcının erişebildiği nesneler
ALL_OBJECT_TABLES	Kullanıcının erişebildiği bütün nesne tabloları tanımları
ALL_SEQUENCES	Kullanıcının erişeibldiği sequence tanımları
ALL_SNAPSHOTS	Kullanıcının erişebildiği snapshot’lar
ALL_SOURCE	Kullanıcının oluşturmasına izin verilen mevcut nesnelerin kodları
ALL_SYNONYMS	Kullanıcının erişebildiği synonymler
ALL_TABLES	Kullanıcının erişebildiği ilişkisel tablo tanımları
ALL_TAB_COLUMNS	Kullanıcının tablo , view ve cluster’larına ait kolonlar
ALL_TAB_COL_STATISTICS	Kullanıcının tablo , view ve cluster’larına ait kolonlar
ALL_TAB_COMMENTS	Kullanıcının erişebildiği tablo ve view’lerin üzerindeki yorumlar
ALL_TRIGGERS	Kullanıcının erişebildiği bütün trigger’lar
ALL_TRIGGER_COLS	Kullanıcının tablolarındaki trigger’lar veya trigger kolonları
ALL_TYPES	Kullanıcının erişebildiği bütün tipler
ALL_UPDATABLE_COLUMNS	Bütün güncellenebilir kolon tanımları
ALL_USERS	Veritabanının bütün kullanıcılarının bilgisi
ALL_VIEWS	Kullanıcının erişebildiği view tanımları
DATABASE_COMPATIBLE_LEVEL	init.ora dan set edilen Database compatible parametresi
DBA_DB_LINKS	Veritabanındaki bütün veritabanı link’leri
DBA_ERRORS	Veritabanındaki nesnelere ait mevcut hatalar
DBA_OBJECTS	Veritabanındaki bütün nesneler
DBA_ROLES	Veritabanındaki bütün roller
DBA_ROLE_PRIVS	Rollere ve kullanıcılara verilen roller
DBA_SOURCE	Veritabanındaki bütün mevcut nesnelerin kodları
DBA_TABLESPACES	Bütün tablespace tanımları
DBA_TAB_PRIVS	Veritabanındaki bütün yetkilendirmeler
DBA_TRIGGERS	Veritabanındaki bütün trigger’lar
DBA_TS_QUOTAS	Bütün kullanıcılar için tablespace kotaları
DBA_USERS	Veritabanındaki bütün kullanıcılar
DBA_VIEWS	Veritabanındaki bütün view’lar
DICTIONARY	Veritabanındaki bütün data dictionary tabloları ve view’lar
DICT_COLUMNS	Data dictionary tabloları ve view’lardaki kolonlar
GLOBAL_NAME	global veritabanı adı
NLS_DATABASE_PARAMETERS	Veritabanındaki kalıcı NLS parametreleri
NLS_INSTANCE_PARAMETERS	Örneğe(instance) ait NLS parametreleri
NLS_SESSION_PARAMETERS	Kullanıcı oturumuna ait NLS parametreleri
PRODUCT_COMPONENT_VERSION	Bileşenler için versiyon ve durum bilgisi
ROLE_TAB_PRIVS	Tablo hakları ve rol yetkileri
SESSION_PRIVS	Kullanıcının halihazırdaki yetkileri
SESSION_ROLES	Kullanıcnın halihazırdaki rolleri
SYSTEM_PRIVILEGE_MAP	Erişim hakları tip kodları
TABLE_PRIVILEGES	Kullanıcının yetki verdiği ,yetki aldığı ,sahip olduğu , erişebilecek bir role sahip olduğu ve herkese açık olan nesneler üzerindeki yetkiler
TABLE_PRIVILEGE_MAP	Tablo erişim tipi kodları

Kaynak