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.
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

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:

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-Spalten

Ein 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

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

Aber mit einem kleinen Trick bekommen wir das doch noch hin

Wenn Sie z.B. auf die Spalte view_text aus DBA_VIEWS zugreifen möchten, gehen Sie wie folgt vor:
1. rufen Sie die Funktion sys_dburigen mit den Parametern: Eigentümer der Tabelle/View, Name der Tabelle/View und Spaltenname auf:

CREATE OR REPLACE VIEW muso_views as
SELECT  o.owner,o.object_name, v.text,v.textlength
FROM view$ v, dba_objects o
WHERE v.obj#=o.object_id
and object_type='VIEW';
SELECT substr(sys_dburigen( s.owner,s.object_name, s.text, 'text()').getclob(),1,s.textlength)
FROM muso_views s;

Hinweis: Leider müssen die Daten aus der Originaltabelle geholt werden, da die View die Long Spalte ab Version 19c nur noch gekürzt als VARCHAR2 mit Länge 4000 Zeichen zur Verfügung stellt.

2. verwenden Sie nund ie gewünschte Funktion aus 6. (z.B. substr)

SELECT substr(sys_dburigen( owner,view_name, text, 'text()').getclob(),1,100)
FROM dba_views
WHERE owner='SCOTT';

Das Ganze geht natürlich mit der Long Spalte:
TRIGGER_BODY aus Tabelle DBA_TRIGGERS
SEARCH_CONDITION aus Tabelle DBA_CONSTRAINTS
u.v. weiteren LONG Spalten
Achtung: Die 3 Parameter müssen genau eine Zeile zurückgeben, sonst erhält man den Fehler:
ORA-19003 XML document must have a top level element
Deswegen funktioniert der TRcik auch leider nicht bei:

SELECT sys_dburigen( table_owner,partition_name,HIGH_VALUE, 'text()').getxml() FROM all_tab_partitions
WHERE table_owner='AUDSYS' AND table_name='AUD$UNIFIED' ;

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.

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.) PL/SQL Package zum Berechnen der LONG Spaltenlänge und zum Auslesen von Daten

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:

    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)
    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;
    dbms_sql.parse( l_cursor,'select ' || v_col || ' from ' ||    v_tab ||
    ' 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;
   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);
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
eached');
 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);
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
  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