Re: LONG datatypes - what's the trick?

That's not a valid use of LONG datatypes. You also can't insert as select if LONGs are involved. You can use the COPY command to move LONG values around, but you're trying to do an update. If the LONG values are small enough (<32768 characters in length - but good luck measuring this since you can't do a LENGTH function on the LONG column), use PL/SQL. Create a PL/SQL variable, make it VARCHAR2(32767) and read the value into the variable. Use that VARCHAR2 value for the comparisons you're trying to make.

Ugly? Yes, but it works if you can handle the length limitation. I prefer not to store large non-relational data attributes in relational databases.

hth.
Kevin
see also http://www.kevinloney.com/free/fga.htm , question 4.

Paul A. Horan wrote in message
<#648F1kZ#GA.320_at_nih2naaa.prod2.compuserve.com>...
>I have two tables, each defined with a primary key, and a single column of
>LONG datatype. These are simple long text columns, designed to store
>copious notes
>
>Why can't I say:
>UPDATE table2
> set table2.long_col =
> (Select table1.long_col
> where table1.primary_key = table2.primary_key )
> WHERE exists
> (Select 1
> from table1
> where table1.primary_key = table2.primary_key) ;
>
>The error message I'm getting is:
>ORA-00997: illegal use of LONG datatype
>
>Is this kind of update statement not allowed? What are my options here?
Received on Thu Mar 04 1999 - 11:43:24 CST