Einige neue Oracle9i Feature - Lambertz

Oracle 9i
New Feature
Stand: 16.06.2004
Autor: R. Lambertz
Oracle 9i – New feature
________________________________________________________________________________________________________________
1
ALLGEMEIN __________________________________________________________ 3
2
UNDO_RETENTION TIME ______________________________________________ 3
3
FLASHBACK QUERY ___________________________________________________ 3
4
VIEW CONSTRAINTS ___________________________________________________ 5
5
MATERIALIZED VIEWS (MV) ___________________________________________ 5
5.1
Materialized Views COMPLETE REFRESH __________________________________ 6
5.2
Materialized Views FAST REFRESH ________________________________________ 6
5.3
Materialized Views FAST REFRESH ON COMMIT____________________________ 6
5.3.1
5.4
6
ENABLE QUERY REWRITE ___________________________________________________ 7
Materialized Views FAST REFRESH ON COMMIT für Joins____________________ 8
TABLE PARTITIONING _________________________________________________ 9
6.1
Hash Partitioning ________________________________________________________ 11
6.2
List Partitioning _________________________________________________________ 11
7
INDEX ORGANIZED TABLE (IOT)_______________________________________ 12
7.1
Bitmap secondary index on ________________________________________________ 15
7.2
Hash Partitioning on______________________________________________________ 16
8
EXTERNAL TABLES (ETL) _____________________________________________ 16
8.1
ETL-Table Function ______________________________________________________ 18
8.2
ETL-Multi Table Insert ___________________________________________________ 20
8.3
ETL-Multi Table Merge___________________________________________________ 20
9
CHANGE DATA CAPTURE _____________________________________________ 21
10
EXPLAIN PLAN ZU ABGEARBEITETEN STATEMENTS __________________ 22
11
RESUMABLE SPACE MANAGEMET ___________________________________ 23
12
PL/SQL Erweiterungen ________________________________________________ 24
12.1 SELECT FOR UPDATE WAIT ____________________________________________ 24
12.2 Verbesserung des Bulk Bind Verfahrens _____________________________________ 24
12.3 CASE Konstruckte _______________________________________________________ 25
Seite 2/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
1
ALLGEMEIN
Oracle 9i wurde um einige neue Funktionen und Möglichkeiten erweitert, die
alle aufgezählt und beschrieben ein Buch gestalten kann. Hier werden nur
einige Neuerungen gezeigt, die gerade für Entwickler von Interesse sein
können.
2
UNDO_RETENTION TIME
Mit Hilfe dieses Parameters kann man den Fehler „SnapShot to old“ vermeiden.
ALTER SYSTEM SET UNDO_RETENTION = <seconds>;
Durch diese Angabe wird Oracle angewiesen, alle Rollbackinformationen für
eine Dauer von <seconds> Sekunden vorzuhalten.
Dieser Trick sollte nur eingesetzt werden, wenn langfristig geöffnete Cursor für
die Verarbeitung nicht vermieden werden können. Je größer die garantierte
Vorhaltedauer eingestellt wird, umso eher läuft das Plattensystem voll, da im
schlechtesten Fall sehr viele RollBackSegmente erzeugt werden.
Flashback query’s können nur dann eingesetzt werden, wenn die Datenbank Instanz
mit dem neuen „Automatic Undo Management“ verwaltet wird, was das traditionelle
RollBack Management ersetzt.
3
FLASHBACK QUERY
Bei dem Begriff „Flashback Query“ handelt es sich um die Möglichkeit, auf
Daten zurückzugreifen, welche mittlerweile verändert sind oder gar gelöscht
wurden. Diese Rückbetrachtung findet für Daten statt, welche mit einem DML
Statement bearbeitet und mit COMMIT festgeschrieben wurden.
Der Trick liegt darin, daß Oracle Rollback Informationen weiterhin vorhält, auch
wenn die zugehörige Transaktion mit COMMIT schon abgeschlossen ist und
diese Rolback-Informationen eigentlich verworfen werden könnten.
Flashback query’s können nur dann eingesetzt werden, wenn die Datenbank
Instanz mit dem neuen „Automatic Undo Management“ verwaltet wird, was das
traditionelle RollBack Management ersetzt.
Alte Rollbackinformationen leben nicht „ewig“. Die Vorhaltedauer wird gesteuert
über den Parameter UNDO_RETENTION, der standardmäßig in der initSID.ora
Datei gesetzt ist. Er kann auch innerhalb einer Session verändert werden kann,
wie das Beispiel zeigt:
ALTER SYSTEM SET UNDO_RETENTION = <seconds>;
Seite 3/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Nachfolgend wird der Einsatz einer Flashback Query gezeigt:
19:52:48 llam@ALVT > create table lc_vertraege as select * from vertraege;
Table created.
real: 10
19:56:16 llam@ALVT > alter system set undo_retention=90;
--1,5 Minuten
System altered.
real: 60
19:56:46 llam@ALVT > delete from lc_vertraege where ver_id = 100037112;
1 row deleted.
real: 30
19:56:54 llam@ALVT > commit;
Commit complete.
real: 10
19:58:00 llam@ALVT > select * from lc_vertraege as
of timestamp to_timestamp ('10-MAR-03 19:56', 'dd-mon-yy hh24:mi' )
where ver_id = 100037112;
*
ERROR at line 1:
ORA-08176: consistent read failure; rollback data not available
real: 31
19:58:01 llam@ALVT >
Die gesetzte UNDO_RETENTION Time war zu klein gewählt und die
Rollbackinformationen nicht mehr verfügbar.
19:58:11 llam@ALVT > alter system set undo_retention=3600;
System altered.
real: 60
19:59:05 llam@ALVT > delete from lc_vertraege where ver_id = 100037110;
1 row deleted.
real: 10
19:59:19 llam@ALVT > commit;
Commit complete.
real: 10
19:59:23 llam@ALVT > select
from
as of
where
*
lc_vertraege
timestamp to_timestamp ('10-MAR-03 19:58', 'dd-mon-yy hh24:mi' )
ver_id = 100037110;
VER_ID VER_MAN_ID VER_VERTRAGSNR
V KZ_HERKUNFT
PSEUDO VER_VERTRAGSNR_ALT
---------- ---------- -------------------- - ---------------- ---------- -------------------100037110
50049 306812889
FP
0
1 row selected.
real: 20
19:59:47 llam@ALVT >
Somit wurden Daten selektiert, die um 19:59h gelöscht wurden!
Seite 4/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
4
VIEW CONSTRAINTS
Mit Einführung von View Constraints, Primary Key, Foreign Key oder Check
Constraints erstellt werden. Hier ein Beispiel:
DROP TABLE lc$test;
CREATE TABLE lc$test( PAR_ID
NUMBER( 9 ),
PAR_ANR_ID NUMBER( 9 ),
PAR_NAME
VARCHAR2( 32 ) );
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
lc$test
lc$test
lc$test
lc$test
lc$test
lc$test
lc$test
lc$test
lc$test
lc$test
VALUES(100104248,
VALUES(100104249,
VALUES(100104250,
VALUES(100104251,
VALUES(100104252,
VALUES(100104253,
VALUES(100104254,
VALUES(100104255,
VALUES(100104256,
VALUES(100104234,
50244,
50244,
50244,
50245,
50244,
50245,
50244,
50244,
50245,
50244,
'InvestRent Tarif55 W' );
'Investrent T55' );
'InvestRent Tarif 55E' );
'InvestRent Tarif 57V' );
'Partner' );
'Person' );
'Investrent Tarif 57' );
'InvestRent K Tarif 57' );
'Person' );
'UFW-Kindergeld' );
CREATE OR REPLACE VIEW lc$test_v( PAR_ID, PAR_ANR_ID, PAR_NAME,
CONSTRAINT lc$test_pk PRIMARY KEY (PAR_ANR_ID) RELY DISABLE NOVALIDATE )AS
SELECT PAR_ID, PAR_ANR_ID, PAR_NAME FROM LC$TEST;
CREATE OR REPLACE VIEW lc$test_v( PAR_ID, PAR_ANR_ID UNIQUE RELY DISABLE
NOVALIDATE, PAR_NAME )AS
SELECT PAR_ID, PAR_ANR_ID, PAR_NAME FROM LC$TEST;
insert into lc$test_v values( 100104246, 50245, 'InvestRent Tarif 55A' );
5
MATERIALIZED VIEWS (MV)
In den früheren Oracle Versionen <8i nannte man diese Technik SNAPSHOT.
Ein SnapShot stellte sich dar als Tabelle, für die bei der Erstellung ein Zeitpunkt
angegeben wurde, zu dem der Inhalt –basierend auf dem Select Statement für
diesen SnapShot- erneuert wird
CREATE SNAPSHOT LOG ON emp;
-- einmaliges Refresh, Typ FAST
CREATE SNAPSHOT emp_sf
REFRESH FAST NEXT sysdate + 7 AS
SELECT * FROM scott.emp;
-- Refresh jeden Montag, nicht FAST
CREATE SNAPSHOT all_emps
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24
NEXT NEXT_DAY(TRUNC(SYSDATE, 'MONDAY') + 15/24 AS
SELECT * FROM fran.emp@dallas
UNION
SELECT * FROM marco.emp@balt;
Diese Elemente findet man heute für das Erstellen einer MV wieder.
Seite 5/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Es gibt unzählige Schalter bei der Erstellung einer MV, die an dieser Stelle nicht
alle besprochen werden. Grundsätzlich gilt es die Art des Refresh zu
differentzieren.
5.1 Materialized Views COMPLETE REFRESH
Wie der Name schon vermuten läßt, werden alle Daten in diesem View
erneuert, wie sie durch das Select Statement beschrieben sind. Besonders bei
großen Quelltabellen ist hier eine lange Laufzeit einzurechnen. Unter der
Betrachtung, daß sich in diesem VIEW allerdings nur sehr wenige Daten
geändert haben oder hinzu geschrieben wurden, ist diese Methode nicht
sonderlich effizient.
5.2 Materialized Views FAST REFRESH
Um nur die realen Änderungen in die MV aufzunehmen kann der Fast Refresh
anstatt eines Complete Refresh genutzt werden. Diese funktioniert allerdings
nur wenn:
¾ Für die Quelltabelle ein Primary Key existiert
¾ Ein MV Log erstellt wurde, indem die Änderungen von Oracle protokolliert
werden
¾ Wenn die MV aus einer Tabelle selektiert wird
5.3 Materialized Views FAST REFRESH ON COMMIT
Mit den zuvor beschriebenen Aktualisierungen eines MV muß immer der
Refresh Zeitpunkt angegeben werden. Durch die Angabe FAST REFRESH ON
COMMIT werden die Änderungen aus der Quelltabelle direkt übernommen in
die MV, wenn der COMMIT durchgeführt wurde. Auch diese Technik
funktioniert nur, wenn:
¾ Für die Quelltabelle ein Primary Key existiert
¾ Ein MV Log basierend auf dem PRIMARY KEY erstellt wurde
¾ Wenn die MV aus einer Tabelle selektiert wird
13:42:53 lambertz@ODB92 > CREATE MATERIALIZED VIEW LOG ON adr_name WITH ROWID INCLUDING NEW
VALUES;
13:42:53 lambertz@ODB92 >
13:42:54 lambertz@ODB92 > CREATE MATERIALIZED VIEW adr_name_mv
13:42:54
2
BUILD IMMEDIATE
13:42:54
3
REFRESH FAST ON COMMIT
13:42:54
4
AS SELECT *
13:42:54
5
FROM adr_name
13:42:54
6
WHERE name like 'Meier%' ;
FROM adr_name
*
ERROR at line 5:
Seite 6/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
ORA-23415: materialized view log for "LAMBERTZ"."ADR_NAME" does not record the primary key
Hier wurde das MVLog mit der Aufgabe erstellt, alle Änderungen basierend auf
der ROWID zu protokollieren. Das ist für einen MV von Typ FAST REFRESH
ON COMMIT nicht zulässig.
13:42:54 lambertz@ODB92 > DROP MATERIALIZED VIEW LOG ON adr_name;
13:43:42 lambertz@ODB92 > CREATE MATERIALIZED VIEW LOG ON adr_name WITH PRIMARY KEY
INCLUDING NEW VA
LUES;
13:43:42 lambertz@ODB92 > CREATE MATERIALIZED VIEW adr_name_mv
13:43:42
2
BUILD IMMEDIATE
13:43:42
3
REFRESH FAST ON COMMIT
13:43:42
4
AS SELECT *
13:43:42
5
FROM adr_name
13:43:42
6
WHERE name like 'Meier%' ;
13:44:53 lambertz@ODB92 >
1* select count(*) from
13:55:44 lambertz@ODB92 >
88
13:55:45 lambertz@ODB92 >
1* select count(*) from
13:55:54 lambertz@ODB92 >
13:56:45 lambertz@ODB92 >
13:56:51 lambertz@ODB92 >
89
13:57:07 lambertz@ODB92 >
13:58:26 lambertz@ODB92 >
13:58:30 lambertz@ODB92 >
89
13:58:40 lambertz@ODB92 >
13:59:36 lambertz@ODB92 >
14:00:38 lambertz@ODB92 >
14:00:41 lambertz@ODB92 >
88
14:00:47 lambertz@ODB92 >
adr_name_mv
/
l
adr_name_mv
insert into adr_name values( 280100, 'Meiermann' );
commit;
select count(*) from adr_name_mv;
insert into adr_name values( 280101, 'Müller' );
commit;
select count(*) from adr_name_mv;
delete from adr_name where id = 280101;
update adr_name set name = 'Müller' where id = 280100;
commit;
select count(*) from adr_name_mv;
Somit ist erkennbar, daß die MV adr_name_mv aktualisiert wurde, indem die
Änderung in der Tabelle adr_name festgeschrieben wurden.
5.3.1 ENABLE QUERY REWRITE
Diese Parametersequence wird bei der Anlage der MV angegeben, funktioniert
allerdings nur, wenn die Datenbank QUERY_REWRITE=TRUE gesetzt ist. Es wird ein
Select auf die Tabelle ADR_NAME abgesetzt und der zugehörige Explain Plan
betrachtet:
13:49:20 lambertz@ODB92 > @xtree
Enter name of file with contains SQL: 0
Generating Execution plan for
1* select * from adr_name where Name like 'Meier%';
SELECT STATEMENT
OPIMIZER=CHOOSE cost= 98
Seite 7/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Execution Plan
Cost
--------------------------------------------------------------------------- --------1.1 TABLE ACCESS FULL LAMBERTZ.ADR_NAME
98
TABLE ACCESS
FULL
ADR_NAME
An diesem Ausführungsplan ist nichts außergewöhnliches. Nun wird eine MV
mit der Parametersequence ENABLE QUERY REWRITE erstellt und wieder ein
Ausführungsplan erstellt:
13:44:02 lambertz@ODB92 > DROP MATERIALIZED VIEW LOG ON adr_name;
13:44:35 lambertz@ODB92 > CREATE MATERIALIZED VIEW LOG ON adr_name WITH PRIMARY KEY
INCLUDING NEW VA
LUES;
13:44:35 lambertz@ODB92 > DROP MATERIALIZED VIEW adr_name_mv;
13:44:36 lambertz@ODB92 >
13:44:36 lambertz@ODB92 > CREATE MATERIALIZED VIEW adr_name_mv
13:44:36
2
BUILD IMMEDIATE
13:44:36
3
REFRESH FAST ON COMMIT
13:44:36
4
ENABLE QUERY REWRITE
13:44:36
5
AS SELECT *
13:44:36
6
FROM adr_name
13:44:36
7
WHERE name like 'Meier%' ;
13:44:36 lambertz@ODB92 >
13:44:37 lambertz@ODB92 > @xtree
Enter name of file with contains SQL: 0
Generating Execution plan for
1* select * from adr_name where Name like 'Meier%';
SELECT STATEMENT
OPIMIZER=CHOOSE cost= 2
Execution Plan
Cost
--------------------------------------------------------------------------- --------1.1 TABLE ACCESS FULL LAMBERTZ.ADR_NAME_MV
2
TABLE ACCESS
FULL
ADR_NAME_MV
Nun zeigt der Ausführungsplan, daß der CBO für die Ausführung des
Statements die MV „gesehen“ und genutzt hat!!
5.4 Materialized Views FAST REFRESH ON COMMIT für Joins
Bisherige Beispiele zeigen nur die MV, welche auf einer Quelltabelle basieren.
Nun wird versucht, diese Einschränkung zu umgehen – mit einer View. Sie wird
zusätzlich mit einem erforderlichen Primary Key versehen wird:
Seite 8/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
19:06:14 lambertz@ODB92 > CREATE VIEW v_adr_name(name, vorname, strasse,
19:06:14
2
CONSTRAINT v_adr_name_pk
19:06:14
3
PRIMARY KEY( name, vorname, strasse) RELY DISABLE NOVALIDATE )AS
19:06:14
4 SELECT n.name, a.VORNAME, a.STRASSE
19:06:14
5
FROM adr_adressen a, adr_name n
19:06:14
6
WHERE a.name_id = n.id
19:06:14
7
AND n.name LIKE 'Meier%'
19:06:14
8
AND n.name IS NOT NULL
19:06:14
9
AND a.VORNAME IS NOT NULL
19:06:14 10
AND a.STRASSE IS NOT NULL;
View created.
Elapsed: 00:00:00.00
19:06:48 lambertz@ODB92 > CREATE MATERIALIZED VIEW LOG ON v_adr_name WITH PRIMARY KEY
INCLUDING NEW
VALUES;
CREATE MATERIALIZED VIEW LOG ON v_adr_name WITH PRIMARY KEY INCLUDING NEW VALUES
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
19:06:49 lambertz@ODB92 >
Angeblich existiert die View „v_adr_name“ nicht, was falsch ist, wie folgende Kontrolle
zeigt:
19:06:59 lambertz@ODB92 > desc v_adr_name
Name
----------------------------------------------------------------NAME
VORNAME
STRASSE
19:07:01 lambertz@ODB92 >
Aber es gibt noch eine Einschränkung für MV. Eine WHERE Bedingung kann
keine nicht konstanten Elemente enthalten wie z.B. SYSDATE. Also eine MV zu
erstellen, welche immer nur Werte der letzen 6 Monate enthält, ist nicht möglich
6
TABLE PARTITIONING
Tables in der Form:
CREATE TABLE adr_part_table
(
anrede
vorname
name
strasse
plz
ort
telefon
vorwahl
rufnummer
VARCHAR2(10),
VARCHAR2(25),
VARCHAR2(90),
VARCHAR2(60),
NUMBER(5),
VARCHAR2(60),
VARCHAR2(15),
NUMBER(10),
VARCHAR2(30)
Seite 9/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
)
PARTITION BY RANGE (name)
(
PARTITION eins_bis_f VALUES LESS THAN ('G')
TABLESPACE mass_dat,
PARTITION g_bis_k VALUES LESS THAN ('L')
TABLESPACE mass_dat,
PARTITION l_bis_r VALUES LESS THAN ('S')
TABLESPACE mass_dat,
PARTITION s_bis_max VALUES LESS THAN (MAXVALUE)
TABLESPACE mass_dat
)
/
sind schon seit Oracle 8 bekannt. Neu am Partitionieren von Tabellen unter 9i
ist, wie einzelne Partitionen gebildet werden.
Durch die Angabe von Partitionen bei der Erstellung einer Tabelle, werden von
Oracle so viele Tabellen erzeugt, wie Partitionen angegeben werden. Auch die
Verwaltung erfolgt durch Oracle.
Wenn eine Abfrage abgesetzt wird wie folgt:
SELECT * FROM adr_part_table WHERE name LIKE ‘Meier‘;
bildet Oracle die Ergebnismenge ausschließlich aus der Partition:
PARTITION l_bis_r VALUES LESS THAN ('S').
Der Explain Plan zeigt das deutlich:
Das hat natürlich gerade Tabellen mit großem Datenvolumen enorme Vorteile.
Seite 10/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Aber – wenn ein Update auf den Primary Key erfolgt, der zur Folge hat, daß der
Datensatz in eine andere Partition übertragen werden müßte, wird für den
„neuen“ Eintrag in der neuen Partion ein Eintrag (so eine Art Zeigert) erzeugt,
der auf den echten Datendsatz in der ursprünglichen Partition verweist. Der
Datensatz selbst verbleibt in der ursprünglichen Tabelle.
Somit ist ekennbar, daß es zu einem Organisations-chaos für Oracle führt,
wenn zu häufig der Primary Key geändert wird.
6.1 Hash Partitioning
Mit der HASH Partitionierung werden nur die Anzahl der Partitionen angegeben
und Oracle verwaltet die Füllstände der einzelnen Partition Tabelle
selbstständig.
CREATE TABLE hash_products
( product_id
NUMBER(6)
, product_name
VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id
NUMBER(2)
, weight_class
NUMBER(1)
, warranty_period
INTERVAL YEAR TO MONTH
, supplier_id
NUMBER(6)
, product_status
VARCHAR2(20)
, list_price
NUMBER(8,2)
, min_price
NUMBER(8,2)
, catalog_url
VARCHAR2(50)
, CONSTRAINT
product_status_lov
CHECK (product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')
) )
PARTITION BY HASH (product_id)
PARTITIONS 5
STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);
6.2 List Partitioning
War es unter Oracle 8 nur möglich Bereiche einzelner Partitionen zu
bestimmen, wie folgendes Beispiel zur Anlage einer solchen Tabelle zeigt,
CREATE TABLE range_sales
( prod_id
NUMBER(6)
, cust_id
NUMBER
, time_id
DATE
, channel_id
CHAR(1)
, promo_id
NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold
NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS
PARTITION SALES_Q2_1998 VALUES LESS
PARTITION SALES_Q3_1998 VALUES LESS
PARTITION SALES_Q4_1998 VALUES LESS
PARTITION SALES_Q1_1999 VALUES LESS
PARTITION SALES_Q2_1999 VALUES LESS
PARTITION SALES_Q3_1999 VALUES LESS
PARTITION SALES_Q4_1999 VALUES LESS
PARTITION SALES_Q1_2000 VALUES LESS
PARTITION SALES_Q2_2000 VALUES LESS
THAN
THAN
THAN
THAN
THAN
THAN
THAN
THAN
THAN
THAN
(TO_DATE('01-APR-1998','DD-MON-YYYY')),
(TO_DATE('01-JUL-1998','DD-MON-YYYY')),
(TO_DATE('01-OCT-1998','DD-MON-YYYY')),
(TO_DATE('01-JAN-1999','DD-MON-YYYY')),
(TO_DATE('01-APR-1999','DD-MON-YYYY')),
(TO_DATE('01-JUL-1999','DD-MON-YYYY')),
(TO_DATE('01-OCT-1999','DD-MON-YYYY')),
(TO_DATE('01-JAN-2000','DD-MON-YYYY')),
(TO_DATE('01-APR-2000','DD-MON-YYYY')),
(TO_DATE('01-JUL-2000','DD-MON-YYYY')),
Seite 11/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE));
können unter Oracle 9 auch Fixwerte angegeben werden:
CREATE TABLE list_customers
( customer_id
NUMBER(6)
, cust_first_name
VARCHAR2(20)
, cust_last_name
VARCHAR2(20)
, cust_address
CUST_ADDRESS_TYP
, nls_territory
VARCHAR2(30)
, cust_email
VARCHAR2(30))
PARTITION BY LIST (nls_territory) (
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES (DEFAULT));
Eine Mischung aus RANGE und LIST ist möglich!
7
INDEX ORGANIZED TABLE (IOT)
IOT sind Tabellen, die nach dem H*Tree Verfahren organisiert sind. Vereinfacht
also ein Index, in dem alle Attribute einer Tabelle enthalten sind – außer der
ROWID, die gibt es in einer IOT nicht. Eine IOT kann nur mit einem Primary
Key erstellt werden. Hier ein Beispiel:
CREATE TABLE adr_iot
(
anrede
VARCHAR2(10),
vorname
VARCHAR2(25),
name
VARCHAR2(90),
strasse
VARCHAR2(60),
plz
NUMBER(5),
ort
VARCHAR2(60),
telefon
VARCHAR2(15),
vorwahl
NUMBER(10),
rufnummer
VARCHAR2(30),
CONSTRAINT "ADR_IOT_PK" PRIMARY KEY( plz, name, rufnummer )
)
ORGANIZATION INDEX
OVERFLOW TABLESPACE mass_dat
TABLESPACE mass_dat;
Eine solche Tabelle ist nicht zu empfehlen, wenn regelmäßig große
Datenbestände aus dieser Tabelle entfernt werden, weil diese Daten in dem
Binärbaum erhalten bleiben und nur als gelöscht markiert werden. Das hat zur
Folge, daß eine solche Tabelle an Größe immer nur zunimmt und in
regelmäßigen Abständen reorganisiert werden muß.
Folgendes Statement verursacht einen Fehler, weil kein PK angegeben wurde:
Seite 12/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
15:15:39 llam@WINA > CREATE TABLE lc$fonds
15:15:40
2 AS SELECT * FROM fonds
15:15:40
3 ORGANIZATION INDEX
15:15:40
4 MAPPING TABLE
15:15:40
5 TABLESPACE aspflv_d01;
ORGANIZATION INDEX
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
real: 10
15:15:40 llam@WINA >
Der Ausführungsplan enthält einen FULL INDEX SCAN, was sich nur ändert,
wenn über den Primary Key eingeschränkt wird:
Seite 13/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Wenn also keine Einschränkung für die Tabelle über den Primary Key erfolgt,
ist der Nutzen einer IOT dahin. Mit der Version 8i wurde die Möglichkeit
geschaffen, einen zusätzlichen Index auf eine IO Table anzulegen; jedoch nur
als B*Tree Index. Der benötigt allerdings eine RowID, welche in einer IOT nicht
vorhanden sind. Oracle hat dazu „logische ROWID‘s“ eingeführt. Nun zeigt sich
der Explain:
Seite 14/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Und ob diese Verwurstung wirklich performanter ist, wage ich allerdings zu
bezweifeln.
7.1 Bitmap secondary index on
Mit Oracle 9i ist das Erstellen eines Bitmap Index auf eine IOT auch möglich.
Damit diese Technik funktioniert, muß für den Bitmap Index eine „mapping
table“ vorliegen, welche die logischen RowID’s aus der IOT enthält. Die
mapping table kann direkt beim Erstellen des Index erzeugt werden.
CREATE TABLE adr_iot
(
anrede
VARCHAR2(10),
vorname
VARCHAR2(25),
name
VARCHAR2(90),
strasse
VARCHAR2(60),
plz
NUMBER(5),
ort
VARCHAR2(60),
telefon
VARCHAR2(15),
vorwahl
NUMBER(10),
rufnummer
VARCHAR2(30),
CONSTRAINT "ADR_IOT_PK" PRIMARY KEY( plz, name, rufnummer )
)
ORGANIZATION INDEX
OVERFLOW TABLESPACE mass_dat
MAPPING TABLE TABLESPACE mass_dat;
CREATE BITMAP INDEX adr_iot_bidx ON adr_iot( plz );
Seite 15/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Und so sieht ein Ausführungsplan dazu aus:
7.2 Hash Partitioning on
Auch für eine IOT können Partitionen beschrieben werden, wenn die Tabelle
erzeugt wird. Neu ist, daß nun entsprechend dem Partitionieren sequentieller
Tabellen, „hash partitioning“ für IOT‘s genutzt werden kann.
8
EXTERNAL TABLES (ETL)
Externe Tabellen sind „flat files“, welche als Metadaten im Data Dictionary einer
Oracle Datenbank verwaltet werden. Die Daten in dem „flat file“ entsprechen
dem Format, wie sie auch für den Einsatz mit dem SQL*Loader bekannt sind.
Jedoch ist das Anlegen eines Oracle Directory erforderlich. Beispiel:
CREATE DIRECTORY ETL_Files AS 'oracle/export/file';
CREATE TABLE LC_ETL( Tex VARCHA2(33), Datum DATE )
ORGANISATION EXTERNAL( TYPE oracle_loader
DEFAULT DIRECTORY 'oracle/export/file'
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE 'lc_etl.bad'
FIELDS TERMINATED BY ";"
LDRTRIM( Tex CHAR(255),
Datum CHAR(255) DATE_FORMAT DATE
MASK 'dd.mm.yyyy' ))
LOCATION( 'lc_etl.tbl' ))
REJECT LIMIT UNLIMITED;
Seite 16/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Der User, welcher eine ETL anlegen möchte, muß das Grant „create dictonary“
verfügen - GRANT CREATE ANY DIRECTORY TO UserName;
Unstimmigkeiten in der Beschreibung der LoaderDaten werden nicht mit dem
CREATE der Tabelle erkannt, sondern erst, wenn die Tabelle angesprochen
wird:
SQL> select count(*) from lc_etl;
select count(*) from lc_etl
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "enclosed,
exit,
(, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim, reject"
KUP-01008: the bad identifier was: /
KUP-01007: at line 4 column 52
KUP-00031: concat
KUP-00031: concat
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
SQL>
Werden zum Zeitpunkt des eigentlichen Ladevorgangs Daten aus der
LoaderDatei abgelehnt, ist dies nur aus dem LogFile ersichtlich. Somit sollte
ETL immer zu beginn mit einem COUNT überprüft werden.
Das Erstellen einer fehlerfreien ETL kann aussehen:
CREATE TABLE LC_ETL( eingabedatum
DATE,
vertragsnr
VARCHAR2(20), /* NOT NULL Angaben
sind nicht möglich */
rolle
VARCHAR2(2),
name
VARCHAR2(70),
dok_art
VARCHAR2(2),
dok_nr
VARCHAR2(20),
ausstell_datum
DATE,
ausstell_behoerde
VARCHAR2(32),
geburtsort
VARCHAR2(32),
wirtschaftszweig1
VARCHAR2(3),
wirtschaftszweig2
VARCHAR2(3),
gv_nationalitaet
VARCHAR2(32),
gv_geburtsdatum
DATE,
gv_geschlecht
VARCHAR2(1),
gv_fiskalnummer
VARCHAR2(25),
gv_adresse
VARCHAR2(70),
gv_plz
VARCHAR2(7),
gv_wohnort
VARCHAR2(32) )
ORGANIZATION EXTERNAL( TYPE oracle_loader
DEFAULT DIRECTORY ETL_files
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
BADFILE 'etl.bad'
Seite 17/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
LOGFILE 'etl.log'
FIELDS TERMINATED BY ";"
LDRTRIM( eingabedatum DATE "DD.MM.YYYY",
vertragsnr
CHAR(20),
rolle
CHAR(2),
name
CHAR(32),
dok_art
CHAR(2),
dok_nr
CHAR(20),
ausstell_datum
DATE "DD.MM.YYYY",
ausstell_behoerde
CHAR(32),
geburtsort
CHAR(32),
wirtschaftszweig1
CHAR(3),
wirtschaftszweig2
CHAR(3),
gv_nationalitaet
CHAR(32),
gv_geburtsdatum
DATE "DD.MM.YYYY",
gv_geschlecht
CHAR(1),
gv_fiskalnummer
CHAR(25),
gv_adresse
CHAR(70),
gv_plz
CHAR(7),
gv_wohnort
CHAR(32)
TERMINATED BY WHITESPACE )
)
LOCATION( 'etl.tbl' ))
REJECT LIMIT UNLIMITED;
Fehlerfrei ist dieses CREATE Statement schon, aber..
SQL> select count(*) from lc_etl;
select count(*) from lc_etl
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file LC_ETL.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
SQL>
..die LOG Datei kann nicht erstellt werden, weil in der Datenbank der Parameter
utl_file_dir nicht gesetzt ist, oder auf kein gültiges Verzeichnis verweiset
(fehlende Schreibrechte, usw.)
8.1 ETL-Table Function
Eine Table Function kann nicht nur in Verbindung mit einer „realen Tabelle“,
sondern auch mit einer External Table eingesetzt werden. Table Funktions
werden gerne eingesetzt, um selektierten Daten direkt zu transformieren und
über die Tabel Function auszugeben. Beispiel:
DROP TYPE t_sqltext
/
CREATE TYPE t_sqltext AS OBJECT( str VARCHAR2( 4000 ),
address VARCHAR2( 16 ) )
/
DROP TYPE t_sqltext_set
Seite 18/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
/
CREATE TYPE t_sqltext_set AS TABLE OF t_sqltext
/
CREATE OR REPLACE PACKAGE sqltext IS
TYPE t_refcur_sqlt IS REF CURSOR RETURN v$sqltext%ROWTYPE;
FUNCTION str( pi_sql_text IN v$sqltext.sql_text%TYPE ) RETURN t_sqltext_set
PIPELINED;
FUNCTION str ( pi_sql_text IN sqltext.t_refcur_sqlt ) RETURN t_sqltext_set
PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY sqltext IS
------------------- Funktion 1
----------------FUNCTION str ( pi_sql_text IN sqltext.t_refcur_sqlt )
RETURN t_sqltext_set PIPELINED IS
v_sql_text VARCHAR2( 4000 );
v_out_sql t_sqltext := t_sqltext( NULL, NULL );
v_in_rec pi_sql_text%ROWTYPE;
CURSOR cur2( ci_address IN v$sqltext.address%TYPE,
ci_hash_value IN v$sqltext.hash_value%TYPE ) IS
SELECT a.sql_text
FROM v$sqltext a
WHERE a.address = ci_address
AND a.hash_value = ci_hash_value
ORDER BY piece;
BEGIN
LOOP
FETCH pi_sql_text INTO v_in_rec;
EXIT WHEN pi_sql_text%NOTFOUND;
v_SQL_text := NULL;
FOR cur2_rec IN cur2( v_in_rec.address, v_in_rec.hash_value )LOOP
v_SQL_text := v_SQL_text || cur2_rec.SQL_text;
END LOOP;
v_out_sql.address := v_in_rec.address;
v_out_sql.str := v_SQL_text;
PIPE ROW( v_out_sql );
END LOOP;
CLOSE pi_sql_text;
RETURN;
END;
------------------- Funktion 2
----------------FUNCTION str( pi_sql_text IN v$sqltext.sql_text%TYPE )
RETURN t_sqltext_set PIPELINED IS
v_sql_text VARCHAR2( 4000 );
v_out_sql t_sqltext := t_sqltext( NULL, NULL );
CURSOR cur1 IS
SELECT DISTINCT a.address, a.hash_value
FROM v$sqltext a
WHERE a.sql_text LIKE pi_sql_text
AND UPPER(a.sql_text) NOT LIKE '%V$SQL_TEXT%'
AND UPPER(a.sql_text) NOT LIKE '%V$SQLTEXT%';
CURSOR cur2( ci_address IN v$sqltext.address%TYPE,
Seite 19/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
ci_hash_value IN v$sqltext.hash_value%TYPE ) IS
SELECT a.sql_text
FROM v$sqltext a
WHERE a.address = ci_address
AND a.hash_value = ci_hash_value
ORDER BY piece;
BEGIN
FOR cur1_rec IN cur1 LOOP
v_SQL_text := NULL;
FOR cur2_rec IN cur2( cur1_rec.address, cur1_rec.hash_value )LOOP
v_SQL_text := v_SQL_text || cur2_rec.SQL_text;
END LOOP;
v_out_sql.address := cur1_rec.address;
v_out_sql.str := v_SQL_text;
PIPE ROW( v_out_sql );
END LOOP;
RETURN;
END;
END;
/
SELECT * FROM TABLE( sqltext.str('% dual%')); /* Kann zu Error 22905 führen*/
SELECT str FROM TABLE( sqltext.str('% dual%')); /* nicht, wenn qualifiziert */
SELECT sqltext.str('% dual%') FROM dual;
SELECT sql_text, address
FROM TABLE( sqltext.str( CURSOR( SELECT sql_text,
address
FROM v$sqltext
WHERE sql_text
LIKE '%FROM BEARBEITUNGSNACHWEISE%')));
SELECT *
FROM TABLE( sqltext.str( CURSOR( SELECT sql_text,
address
FROM v$sqltext
WHERE sql_text
LIKE '%FROM BEARBEITUNGSNACHWEISE%')));
8.2 ETL-Multi Table Insert
INSERT ALL
WHEN product_id IN ( SELECT product_id
FROM promotional_items)
INTO promotion_sales VALUES( product_id, list_price)
WHEN order_mode = ’online’
INTO web_orders VALUES( product_id, order_total)
SELECT product_id, list_price, order_total, order_mode
FROM orders_ext
WHERE order_total > 1;
8.3 ETL-Multi Table Merge
MERGE INTO customer c
USING customer_merge m
ON (c.customer_id = m.src_customer_id)
Seite 20/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
WHEN MATCHED THEN
UPDATE SET c.cust_address = m.cust_address
WHEN NOT MATCHED THEN
INSERT ( Customer_id, cust_first_name,… )
VALUES ( src_customer_id, src_first_name,… );
9
CHANGE DATA CAPTURE
BEGIN
dbms_logmnr_cdc_publish.drop_change_table
( owner => 'hr',
change_table_name => 'departments_changes',
force_flag => 'y');
END;
BEGIN
dbms_logmnr_cdc_publish.create_change_table(
owner => 'hr',
change_table_name => 'departments_changes',
change_set_name => 'SYNC_SET',
source_schema => 'hr',
source_table => 'departments',
column_type_list => 'DEPARTMENT_ID NUMBER(4), DEPARTMENT_NAME VARCHAR2(30),
MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4), ANREDE VARCHAR2(10)',
capture_values => 'both',
rs_id => 'y',
row_id => 'y',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'y',
target_colmap => 'y',
options_string => NULL );
END;
SQL> desc hr.departments
Name
----------------------------------------DEPARTMENT_ID
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
Null?
-------NOT NULL
NOT NULL
Type
---------------------------NUMBER(4)
VARCHAR2(30)
NUMBER(6)
NUMBER(4)
SQL> insert into hr.departments values( 999, 'LC$', 200, 1700 );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.departments_changes;
OP
CSCN$ COMMIT_TI
RSID$ ROW_ID$
-- ---------- --------- ---------- -----------------SOURCE_COLMAP$
-------------------------------------------------------------------------------TARGET_COLMAP$
Seite 21/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
-------------------------------------------------------------------------------DEPARTMENT_ID DEPARTMENT_NAME
MANAGER_ID LOCATION_ID ANREDE
------------- ------------------------------ ---------- ----------- ---------I 2.8147E+14 01-JAN-00
1 AAAG8IAAEAAAAA2AAA
FE
FE
999 LC$
200
1700
SQL> delete from hr.departments where department_id =999;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.departments_changes;
OP
CSCN$ COMMIT_TI
RSID$ ROW_ID$
-- ---------- --------- ---------- -----------------SOURCE_COLMAP$
-------------------------------------------------------------------------------TARGET_COLMAP$
-------------------------------------------------------------------------------DEPARTMENT_ID DEPARTMENT_NAME
MANAGER_ID LOCATION_ID ANREDE
------------- ------------------------------ ---------- ----------- ---------I 2.8147E+14 01-JAN-00
1 AAAG8IAAEAAAAA2AAA
FE
FE
999 LC$
200
1700
OP
CSCN$ COMMIT_TI
RSID$ ROW_ID$
-- ---------- --------- ---------- -----------------SOURCE_COLMAP$
-------------------------------------------------------------------------------TARGET_COLMAP$
-------------------------------------------------------------------------------DEPARTMENT_ID DEPARTMENT_NAME
MANAGER_ID LOCATION_ID ANREDE
------------- ------------------------------ ---------- ----------- ---------D 2.8147E+14 01-JAN-00
2 AAAG8IAAEAAAAA2AAA
00
00
999 LC$
200
1700
SQL>
Damit ein User solche CDC Tabellen erzeugen kann, muß JAVA in der Datenbank
arbeiten.
10 EXPLAIN PLAN ZU ABGEARBEITETEN STATEMENTS
Bis war es nur möglich, einen Ausfürungsplan für eine Statement vor der
eigentlichen Ausführung zu erstellen. Ob dieser Plan auch zur Ausführung
genutzt wurde, ist nicht sichergestellt.
Seite 22/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Nun wird eine neue View von Oracle bereitgestellt mit dem Namen
V$SQL_PLAN. Mit Angabe der SQL_Adresse kann der Ausführungsplan
eingesehen werden, der tatsächlich zur Ausführung des Statements eingesetzt
wurde. Die SQL Adresse (ADDRESS) kann aus der View V$SQLAREA ermittelt
werden unter Angabe des Statements (Attribut SQL_TEXT).
SELECT address
FROM v$sqlarea
WHERE sql_text = 'Select * from dual';
SELECT *
FROM v$sql_plan
WHERE address = ( SELECT address
FROM v$sqlarea
WHERE sql_text ='Select * from dual' );
SELECT SUBSTR( lpad(' ',2*( LEVEL -1 )) || level ||'.'|| DECODE( id, 0, 0,
position) || ' ' || operation || ' ' || options || ' ' ||
DECODE( object_name, NULL, NULL, object_owner || '.' || object_name) ||
DECODE( id, 0, 'Mode=' || SUBSTR( optimizer, 1, 20 ) || ' Cost (max) =
' || position , NULL ), 1, 90) "Execution Plan",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( cost ), 1, 10 ) ) "Cost",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( IO_cost ), 1, 10 ) ) "IO
Cost",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( CPU_cost ), 1, 10 ) ) "CPU
Cost",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( partition_id ), 1, 10 ) )
"Part. ID",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( CARDINALITY ), 1, 10 )) "AVG
Rows",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( search_columns ), 1, 10 ))
"search_columns",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( partition_start ), 1, 10 ) )
"Part. Start",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( partition_stop ), 1, 10 ) )
"Part. Stop",
DECODE( cost, NULL, NULL, SUBSTR( TO_CHAR( bytes), 1, 10 ) ) "Bytes"
FROM v$sql_plan
WHERE address = ( select address
from v$sqlarea
where sql_text ='Select * from dual' )
CONNECT BY PRIOR id=parent_id
START WITH id=1
11 RESUMABLE SPACE MANAGEMET
Jedem ist das sicherlich schon einmal vorgekommen, dass unerwartet der
Space bzw. Plattenplatz nicht mehr ausreicht, ein RollBack erfolgt und eine
Fehlermeldung ausgegeben wird.
Besonders tragisch:
•
wenn z.B. eine Abfrage schon seit mehreren Stunden das Result Set
bildet und plötzlich der TempSpace nicht ausreicht.
•
wenn nach meheren Stunden eines ziemlich aufwendigen UPDATE die
Rollback segmente nicht erweitert werden können
Seite 23/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
Um solche Speicher Probleme in den Griff zu bekommen, hat Oracle ein neues
Package eingeführt mit Namen „DBMS_RESUMABLE“. Mit hilfe dieses
Package kann innerhalb eines definierten Zeitraum die durch den Space
Management ausgelösten Unterbrechung der Verarbeitung wieder
aufgenommen werden.
Wie stellt sich das im konkreten dar?
EXEC DBMS_RESUMABLE.SET_TIMEOUT( 14400 ); -- 4Stunden
Ein Update führt zu einem Fehler ORA-01650. Nun erfolgt kein Rollback,
sondern die Transaktion verweilt in dem Zustand bis
•
die TimeOut Grenze erreicht wurde. Es erfolgt ein Rollback
•
der Fehler korrigiert wurde; vergrößern der TableSpace
•
der Resumable abgebrochen wird mit EXEC
DBMS_RESUMABLE.ABORT;
Feine Sache..
12 PL/SQL Erweiterungen
12.1 SELECT FOR UPDATE WAIT
Bisher gab es nur die Vartianten:
¾
SELECT * FROM Tabelle_name FOR UPDATE
¾
SELECT * FROM Tabelle_name FOR UPDATE NO WAIT
Für den ersten Fall kann das Select Statment nicht ausgeführt werden, wenn
die mit der FOR UPDATE Klausel angeforderten Satzsperren nicht ausgeführt
werden können. Es entsteht ein LockWait.
Im zweiten Fall wird das SELECT Stament sofort beendet, ohne ausgeführt
worden zu sein, wenn die Satzsperren nicht aufgebaut werden können.
Die Neuerung besteht nun in der Angabe der WAIT Dauer in Sekunden. Ist
innerhalb dieser Zeitspanne das Sperren der angeforderten Sätze nicht
möglich, erfolgt der Abbruch des Statements; ohne das dieses abgearbeitet
wurde.
¾
SELECT * FROM Tabelle_name FOR UPDATE WAIT 3
-- 3 Sekunden Wartezeit
12.2 Verbesserung des Bulk Bind Verfahrens
Mit der 8i Version wurde die „massenorientierte Denke“ bei Oracle für
INSERT, UPDATE oder DELETE Statements eingeführt. Diese Technik
Seite 24/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
basiert auf „Array‘s“, also einer Zusammenfassung von Daten, die dann als
Paket in die Datenbank eingestellt und verarbeitet werden.
Tritt in der Schleife des folgenden Beispiel ein Fehler bei der Verabeitung auf,
wird direkt ein Exception ausgelöst; vielleicht schon nach dem ersten
DELETE.
DECLARE
TYPE t_ver_id IS TABLE OF vertraege.ver_id%TYPE;
v_get_ver_id t_ver_id := t_ver_id(1);
BEGIN
V_get_ver_id.EXTEND(3);
FORALL v_count IN 1..3
DELETE FROM vertraege
WHERE ver_id = v_get_ver_id(v_count);
END;
Anders in dem weiteren Beispiel. Hier werden erst alle Schleifendurchläufe
abgeschlossen. Im Anschluß wird das Exception ausgelöst:
DECLARE
TYPE t_ver_id IS TABLE OF vertraege.ver_id%TYPE;
v_get_ver_id t_ver_id := t_ver_id(1);
BEGIN
V_get_ver_id.EXTEND(3);
FORALL v_count IN 1..3
SAVE EXCEPTIONS
-- das ist neu
DELETE FROM vertraege
WHERE ver_id = v_get_ver_id(v_count);
EXCEPTION
WHEN Others THEN
FOR v_count_err IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Record Number: ' || TO_CHAR( v_count_err ));
DBMS_OUTPUT.PUT_LINE('ErrorCode: ' ||
TO_CHAR(SQL%BULK_EXCEPTIONS(v_count_err).error_code ));
DBMS_OUTPUT.PUT_LINE('ErrorMessage: ' ||
TO_CHAR(SQL%BULK_EXCEPTIONS(v_count_err).error_Index ));
END LOOP;
END;
12.3 CASE Konstruckte
Eigentlich ist es längst überfällig, daß Oracle in der PL/SQL CASE
Expressions zuläßt. Nun gibt es in 9i direkt zwei Varianten das CASE. Einmal
die klassige, wie sie aus 3GL Programmiersprachen bekannt ist:
DECLARE
v_case_wert NUMBER(1) :=1;
BEGIN
CASE v_case_wert
WHEN .1 THEN
EXIT 1;
WHEN .2 THEN
Seite 25/ 26
Oracle 9i – New feature
________________________________________________________________________________________________________________
EXIT 2;
ELSE
EXIT 3;
END CASE;
END;
Aber auch:
DECLARE
v_case_wert NUMBER(1) :=1;
BEGIN
CASE
WHEN v_case_wert=1 THEN
EXIT 1;
WHEN v_case_wert=2 THEN
EXIT 2;
ELSE
EXIT 3;
END CASE;
END;
Die zweite Variante ist, eine Wertzuweisung direkt per CASE gesteuert:
DECLARE
v_case_wert NUMBER(1) :=1;
v_ret NUMBER(2);
BEGIN
v_ret := CASE v_case_wert
WHEN .1 THEN 10
WHEN .2 THEN 20
ELSE 30
END;
END;
Seite 26/ 26