Als PDF Downloaden!

Tipps & Tricks: März 2007
Bereich:
PL/SQL, SQL
Erstellung:
03/2007
Versionsinfo:
9.2, 10.2, 11.2
Letzte Überarbeitung:
06/2009 MP
Besonderheiten des Datentyps LONG
In unseren Schulungen werden wir immer mal wieder zu dem Datentyp LONG befragt.
Hierzu gibt eine Reihe von Einschränkungen, nur leider keine übersichtliche Dokumentation zum Thema Oracle
LONG Spalten.
Das soll sich ändern, wir haben hier nun die wichtigsten Punkte zu Oracle LONG Spalten zusammengefasst:
LONG Spalten wurden in Oracle 7.3 eingeführt und sollten die Beschränkung von VARCHAR2 Spalten die nur
4000 Zeichen aufnehmen können, beheben.
Deswegen wurden zwei neue Datentypen geschaffen LONG (kann bis 2 GB an Textdaten aufnehmen) und LONG
RAW (kann bis zu 2 GB an Binärdaten speichern).
Leider hat man keine Routinen dazu entwickelt, die mit dieser Speichermenge umgehen konnten. Man hat
deshalb mit 2 Speicherbegrenzungen zu kämpfen:
SQL Funktionen können (wenn überhaupt) nur die ersten 4000 Zeichen einer LONG-Spalte nutzen.
In PL/SQL kann man die ersten 32760 Zeichen lesen/schreiben. Mit einem Trick (dazu später mehr)
lesen wir aber auch darüber hinaus.
Inhaltsangabe
1.Beschränkungen von LONG Spalten
2.Constraints auf LONG Spalten
3.Index auf LONG Spalten
4.LONG Spalten füllen (bis 4000 Zeichen)
5.LONG Spalten in PL/SQL füllen (bis 32760 Zeichen)
6.Funktionen SUBSTR, INSTR, LENGTH
7.Trigger auf LONG Spalten
8.Ausgabelänge von LONG Spalten in SQL*Plus
9.Tabelle mit LONG Spalten kopieren
10.LONG Spalten konvertieren
11.LONG Spalten in PL/SQL
12.PL/SQL Package zum Berechnen der LONG Spaltenlänge und zum Auslesen von Daten
Wo kann man LONG Spalten verwenden?
SELECT auf eine LONG Spalte
In der SET Klausel eines UPDATE Statements
In der VALUES Klausel eines INSERT Statements
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 12
1.) Beschränkungen von LONG Spalten
Objekt Typen dürfen keine LONG Attribute besitzen.
LONG Spalten dürfen nicht in einer WHERE Klausel vorkommen.
LONG Spalten dürfen nur NULL oder NOT NULL Constraints besitzen.
LONG Spalten dürfen keinen Index besitzen (Ausnahme Text-Index).
Reguläre Ausdrücke können nicht auf LONG Spalten verwendet werden.
Funktionen (auch innerhalb eines Package) dürfen keinen LONG Wert zurückgeben (Nur
VARCHAR2(4000)).
Bei einem Join über mehrere Datenbanken müssen sich alle LONG Spalten auf einer Datenbankseite
befinden.
LONG und LONG RAW Spalten können nicht repliziert werden.
Wenn eine Tabelle LONG und LOB Spalten enthält, dürfen nur jeweils 4000 Zeichen in die Spalten
geschrieben werden.
GROUP BY, ORDER BY, CONNECT BY oder DISTINCT dürfen nicht in Verbindung mit LONG Spalten
verwendet werden.
Eine Tabelle darf nur eine LONG/LONG RAW Spalte besitzen.
Eine Tabelle mit LONG Spalte kann nicht mittels CREATE TABLE ... AS SELECT <LONG> FROM
<table> kopiert werden.
" ALTER TABLE ... MOVE"-Befehl kann nicht verwendet werden, wenn die Tabelle eine LONG Spalte
besitzt.
Immerhin werden LONG Spalten wohl auch noch in Oracle 11g unterstützt :-)
Das schauen wir uns mal genauer an ...
CREATE TABLE t (
id
NUMBER,
text LONG,
bild LONG RAW);
FEHLER in Zeile 4:
ORA-01754: Eine Tabelle darf nur eine einzige Spalte des Typs LONG aufweisen
Zweiter Versuch funktioniert, da nur eine LONG Spalte verwendet wird:
CREATE TABLE t (
id NUMBER,
text LONG);
Das gleiche kann man auch mit einer LONG RAW Spalte machen:
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 12
CREATE TABLE test_LONG_raw (
id
NUMBER,
long_col_raw LONG RAW);
2.) Constraints auf LONG-Spalten
Nur NOT NULL Constraints sind erlaubt
Die Constraint-Typen P (Primary Key), U (Unique Key), C (Check) können nicht verwendet werden.
CREATE TABLE t (
id NUMBER,
l
LONG NOT NULL);
... alle anderen Constraints sind nicht erlaubt
CREATE TABLE t (
id NUMBER,
l
LONG PRIMARY KEY);ORA-02269:
Schlüsselspalte kann nicht vom Datentyp LONG sein
02269: "key column cannot be of LONG datatype"
*Cause: Self-evident.
*Action: Change the datatype of the column, or remove the column from the key.
3.) Indizierung von LONG-SpaltenEin normaler Index kann nicht auf eine LONG-Spalte gesetzt werden,
aber ein Text-Index schon.
CREATE INDEX i ON t(text);
Error: ORA-00997: Unzulässige Verwendung des Datentyps LONG
Workaround: Text-Index setzen
CREATE INDEX i ON t(text) INDEXTYPE IS ctxsys.context;
SELECT * FROM t WHERE CONTAINS( text, 'Muniqsoft GmbH' ) > 0;
4.) LONG Spalte füllen (bis zu 4000 Zeichen)
INSERT INTO t VALUES (1,lpad('#',4000,'#'));
LONG RAW Spalte füllen
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 12
INSERT INTO test_LONG_raw (id,LONG_col_raw)
VALUES(1,utl_raw.cast_to_raw('abcdefghijklmnopqrstuvwxyz'));
5.) Füllung einer LONG-Spalte mit 32760 Bytes in PL/SQL
DECLARE
v_l LONG:=lpad('#',32760,'#');
BEGIN
INSERT INTO t VALUES (1,v_l);
COMMIT;
END;
/
Update eines LONG-Datentyps mit bis zu 32760 Bytes
DECLARE
v_l LONG;
CURSOR c IS SELECT text FROM t
WHERE id=2 FOR UPDATE;
BEGIN
FOR r IN c LOOP
v_l:=r.text || ' Neuer Text';
UPDATE t SET text = v_l WHERE CURRENT OF c;
END LOOP;
COMMIT;
END;
/
6.) Die Funktionen INSTR, SUBSTR und LENGTH können nicht auf LONG-Spalten angewendet werden
SELECT LENGTH(text) FROM t WHERE id=1;
FEHLER in Zeile 1:
ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten
SELECT SUBSTR(text,1,5) FROM t;
FEHLER in Zeile 1:
ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalt
SELECT INSTR(text,1,'#') FROM t;
FEHLER in Zeile 1:
ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 12
7.) Trigger auf LONG Spalten
Ein SQL Statement innerhalb eines Trigger kann Daten in eine LONG oder LONG RAW Spalte einfügen.Die
maximale Länge einer Text-Variable (VARCHAR2 oder LONG) in einem Trigger beträgt 32000.:NEW und :OLD
dürfen nicht auf LONG oder LONG RAW Spalten gesetzt werden.
LONG oder LONG RAW Spalten dürfen nur referenziert werden, wenn sie in CHAR oder VARCHAR2
konvertierbar sind.
CREATE OR REPLACE TRIGGER tr
BEFORE UPDATE OR INSERT OR DELETE
ON t
FOR EACH ROW
BEGIN
:new.text:='A';
END;
/
Error: ORA-04093: Referenzen auf Spalten von Typ LONG sind in Triggern nicht zulässig.
8.) Ausgabelänge für Oracle LONG-Spalten in SQL*Plus auf 2 Mio Zeichen setzen (Default= 80)
Wenn Sie diesen Parameter nicht umsetzen, werden bei einer Ausgabe in SQL*Plus nur die ersten 80 Zeichen
einer LONG Spalte ausgegeben.
SET LONG 200000
9.) Tabellen mit LONG Spalten kopieren
Eine Tabelle kann mit einem CREATE TABLE AS SELECT ... nicht kopiert werden, wenn LONG Spalten
enthalten sind.
CREATE TABLE t2 AS SELECT * FROM t;
FEHLER in Zeile 1:
ORA-00997: Unzulässige Verwendung des Datentyps LONG
Workaround mit Konvertierung in einen CLOB Datentypen
CREATE TABLE t2 AS SELECT to_lob(text) as text FROM t;
Tabelle wurde erstellt.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 12
10.) LONG Spalten konvertieren
Oracle empfiehlt, möglichst zeitnah auf die Datentypen CLOB, BLOB und NCLOB zu konvertieren. Diese stehen
ab Version 8.0 zur Verfügung.
Beispiel: Umwandeln von LONG in CLOB und LONG Raw in BLOB
ALTER TABLE test_LONG MODIFY (LONG_col CLOB);
ALTER TABLE test_LONG_raw MODIFY (LONG_col_raw BLOB);
11.) LONG Spalten in PL/SQL
In PL/SQL wird ein LONG-Datentyp als VARCHAR2(32760) abgebildet. Das heißt folgende beiden Deklarationen
sind identisch:
DECLARE
v_a LONG;
v_b VARCHAR2(32760);
BEGIN
NULL;
END;
/
12.) Das nachfolgende kleine PL/SQL Beispiel zeigt, dass man mit einem Spezial-Package (dbms_sql)
auch an die Daten einer LONG Spalte herankommt, die hinter der magischen Grenze von 32760 liegen.
Das Package LLONG bietet Ihnen die folgenden Möglichkeiten:
Berechnung der Länge eines LONG Feldes. Damit können Sie feststellen, ob Sie die Daten auch in
VARCHAR2(4000) bekommen könnten.
Der Aufruf dazu lautet:
LLONG.LEN(<rowid>)
Beispiel:
SELECT LLONG.LEN(rowid) FROM t;
Herausschneiden von beliebigen 400 Zeichen in der LONG-Spalte
Der Aufruf lautet:
LLONG.SUB_STR(<rowid>, <start_pos>, <end_pos>)
Der Default für die Startposiion ist 1 und der Default für die Endposition ist 4000.
Beispiele:
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 6 von 12
SELECT LLONG.SUB_STR(rowid) FROM t;
SELECT LLONG.SUB_STR(rowid, 400000,404000) FROM t;
Das Suchen innerhalb einer Long Spalte mit Like
Der Aufruf dazu lautet:
LLONG.LIKE2(<rowid>,'<such_str>')
Beispiel:
SELECT * FROM t
WHERE LLONG.LIKE2(rowid,'MARCO%') >0
Das Anhängen von Daten an eine bestimmte Zeile
Der Aufruf lautet:
LLONG.APPEND(<rowid>,'<TEXT>')
Beispiel:
EXEC LLONG.APPEND('AAAUfXAAEAAAAEAAAC',' Anhänger')
Das Anzeigen der Position eines Suchstring:
Der Aufruf dazu lautet:
LLONG.IN_STR(<rowed>,'<TEXT>',<pos>)
Beispiel:
SELECT LLONG.IN_STR(rowid,'MARCO',1) FROM t;
Und so sieht das komplette Package aus:
CREATE OR REPLACE PACKAGE LLONG
AS FUNCTION sub_str(
p_rowid in rowid,
p_startpos IN NUMBER DEFAULT 1,
p_endpos IN NUMBER DEFAULT 4000)
RETURN CLOB;
FUNCTION len( p_rowid in rowid)
RETURN VARCHAR2;
PROCEDURE append (
p_rowid IN ROWID,
p_value IN VARCHAR2);
FUNCTION like2 (
p_rowid IN ROWID,
Like_str In VARCHAR2)
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 7 von 12
RETURN NUMBER;
FUNCTION in_str (
p_rowid IN ROWID,
search_char In VARCHAR2,
position IN NUMBER DEFAULT 1)
RETURN NUMBER;
END;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY LLONG
AS
l_cursor integer default dbms_sql.open_cursor;
l_n
number;
--l_LONG_val varchar2(4000);
l_LONG_val CLOB;
l_LONG_len number;
l_buflen number := 4000;
l_curpos number := 0;
v_tab
VARCHAR2(62);
v_col
VARCHAR2(30);
offset
integer;
v_length integer;
v_collen integer;
FUNCTION get_obj_name (r ROWID) RETURN VARCHAR2
AS
BEGIN
select owner||'.'||object_name INTO v_tab
from all_objects where object_id=(
select dbms_rowid.rowid_object(r)
from dual);
RETURN v_tab;
END;
FUNCTION get_col_name (r ROWID) RETURN VARCHAR2
AS
BEGIN
select column_name INTO v_col
from all_tab_columns where (owner,table_name) IN (select
owner,object_name
from all_objects where object_id=(
select dbms_rowid.rowid_object(r)
from dual)) AND data_type='LONG';
RETURN v_col;
END;
PROCEDURE open_curs (p_rowid IN ROWID)
AS
BEGIN
v_tab:=get_obj_name(p_rowid);
v_col:=get_col_name(p_rowid);
l_cursor:=dbms_sql.open_cursor;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 8 von 12
dbms_sql.parse( l_cursor,'select ' || v_col || ' from ' ||
' where rowid = :x',dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_rowid );
dbms_sql.define_column_LONG(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
END;
v_tab ||
FUNCTION sub_str(
p_rowid in rowid,
p_startpos IN NUMBER DEFAULT 1,
p_endpos IN NUMBER DEFAULT 4000)
RETURN CLOB
AS
BEGIN
IF p_endpos-p_startpos > 4000 THEN
RETURN 'Max Return Length 4000 Bytes !';
END IF;
open_curs(p_rowid);
IF (dbms_sql.fetch_rows(l_cursor)>0) then
dbms_sql.column_value_LONG(
l_cursor, 1, p_endpos-p_startpos, p_startpos ,l_LONG_val, l_LONG_len
);
END IF;
dbms_sql.close_cursor(l_cursor);
return l_LONG_val;
EXCEPTION WHEN OTHERS THEN RETURN sqlerrm;
end; --sub_str
FUNCTION len (p_rowid in rowid)
RETURN VARCHAR2
AS
BEGIN
open_curs(p_rowid);
IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
offset := 0;
v_collen := 0;
LOOP
dbms_sql.column_value_LONG(l_cursor,
1,32767,offset,l_LONG_val, v_length);
v_collen := v_collen + v_length;
EXIT WHEN v_length < 32767 OR v_length IS NULL;
offset := offset + v_length;
END LOOP;
END IF;
dbms_sql.close_cursor(l_cursor);
return v_collen;
EXCEPTION WHEN OTHERS THEN RETURN sqlerrm;
END; --len
PROCEDURE append (
p_rowid IN ROWID,
p_value IN VARCHAR2)
AS
v_app VARCHAR2(32760);
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 9 von 12
s
VARCHAR2(1000);
BEGIN
open_curs(p_rowid);
IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
offset := 0;
LOOP
dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length);
v_app := v_app || l_LONG_val;
EXIT WHEN v_length < 32767 OR v_length IS NULL;
offset := offset + v_length;
END LOOP;
END IF;
dbms_sql.close_cursor(l_cursor);
BEGIN
v_app:=v_app||p_value;
s:='UPDATE '||get_obj_name(p_rowid)||' SET '||get_col_name(p_rowid)||' =:x WHERE
rowid=:y';
EXECUTE immediate s USING v_app,p_rowid;
EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000,'Max Col Length Limit
32767 reached');
END;
END; --append
FUNCTION like2 (
p_rowid IN ROWID,
Like_str In VARCHAR2)
RETURN NUMBER
IS
BEGIN
open_curs(p_rowid);
IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
offset := 0;
LOOP
dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length);
IF l_LONG_val LIKE like_str THEN
RETURN 1;
END IF;
EXIT WHEN v_length < 32767 OR v_length IS NULL;
offset := offset + v_length;
END LOOP;
END IF;
dbms_sql.close_cursor(l_cursor);
RETURN 0;
END; --like2
FUNCTION in_str (
p_rowid IN ROWID,
search_char IN VARCHAR2,
position IN NUMBER DEFAULT 1)
RETURN NUMBER
IS
pos NUMBER;
BEGIN
open_curs(p_rowid);
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 10 von 12
IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
offset := 0;
LOOP
dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length);
pos:= instr(l_LONG_val,search_char,mod(position,32767));
IF pos>0 and pos+offset>position THEN
RETURN pos+offset;
END IF;
EXIT WHEN v_length < 32767 OR v_length IS NULL;
offset := offset + v_length;
END LOOP;
END IF;
dbms_sql.close_cursor(l_cursor);
RETURN pos;
END; -- instr Function
END; -- PACKAGE
/
SHOW ERROR
Beispielszenario:
CREATE TABLE t ( text LONG );
INSERT INTO t VALUES ( RPAD( '*', 32000, '*' ) );
BEGIN
INSERT INTO t VALUES('#');
FOR i in 1.. 10000 LOOP
UPDATE T SET text=text||';'||to_char(i);
END LOOP;
COMMIT;
END;
/
SELECT LLONG.LEN( rowid) FROM t;
GETLONG(ROWID)
--------------------------------4000
SELECT LLONG.SUB_STR(rowid, 400000, 404000) FROM t;
Suchen in LONG Data Dictionary Spalten:
Als Beispiel wird hier in der Tabelle user_constraints Spalte search_condition nach einem Wert gesucht. Das
Beispiel ist leich abänderbar, damit auch die Tabellen DBA_VIEWS (Spalte text) oder DBA_TRIGGERS (Spalte
trigger_body) mit ihren LONG Spalten ausgelesen werden können.
CREATE OR REPLACE FUNCTION get_search_condition( p_cons_name IN VARCHAR2 )
RETURN VARCHAR2
authid current_user
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 11 von 12
IS
l_search_condition user_constraints.search_condition%type;
BEGIN
SELECT search_condition into l_search_condition
FROM all_constraints
WHERE constraint_name = p_cons_name;
RETURN l_search_condition;
END;
/
SELECT constraint_name FROM all_constraints
WHERE owner='SYSTEM'
AND get_search_condition(constraint_name) LIKE '%NOT NULL%';
PS: Die Konvertierung der Funktion kann einige Zeit in Anspruch nehmen!
CONSTRAINT_NAME
-----------------------------SYS_C002892
.....
Zum Vergleich ohne Funktion:
SELECT constraint_name FROM user_constraints;
WHERE search_condition LIKE '%NOT NULL%';
where search_condition like '%NOT NULL%'
*
FEHLER in Zeile 2:
ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten
Abschlussbemerkung:
Oracle empfiehlt zwar den Verzicht auf LONG Spalten, verwendet selber aber fleißig diesen Datentyp :-)
SELECT count(*) FROM dba_tab_columns
WHERE data_type='LONG';
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 12 von 12