In diesem Beitrag möchte ich drei verschiedene Möglichkeiten aufzeigen, wie man mit einfachen Mitteln eine LONG-Spalte nach CLOB – möglichst noch in einer Abfrage konvertieren kann.

Um das Ganze an einem praktischem Fall darzustellen, geht es im Folgendem um die LONG Spalte HIGH_VALUE der Tabelle DBA_TAB_PARTITIONS, die z.B. bei einer RANGE-Partitionierung die zu jeder Partition zugehörige obere Grenze darstellt. Um es etwas einfacher zu machen, nehmen wir in allen Fällen an, das der Partition-Key vom Typ DATE ist. Gerade im Zusammenhang mit HOUSEKEEPING es ist dann wichtig, alle diejenigen Partitionen zu finden, die älter als z.B. 30 Tage sind.

Die SQL Funktion TO_LOB kann leider (gemäß Doku) nur innerhalb eines INSERT Statements benutzt werden. Die einfachste Variante ist demnach:

1. INSERT INTO TABLE – Dazu wird zuerst eine Tabelle erstellt. Diese wird vor jedem Auslesen geleert und neu gefüllt und dann kann man die darin enthaltenen Daten aufbereiten. Das Ganze sähe dann in etwa so aus:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

   CREATE TABLE partitions( TABLE_NAME VARCHAR2(30) , part_name VARCHAR2(30) , POSITION NUMBER , high_value CLOB );   BEGIN DELETE FROM partitions ; INSERT INTO partitions( TABLE_NAME , part_name , POSITION , high_value) SELECT TABLE_NAME AS TABLE_NAME , partition_name AS partition_name , partition_position AS POSITION , to_lob(high_value) AS high_value FROM dba_tab_partitions p WHERE (TABLE_NAME NOT LIKE 'BIN$%') ; COMMIT; END; /

   CREATE TABLE partitions( table_name VARCHAR2(30) , part_name VARCHAR2(30) , position NUMBER , high_value CLOB ); BEGIN DELETE FROM partitions ; INSERT INTO partitions( table_name , part_name , position , high_value) SELECT table_name as table_name , partition_name as partition_name , partition_position as position , to_lob(high_value) as high_value FROM dba_tab_partitions p WHERE (table_name not like 'BIN$%') ; COMMIT; END; /

Um den Prozess des Leerens und Einfügens zu automatisieren, kann man an dieser Stelle eine Materialized View (MV oder MatView) einführen, die genau diese Aufgabe erledigt. Ist der Zeitpunkt der Abfrage bekannt (Batchjob), so kann man die MV vorher so definieren, dass sich diese selbständig refreshed. Da dies aber meistens nicht bekannt ist, muss man nur  die MV refreshen und dann abfragen. Das sähe dann in etwa so aus:

2. REFRESH MATVIEW – Im ersten Schritt wird einmalig die MV erstellt. Danach muss diese nur noch refreshed werden, bevor die man die Daten abfragen möchte:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

   CREATE MATERIALIZED VIEW partitions( TABLE_NAME , part_name , POSITION , high_value) PCTFREE 1 NOLOGGING build immediate refresh complete AS SELECT p.table_name AS TABLE_NAME , p.partition_name AS part_name , p.partition_position AS POSITION , to_lob(p.high_value) AS high_value FROM dba_tab_partitions p WHERE (p.table_name NOT LIKE 'BIN$%') ;   BEGIN DBMS_MVIEW.REFRESH (list => 'PARTITIONS', method => '?C', refresh_after_errors => TRUE, atomic_refresh => FALSE); END; /

   CREATE MATERIALIZED VIEW partitions( table_name , part_name , position , high_value) PCTFREE 1 NOLOGGING build immediate refresh complete as SELECT p.table_name as table_name , p.partition_name as part_name , p.partition_position as position , to_lob(p.high_value) as high_value FROM dba_tab_partitions p WHERE (p.table_name not like 'BIN$%') ; BEGIN DBMS_MVIEW.REFRESH (list => 'PARTITIONS', method => '?C', refresh_after_errors => TRUE, atomic_refresh => FALSE); END; /

In beiden gezeigten Möglichkeiten kann man dann das konkrete Datum und die Anzahl Tage zu heute mit folgender Abfrage sich anzeigen lassen:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT 	TABLE_NAME
	, part_name
	, POSITION
	, day_value 
	, CAST((trunc(SYSDATE) - trunc(day_value)) AS INTEGER)  AS nodays
FROM (  SELECT TABLE_NAME
		, part_name
		, POSITION
		, high_value
		, to_date(fixedday, substr(daymask, 1,instr(daymask,',')-1)) 	AS day_value 
	FROM (  SELECT TABLE_NAME
			, part_name
			, POSITION
			, high_value
			, CAST(substr(UPPER(high_value),
				instr(high_value,'TO_DATE')+LENGTH('TO_DATE')+1,
				instr(high_value,',')-LENGTH('TO_DATE')-2) AS VARCHAR2(50)) 	 AS fixedday
			, CAST(substr(UPPER(high_value),
				instr(high_value,',')+2,
				instr(high_value,')')-instr(high_value,',')-2) AS VARCHAR2(100)) AS daymask
		FROM partitions  )  )
ORDER BY 1, 2 DESC
;

Die dritte Variante bietet – speziell in diesem Fall – die Möglichkeit, alles ohne Hilfskonstruktion zu erreichen und die Konvertierung innerhalb einer Abfrage durchzuführen. Sie hat allerdings den Nachteil, dass sie sich je nach  Anwendungsfall nur schwer oder gar nicht adaptieren lässt:

3. XMLGEN  –  Durch die im Oracle Kernel eingebaute Funktion XMLGEN, die das Ergebnis einer übergebenen Abfrage in einer XML-Struktur als CLOB zurückgibt, lässt sich dies u.a. wie folgt bewerkstelligen:

   1 2 3 4 5 6 7 8 9 10

   SELECT TABLE_NAME AS TABLE_NAME , table_owner AS owner , partition_name AS part_name , partition_position AS POSITION , to_date(TRIM ('''' FROM regexp_substr (extractvalue ( dbms_xmlgen.getxmltype ('select high_value from dba_tab_partitions where table_name='''||TABLE_NAME||''' and table_owner='''||table_owner||''' and partition_name='''||partition_name||''''),'//text()'), '''.*?''')), 'syyyy-mm-dd hh24:mi:ss') AS high_value FROM dba_tab_partitions WHERE (TABLE_NAME NOT LIKE 'BIN$%') ORDER BY 2, 1, 4 DESC ;

   select table_name as table_name , table_owner as owner , partition_name as part_name , partition_position as position , to_date(trim ('''' from regexp_substr (extractvalue ( dbms_xmlgen.getxmltype ('select high_value from dba_tab_partitions where table_name='''||table_name||''' and table_owner='''||table_owner||''' and partition_name='''||partition_name||''''),'//text()'), '''.*?''')), 'syyyy-mm-dd hh24:mi:ss') as high_value from dba_tab_partitions WHERE (table_name not like 'BIN$%') order by 2, 1, 4 desc ;

Viel Erfolg beim Testen.