Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Would like to do .... LIKE '%fred%' on LONG column

Re: Would like to do .... LIKE '%fred%' on LONG column

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Wed, 21 Oct 1998 09:09:40 -0700
Message-ID: <362E0744.F4A8A763@uclink4.berkeley.edu>


Paul,

     You could pull out chunks of the LONG with the DBMS_SQL.Column_Value_Long procedure. Using PL/SQL, you would pull out 32K (or any smaller size) chunks, assign them to a VARCHAR and then look for '%fred%' within that chunk. The obvious downsides:

  1. If 'fred' is on the 32K border and then split (e.g. chunck A ends with ' fre' and chunk B starts with 'd '). You could solve this by doing a four searches, offsetting the search by 0, 1, 2, then 3.
  2. If the record is anywhere near the 2GB max size, then it would take a quite a long time to search through several records, let alone search through each 4 times.

If you know the records that you are searching through are less then 32K, or you are just looking for 'fred' in the first 32K of the record, then Column_Value_Long wouldn't be too horrific.

If you're interested I'll post a PL/SQL procedure for you to do this.

Jay!!!

Paul Smith wrote:

> I would like to add a where clause to a select on a long column, but I always
> get an oracle error stating that there are inconconsistent datatypes
> (ORA-00932). I can't even do a TO_CHAR() on the column. This seems like a big
> oversight to me - is there *any* way to do what I want to do?
>
> --
> Paul Smith
> Technical Manager, SDR Systems Ltd
> Please change Compuserve.comz to Compuserve.com to respond via Email
Received on Wed Oct 21 1998 - 11:09:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US