RE: GETTING FIRST 250 CHARACTERS FROM A LONG DATATYPE

Stephane,

You could write a plsql by defiining a variable with 32K (like fetch_log_value varchar2(32760))
You could fetch the long data type column value into fetch_long_value variable after opening the cursor. After that you can use substr on this variable.

Baskar

-----Original Message-----
Sent: 04 July 2001 23:20
To: Multiple recipients of list ORACLE-L

Mark Liggayu wrote:
>
> HI GURUS,
>
> If I have a table that has a field with long data type and I want to get
the
> first 250 characters out of it how can I do it. I tried using SUBSTR(long
> data type field, 1, 250) but it complained about "ORA-00932: inconsistent
> datatypes". I then tried TO_CHAR(SUBSTR(long data type field, 1, 250))
but
> still gave me the same result. Tried TO_VARCHAR2 but to no avail.
>
> Thanks,
> Mark
>

You can't apply a function to a LONG (but perhaps the LONG-to-LOB conversions of 8i). If this is possible to you (ie Oracle version supporting it), convert to CLOB or similar. Otherwise, all I can think of is a user-written PL/SQL function (would work with Oracle 7.3 and, of course, above). In PL/SQL, VARCHAR2 can be up to 32K (or around) and you CAN select a LONG into a large enough VARCHAR2. If your LONGs are under 32K, it is easy to write a function LONGSTART which fetches the suitable LONG into a suitably long VARCHAR2, and returns the 250 first characters of this VARCHAR2. Note that I am talking about feasibility, not performance (it may not be THAT bad but I wouldn't do it on millions of rows).

-- 
HTH,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ramasamy, Baskar
  INET: Baskar.Ramasamy_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).