Besonderheiten des Datentyps LONG

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

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';