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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
