Konvertierungsfunktionen, SLEEP und LOBs – was gibt es Neues?

Default-Werte bei Konvertierungsfehlern

Jeder kennt die Fehlermeldung beim Konvertieren von Zeichen in Zahlen mit TO_NUMBER, sollte die Zeichenkette ein nicht-numerisches Zeichen enthalten (INVALID_NUMBER). Innerhalb von PL/SQL, z. B. einem Trigger, ist das noch recht einfach zu lösen über einen Exception-Handler. Bei einem Select wird es schon schwieriger, weil man erst einmal die störende(n) Zeile(n) finden muss. So mancher bastelte sich da seine eigene „my_to_number“- oder „my_is_numeric“-Funktion

Seit Version 12.2 ist das nicht mehr nötig. Die gängigen Konvertierungsfunktionen (einschließlich CAST) wurden erweitert um die DEFAULT <wert> ON CONVERSION Klausel.  Der dabei anzugebende DEFAULT-Wert muss vom passenden Datentyp sein; NULL ist generell erlaubt.

Beispiele:

SELECT TO_NUMBER('19a' DEFAULT -1 ON CONVERSION ERROR) keine_zahl,
       TO_NUMBER('19' DEFAULT -1 ON CONVERSION ERROR) zahl,
       TO_NUMBER('1234,23' DEFAULT -1 ON CONVERSION ERROR, '0.99') falsches_format,
       TO_DATE('31.4.2020' DEFAULT '01.01.1900' ON CONVERSION ERROR) ersatz_datum,
       TO_DATE('irgendwas' DEFAULT NULL ON CONVERSION ERROR) ersatz_null
FROM  dual;

KEINE_ZAHL       ZAHL FALSCHES_FORMAT ERSATZ_DAT ERSATZ_NULL                
---------- ---------- --------------- ---------- ------------
        -1         19              -1 01.01.1900                    

Zusätzlich wurde eine neue Funktion eingeführt, mit deren Hilfe überprüft werden kann, ob eine Konvertierung möglich ist: VALIDATE_CONVERSION. Diese gibt 1 zurück, falls eine Konvertierung möglich ist, sonst 0. Die Syntax orientiert sich dabei an CAST:

SELECT VALIDATE_CONVERSION('19a' AS NUMBER) keine_zahl,
       VALIDATE_CONVERSION('19' AS NUMBER) zahl,
       VALIDATE_CONVERSION('1234,23' AS NUMBER, '0.99') falsches_format,
       VALIDATE_CONVERSION('31.4.2020' AS DATE) falsches_datum,
       VALIDATE_CONVERSION('30.4.2020' AS DATE) datum
FROM  dual;

KEINE_ZAHL       ZAHL FALSCHES_FORMAT FALSCHES_DATUM      DATUM
---------- ---------- --------------- -------------- ----------
         0          1               0              0          1

Diese Funktion ist extrem hilfreich, um „fehlerhafte“ Datensätze einfach zu finden, oder um nur die Datensätze zu filtern, die von Interesse sind:

SELECT wert FROM STAGE_TAB
 WHERE VALIDATE_CONVERSION(wert AS BINARY_DOUBLE) = 0;

WERT                
-----------
X

Bzw.:

SELECT TO_NUMBER(wert) wert FROM STAGE_TAB
 WHERE VALIDATE_CONVERSION(wert AS NUMBER) = 1;

      WERT
----------
         3
         6
         9
        12
        …

SLEEP-Prozedur in DBMS_SESSION

Die gängige Methode, um eine Pause im Ablauf einzulegen (beispielsweise, um in einer Schleife auf etwas Bestimmtes zu warten oder um beim Serienversand von Mails den Mailserver nicht zu überlasten), war und ist der Aufruf von DBMS_LOCK.sleep.  Nun vergeben aber Administratoren nur sehr ungern das EXECUTE-Recht auf DBMS_LOCK (wie selbst kürzlich leidvoll erlebt in einem Projekt), und das undokumentierte APEX_UTIL.pause ist auch nur im APEX-Umfeld eine Alternative, die aber mehr als Notlösung zu sehen ist.

