Re: Piecewise access of LONG datatype?

From: Kostya Ogrodnichy <kostya_at_kpvti.kiev.ua>
Date: Tue, 17 Nov 1998 14:46:40 +0200
Message-ID: <911306757.370001@ns.kpvti.kiev.ua>

Harvey 'Bugs' Mitchell wrote in message <36510430.3F0852AC_at_trw.com>...
>Hi,
>
>I need to get the first 200 characters of a LONG datatype into a
>VARCHAR2. Is this possible? So far everything I've tried has failed.
>Any help would be appreciated. Thanks.
>
>--Bugs--
>

In http://www.dbspecialists.com you find :

REATE TABLE html_page_texts(page_id NUMBER PRIMARY KEY,html_text LONG);

CREATE FUNCTION my_instr2 (search_page IN NUMBER, search_text IN VARCHAR2, start_pos IN NUMBER DEFAULT 1) RETURN NUMBERAS   c NUMBER := dbms_sql.open_cursor; i NUMBER; pos NUMBER := start_pos;
  len NUMBER; data VARCHAR2(32767);BEGIN   dbms_sql.parse (c, 'SELECT html_text FROM html_page_texts WHERE page_id = :p',

                  dbms_sql.native);

  dbms_sql.bind_variable (c, 'p', search_page);   dbms_sql.define_column_long (c, 1); i := dbms_sql.execute_and_fetch (c);   IF i = 1 THEN LOOP

      dbms_sql.column_value_long (c, 1, 32767, pos, data, len);
      i := INSTR (data, search_text);      IF i > 0 THEN
        dbms_sql.close_cursor (c);        RETURN i + pos - 1;      END IF;
      EXIT WHEN len < 32767;      pos := pos + 32767 - LENGTH (search_text);

    END LOOP; END IF; dbms_sql.close_cursor (c); RETURN NULL;END my_instr2; Received on Tue Nov 17 1998 - 06:46:40 CST