Re: Problem of using LONG datatype in SQL

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/20
Message-ID: <33d14546.36610425@www.sigov.si>#1/1

On Thu, 17 Jul 1997 19:03:09 -0400, terryg8 <trg_at_ibm.net> wrote:

>> Maoz Mussel <mmussel_at_iil.intel.com> wrote in article
>> <33C9D43C.6DD0_at_iil.intel.com>...
>> I want to run queries based on a LONG field in the where clause, but
>> since this datatype could not be used in the where statement, I cannot
>> run such a query:
>> .....[SNIP].....
>You could dump the rowid and long data into a file and do some stuff
>with the utl_file package. It could all be done within a plsql
>procedure. There may certainly be more elegant approaches.

I'm afraid this won't work because UTL_FILE can handle only lines up to little more then 1000 characters (1013 if I remember right).

But if the maximum length of the text inside your long column is less than 32K bytes you can take the advantage of the fact that PL/SQL's VARCHAR2 datatype can have maximum length 32767 bytes.

So you can write a stored function which will return TRUE/FALSE if the long column contains/doesn't contain the search string (you can use INSTR frunction for searching). You can then use the return value from this function in your WHERE part of the SELECT statement.

>For the insert, you can use cursors, fetching row elements
>including longs from the first table for insertion into the second.
>Cheers,
>Terry
>

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Jul 20 1997 - 00:00:00 CDT