Re: Extracting Substrings from LONG datatype

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Sep 1999 10:49:42 GMT
Message-ID: <37ce55cc.52973732@newshost.us.oracle.com>

A copy of this was sent to Allen Brown <anb_at_nortelnetworks.com> (if that email address didn't require changing) On Wed, 01 Sep 1999 18:25:09 -0400, you wrote:

>Surely someone knows how to do this:
>
>I have string data encoded into a LONG datatype field in Oracle 8. I am
>confident that I know what substrings begin and end the substrings that
>I must extract, and they may include single quote characters. Each of
>the substrings is likely (but not necessarily) less that 4K characters
>long. The usual substring extraction functions do not work on a LONG
>datatype.
>
>How can I extract the substrings from the LONG datatype within a
>"select" query?
>
>Many thanks,
>
>Allen Brown

If the LONG is less then/equal to 32k

and

If the extracted piece s less then/equal to 4000 bytes

then, something like this:

tkyte_at_8.0> create table t ( long_column long ); Table created.

tkyte_at_8.0> create or replace function long_substr( p_rowid in rowid,

  2                                          p_from_char in number,
  3                                          p_for_char in number ) return

varchar2
  4 as
  5 l_long long;
  6 begin

  7      select long_column into l_long
  8        from t
  9       where rowid = p_rowid;
 10  
 10      return substr( l_long, p_from_char, p_for_char );
 11  exception
 12      when no_data_found then return null;

 13 end;
 14 /
Function created.

tkyte_at_8.0> insert into t values ( 'How Now Brown Cow How Now Brown Cow' ); 1 row created.

tkyte_at_8.0> select substr( long_column, 5, 10 ) from t; select substr( long_column, 5, 10 ) from t

               *
ERROR at line 1:
ORA-00932: inconsistent datatypes

tkyte_at_8.0> select long_substr( rowid, 5, 10 ) from t;

LONG_SUBSTR(ROWID,5,10)

---

Now Brown

can be used. If the function ever returns more then 4000 bytes -- you'll get numeric or value error. If the long is bigger then 32k, you'll get numeric or value error.

Are you using Oracle8i?
Why not use a LOB which has substr, instr, etc?

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 05:49:42 CDT