Re: Howto split LONG column in smaller chunks?

From: Frederic DEBRUS <frederic.debrus_at_ces-cdr.be>
Date: 22 Oct 1999 09:47:01 GMT
Message-ID: <01bf1c72$58a326c0$3e35a99e@gal159a>

This is the ID :

CREATE OR REPLACE FUNCTION PC.f_dump_long (whereclause varchar2)

   RETURN VARCHAR2
AS

   record_found                  NUMBER;

   cur                           PLS_INTEGER := DBMS_SQL.open_cursor;

   TYPE long_rectype IS RECORD (
      piece_len                  PLS_INTEGER,
      pos_in_long                PLS_INTEGER,
      one_piece                  VARCHAR2(1000),
      one_piece_len              PLS_INTEGER
   );
   rec                           long_rectype;

BEGIN    DBMS_SQL.parse (cur,
   'SELECT <longcolumn> from <table> where ' || whereclause,    DBMS_SQL.native
   );

   DBMS_SQL.define_column_long (cur, 1);    record_found := DBMS_SQL.execute (cur);    record_found := DBMS_SQL.fetch_rows (cur);

   IF record_found > 0 THEN

      rec.piece_len := 1000;
      rec.pos_in_long := 0;
      DBMS_SQL.column_value_long (
         cur,
         1,
         rec.piece_len,
         rec.pos_in_long,
         rec.one_piece,
         rec.one_piece_len
      );
      DBMS_SQL.close_cursor (cur);
      RETURN rec.one_piece;
   ELSE
      DBMS_SQL.close_cursor (cur);
      RETURN ' ';

   END IF;
END; bmlam <bmlam_at_online.de> wrote in article <380F7A97.BB8CB111_at_online.de>...
> I saw this in a book but could not get hold of it now. How would code a
> PL/SQL procedure to get a LONG column from a table and split it into
> more manageable pieces, lets say separated by new line characters.
>
> In what type of PL/SQL variable would you SELECT the LONG column INTO,
> how do you tell the actual size of the variable; since you one can not
> apply character function as INSTR, SUBSTR to LONG columns, how could one
> split it?
>
> The background is I want to write a script that fetches the content of
> the TEXT column from USER_VIEWS and breaks it into lines so I can run a
> diff later on to compare it without another version, say in another
> schema.
>
> Thanks in advance for your help.
>
>
Received on Fri Oct 22 1999 - 04:47:01 CDT