Xref: alice comp.databases.oracle.server:34011
Path: alice!news-feed.fnsi.net!news.idt.net!news-peer.gip.net!news.gsl.net!gip.net!demos!newsfeed.gamma.ru!Gamma.RU!carrier.kiev.ua!news.lucky.net!not-for-mail
From: "Kostya Ogrodnichy" <kostya@kpvti.kiev.ua>
Newsgroups: comp.databases.oracle.server
Subject: Re: Piecewise access of LONG datatype?
Date: Tue, 17 Nov 1998 14:46:40 +0200
Organization: KPVTI
Lines: 34
Message-ID: <911306757.370001@ns.kpvti.kiev.ua>
References: <36510430.3F0852AC@trw.com>
X-Trace: news.lucky.net 911306622 26583 193.193.220.30 (17 Nov 1998 12:43:42 GMT)
X-Complaints-To: abuse@news.lucky.net
NNTP-Posting-Date: 17 Nov 1998 12:43:42 GMT
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Cache-Post-Path: ns.kpvti.kiev.ua!unknown@9.68.174.105
X-Cache: nntpcache 2.3.2 (see http://www.nntpcache.org/)
Cache-Post-Path: frodo!unknown@kpvti-gw.777.com.ua
X-Cache: nntpcache 2.3.3b4 (see http://www.nntpcache.org/)


Harvey 'Bugs' Mitchell wrote in message <36510430.3F0852AC@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;