Oracle Uygulamaları: 2011

Alıntıdır :
Programmer Blog: oracle flashback query: "son zamanlarda sıkça hata yapmaya başladım.yaşlandım herhalde bu işler için. bi delete veya bi update yapıyorum sonra commit diyorum ardında..."

oracle flashback query

son zamanlarda sıkça hata yapmaya başladım.yaşlandım herhalde bu işler için. bi delete veya bi update yapıyorum sonra commit diyorum ardından eyvah diyorum:) sonra da hemen neydi lan bişi vardı tablonun eski tarihli bir görüntüsünü alabiliyorduk diyorum.google a oracel flashback query yazıyorum sonra scn(system change number) li sonuçlar dönüyor falan. ben bilmiyorum scn falan diyorum bana zamanla ilgili bişiler ver bu sefer timestamp çıkıyor karşıma... sonuçta aşağıdaki query i yazabiliyorum.

SELECT *
FROM stn_ihale_yaklasik_maliyet
AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24)

query den de anlaşılacağı üzere arkadaş bize tablonun bir saat önceki veri kümesini veriyor.

burda dikkat edilmesi gereken üç konu var.
1- bu özelliği kullanabilmek için database'de ayarlamak yapmak gerekebilir.(default olarak açıktır karıştırmayın yani:) 10g ile birlikte gelmiştir diye biliyorum)
2- tablo yapısı değişmemiş olmalıdır.yani siz kolon ekler veya çıkarırsanız geçmiş olsun.
3- from yanına sadece tek tablo yazabiliyorsunuz. tavsiyem select in başına
create table tmp as
yazıp sonra gerekli işlemleri yapmanızdır.

birde en fazla bir hafta geriye dönebiliyorsunuz default ayarlarla.

detaylı bilgi için
http://psoug.org/reference/flash_query.html
http://www.oracle-base.com/articles/10g/Flashback10g.php

Alıntı dışı :

SELECT *
FROM XXYSM.MERICSTOK
AS OF SCN TIMESTAMP_TO_SCN (SYSTIMESTAMP - (1-(1439/1440)))
-- tablonun 1 dk onceki halini verir
-- 1440 - dk değeri 1439 yerine yazılmalıdır...

5 dk onceki halini istiyorsak

SELECT *
FROM XXYSM.MERICSTOK
AS OF SCN TIMESTAMP_TO_SCN (SYSTIMESTAMP - (1-(1435/1440)))

yazılmalıdır...

SELECT u.user_name

FROM fnd_user u

WHERE u.creation_date > SYSDATE – 120

Şeklinde yazılan sorguda sonuç

Ahmet

Mehmet

Veli

…

Olsun. Amacımız oluşan bu listeyi tek satirda araya bizim belirleyeceğimiz bir ayraç ile birleştirerek

Ahmet,Mehmet,Veli, ….

gibi getirmek ise şu yöntemi uygulayabiliriz…

Öncelikle liste için bir tip belirleriz…

CREATE TYPE TListe IS TABLE OF VARCHAR2 (4000);

Bir de bu listeyi belirleyeceğimiz ayraç ile birleştiren fonksiyonu oluştururuz…

CREATE FUNCTION apps.birlestir (p_liste IN tliste, p_separator IN VARCHAR2)

RETURN VARCHAR2 IS

ret VARCHAR2 (4000);

BEGIN

FOR j IN 1 .. p_liste.LAST LOOP

IF j = 1 THEN

ret := p_liste (j);

ELSE

ret := ret || p_separator || p_liste (j);

END IF;

END LOOP;

RETURN ret;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END;

Şimdi sorgumuza geçebiliriz…

SELECT apps.birlestir (CAST (MULTISET (SELECT u.user_name

FROM fnd_user u

WHERE u.creation_date > SYSDATE - 120) AS apps.tliste),

',')

isimler

FROM DUAL;

Sonuç :

isimler

Ahmet,Mehmet,Veli,…

Olur…

Satirdan sutuna geçirmenin diger bir yöntemi de

SELECT TRUNC (f.creation_date) tarih, RTRIM (XMLAGG (XMLELEMENT (e, f.user_name || '-' || f.user_id || ',')).EXTRACT ('//text()'), ',') isimler

FROM fnd_user f

GROUP BY TRUNC (creation_date);

Tarih isimler

01.01.2011 Ahmet-1,Mehmet-2

01.02.2011 Veli-3, Mustafa-4

Gibi sonuclar olacaktir…

Ayraç ile birleştirilmiş bir alandan istenen parçaların getirilmesinin sağlanmasi

Örneğin;

Ne 30/1 - 12630037%76|100/36 den - 101721%24

