Oracle SQL und die Bedeutung verschiedener Sonderzeichen

Quellenangabe: siehe unten

"

"Oracle SQL und die Bedeutung verschiedener Sonderzeichen

Momentan arbeite ich an einem etwas größerem Data-Warehouse Projekt und verwalte dort die SQL-Scripte. Dabei kommt es schon vor, dass verschiedene Sachen in die Datenbank eingefügt werden müssen. Letztens stolperte ich dabei über das folgende Phänomen:

SQL> insert into temp_data(name, version) values ('A & P', 3);
Enter value for p: blah
old   1: insert into temp_data values ('A & P', 3)
new   1: insert into temp_data values ('A blah', 3)

1 row created.
 

Was passiert hier? Nun, Zeichenketten, die mit dem Kaufmannsund anfangen, werden von Oracle als Beginn von Variablennamen definiert. So kann man den Inhalt einer Variablen in ein Textfeld einfügen. Eine Variable kann z.B. so definiert werden:

col spoolfile new_value spoolfile
select '/tmp/logfile_'||to_char(sysdate,'yyyymmdd_HHMI') || '.txt' as spoolfile from dual;
spool &spoolfile
 

Hier wird in die Variable spoolfile der aktuelle Zeitstempel gespeichert und diese Variable nachher genutzt, um die Log-Datei festzulegen.

Bis hier ist es ok. Wie kann man jetzt ein '&' innerhalb eines Insert-Statements verwenden, obiges Statement funktioniert ja nicht? Man kann Oracle beibringen, dass es keine Variablen-Substitution machen soll. Dies geschieht über ein Set-Statement:

-- Ausschalten des Interpretierens von Variablennamen
-- Dadurch kann & innerhalb von SQL-Statements angegeben werden
set define off
 

Wenn man jetzt also ein Kaufmannsund braucht und Variablensubstitution nicht ausschalten möchte, ist das schwieriger zu erreichen. Die offensichtliche Lösung besteht darin ähnlich wie in den SELECT-Abfragen die Wildcards % und _ mittels ESCAPE-Zusatz zu maskieren. Die Zeichen % stehen für ein Match auf beliebig viele Zeichen, der Unterstrich matcht genau ein beliebiges Zeichen, wenn man jetzt nach dem Zeichen % sucht, muß man dieses dann eben escapen:

SELECT * FROM testtab WHERE textfeld LIKE '%20@%%' ESCAPE '@';
 

Hiermit würde man alle Felder finden, in denen die Zeichenkette "20%" vorkommt, egal ob am Anfang oder mittendrin oder am Ende.

Das funktioniert nur leider eben nicht für Insert-Statements:

SQL> insert into temp_data(name,version) values ('A @& P' ESCAPE '@', 3);
Enter value for p: blah
old   1: insert into temp_data values ('A & P', 3)
new   1: insert into temp_data values ('A blah', 3)

1 row created.
 

Wer sich jetzt fragt, warum ich darüber blogge, reicht doch ein einfaches set define off vor dieser Anweisung und evtl. hinterher ein set define on um die Variablensubstitution aus- und wieder einzuschalten, hat noch nicht folgendes probiert:

col version new_value version
select versionsnr into version from versionstabelle where status='aktiv';
SQL> insert into temp_data(name,version) values ('A & P', &version);
Enter value for p: blah
old   1: insert into temp_data values ('A & P', &version)
new   1: insert into temp_data values ('A blah', 3)

1 row created.
 

Nein, clevererweise hat sich Oracle hier etwas ganz besonderes einfallen lassen. ESCAPE wäre ja auch zu einfach gewesen. Möchte man unbedingt und auf jeden Fall sowohl Variablen, als auch das Kaufmannsund innerhalb eines Statements haben, muß man diese Zeichenkette als 2 Zeichenketten angeben und diese dann noch konkatenieren. Dabei muß das Kaufmannsund das letzte Zeichen des ersten Teils sein. Das obige Statement sähe dann etwa so aus:

col version new_value version
select versionsnr into version from versionstabelle where status='aktiv';
SQL> insert into temp_data(name,version) values ('A &'||' P', &version);
old   1: insert into temp_data values ('A & P', &version)
new   1: insert into temp_data values ('A & P', 3)

1 row created.
 

Ja genau, auf sowas muß man erstmal kommen und in den Oracle-Dokus steht dazu nämlich garantiert nichts, zumindest nichts offensichtliches.

Disclaimer: alle obigen SQL-Statements sind aus meiner Erinnerung abgetippt. Sie sind evtl. nicht ganz syntaktisch korrekt, aber ich hab zuhause halt keine Oracle laufen und so muß ich mein Gedächtnis bemühen.
Ein Dank geht an "The Oracle (tm) Users' Co-Operative FAQ"
"


Quelle:
http://blog.256bit.org/archives/272-Oracle-SQL-und-die-Bedeutung-verschiedener-Sonderzeichen.html