Daher wurde in Version 18c das allgemein zugängliche Package DBMS_SESSION um eine entsprechende sleep-Prozedur erweitert. Seither gilt DBMS_LOCK.sleep als „deprecated“ und erscheint auch nicht mehr in der Doku, wird aber aus Gründen der Abwärtskompatibilität noch weiter unterstützt.  

Neuigkeiten zu LOBs

LOB-Spalten können seit Version 12.2 – endlich – auch über einen Datenbank-Link selektiert oder geändert werden.  Bis einschließlich Version 12.1 wurde ein solcher Versuch mit einem Fehler quittiert:

ORA-22992: LOB-Locators können nicht von Remote-Tabellen verwendet werden

Voraussetzung ist, dass beide Datenbanken mindestens Version 12.2 haben. Auch DBMS_LOB-Prozeduren und -Funktionen sind - mit Einschränkungen - auf „remote LOB locators“ anwendbar (Ausnahme: BFILE). Sofern die Prozeduren mit zwei LOBs arbeiten, müssen sich beide in der gleichen Datenbank befinden.

Passend dazu wurde das Package um eine weitere Funktion erweitert: ISREMOTE.

Beispiele:

CREATE TABLE MY_TAB AS SELECT * FROM TEST@HA;

DESC MY_TAB

Name    Null?    Typ    
------- -------- ------
ID      NOT NULL NUMBER
MY_LOB           CLOB   
MY_BLOB          BLOB   
DATUM            DATE 

DECLARE
   v_lob CLOB;
   v_val VARCHAR2(200);
BEGIN
   SELECT my_lob
     INTO v_lob
     FROM test@ha
      FOR UPDATE;

   IF DBMS_LOB.isremote(v_lob) THEN
      DBMS_OUTPUT.put_line('Remote');
   ELSE
      DBMS_OUTPUT.put_line('lokal');
   END IF;

   v_val := 'Das ist ein Text';

   DBMS_LOB.writeappend(v_lob,LENGTH(v_val), v_val );

   COMMIT;
END;
/

Remote:

SELECT my_lob,
       DBMS_LOB.substr@HA(my_lob, 16, 4033 ) text,
       DBMS_LOB.getlength@HA(my_lob) länge
  FROM TEST@HA;

MY_LOB                                   TEXT                  LÄNGE
---------------------------------------- ---------------- ----------
LLOTTBHXGHOWFESUFPSEORAYPOHTCYIZNWTXWUDA Das ist ein Text       4048

Hinweis: Der Output wurde formatiert.

Weitere Einzelheiten zu Remote LOBs finden Sie im "SecureFiles and Large Objects Developer's Guide", Kapitel "Distributed LOBs".

Außerdem wurde – ebenfalls mit Version 12.2 - DBMS_LOB.CLOB2FILE neu eingeführt, welches das beliebte, zeitgleich als „deprecated“ markierte DBMS_XSLPROCESSOR.CLOB2FILE ersetzt.

Achtung: Hier hat sich in die Doku ein Fehler eingeschlichen (bis hinauf zu Version 19c); die formalen Namen der Parameter stimmen nicht. Laut Doku sieht die Prozedur folgendermaßen aus:

DBMS_LOB.CLOB2FILE(
   src_cl      IN  CLOB,
   file_loc    IN  VARCHAR2,
   file_name   IN  VARCHAR2,
   csid        IN  NUMBER   := 0,
   open_mode   IN  VARCHAR2 :='wb');

Schaut man direkt in den Package-Header, dann entdeckt man stattdessen folgende Signatur:

PROCEDURE clob2file(cl                  IN CLOB,
                    flocation           IN VARCHAR2,
                    fname               IN VARCHAR2,
                    csid                IN NUMBER := 0,
                    openmode            IN VARCHAR2 := 'wb');

Bei namentlicher Notation funktioniert natürlich nur das „Ist“ aus dem Package-Header.

Fazit

Neben den großen Features bieten neue Releases auch immer wieder kleine „Perlen“, die oft untergehen. Dieser Tipp hat sich bewusst auf drei davon beschränkt, die im Entwickler-Alltag eine Rolle spielen.