Yukaridaki alanda ayraç “|” olarak belirlenmiş..

Biz bu ayraca gore

Ne 30/1 - 12630037%76

100/36 den - 101721%24

Değerlerinin alınmasını istersek su fonksiyonu yazabiliriz…

CREATE OR REPLACE FUNCTION APPS.explode (

p_seperator IN VARCHAR2,

p_string IN VARCHAR2,

p_count NUMBER

)

RETURN VARCHAR2

l_string LONG DEFAULT p_string || p_seperator;

TYPE listtable is TABLE OF VARCHAR2 (255);

l_data listtable := listtable ();

n NUMBER;

RESULT VARCHAR2 (255);

BEGIN

LOOP

EXIT WHEN l_string IS NULL;

n := INSTR (l_string, p_seperator);

l_data.EXTEND;

l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_string, 1, n - 1)));

l_string := SUBSTR (l_string, n + 1);

END LOOP;

RESULT := l_data (p_count);

RETURN RESULT;

EXCEPTION

WHEN OTHERS

THEN

RETURN RESULT;

END;

FROM DUAL;

Çıktı :

Orj ilk ikinci

Ne 30/1 - 12630037%76|100/36 den - 101721%24 Ne 30/1 - 12630037%76 100/36 den - 101721%24

Bu örneği bir üst level a alirsak…

Ayraç ile ayırmanın en fazla 8 adet olabileceğini bilerek eklenen herbir değeri satir satir göstermek isteyelim…

FROM dual k

Bu sorgu ile tek satıra düşüyoruz…

SELECT ROWNUM,CASE

WHEN ROWNUM = 1 THEN ilk

WHEN ROWNUM = 2 THEN iki

WHEN ROWNUM = 3 THEN uc

WHEN ROWNUM = 4 THEN dort

WHEN ROWNUM = 5 THEN bes

WHEN ROWNUM = 6 THEN alti

WHEN ROWNUM = 7 THEN yedi

WHEN ROWNUM = 8 THEN sekiz

END bilgi

FROM dual k)

CONNECT BY LEVEL <= 8

Çıktı:

1 TEKSTURE - 3R28884%7,31

2 TEKSTURE - 010015504KREM01%25,27

3 TEKSTURE - 010007255BEJ01%4,65

4 TEKSTURE - 3R28978%16,46

5 TEKSTURE - 3R28978%4,3

6 TEKSTURE - 3R2

Şeklinde olur…

Procedure ise

CREATE FUNCTION APPS.explode (

p_seperator IN VARCHAR2,

p_string IN VARCHAR2,

p_count NUMBER

)

RETURN VARCHAR2

l_string LONG DEFAULT p_string || p_seperator;

TYPE listtable is TABLE OF VARCHAR2 (255);

l_data listtable := listtable ();

n NUMBER;

RESULT VARCHAR2 (255);

BEGIN

LOOP

EXIT WHEN l_string IS NULL;

n := INSTR (l_string, p_seperator);

l_data.EXTEND;

l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_string, 1, n - 1)));

l_string := SUBSTR (l_string, n + 1);

END LOOP;

RESULT := l_data (p_count);

RETURN RESULT;

EXCEPTION

WHEN OTHERS

THEN

RETURN RESULT;

END;

Random veriye ihtiyaç duyulursa

SELECT rownum,TRUNC (DBMS_RANDOM.VALUE (1, 100)) a,

DBMS_RANDOM.STRING ('U', 20) b, DBMS_RANDOM.STRING ('L', 20) c,

DBMS_RANDOM.STRING ('A', 20) d, DBMS_RANDOM.STRING ('P', 20) e,

DBMS_RANDOM.VALUE (1, 100) f, DBMS_RANDOM.VALUE g,

DBMS_RANDOM.random h

FROM DUAL

CONNECT BY LEVEL <= 7

U - Upper case

L - Lower case

A - Alphanumeric

X - Alphanumeric with upper case alphabets.

P - Printable characters only.

--------

SELECT DISTINCT k.ad, wm_concat (DISTINCT k.soyad) soyadlar, COUNT (DISTINCT k.soyad)
FROM sevk.kullanici k
GROUP BY k.ad
ORDER BY COUNT (DISTINCT k.soyad) DESC

wm_concat kullanarakta yukarıdaki gibi bir sorgu çalıştırılabilir...

Oracle Uygulamaları

Sayfalar

7 Ağustos 2011 Pazar

Oracle EBS de Trace Kullanımı

23 Temmuz 2011 Cumartesi

Programmer Blog: oracle flashback query

oracle flashback query

29 Haziran 2011 Çarşamba

PL/SQL de satırdan sutuna , sutundan satıra çevirme, crosstab , row to column , column to row