Re: Updating LONG data type columns

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Wed, 19 Nov 2003 01:23:37 GMT
Message-ID: <tCzub.12492$st4.8242@news01.roc.ny>

"Dave Pylatuk" <davep_at_centurysystems.net> wrote in message news:XGuub.9213$iT4.991931_at_news20.bellglobal.com...
> Hello all. I have a question about updating a LONG datatype
> column in Oracle 8.1.7.
>
> I have a STUDENT table like:
>
> student_id INTEGER PK
> student_comments LONG
>
> I want to execute a command like this:
>
> UPDATE student SET student_comments = student_comments || 'some new
> comments' WHERE student_id = 1;
>
> I am getting an error message indicating incompatible data types.
> Do I have to do some sort of conversion/cast to add my new
> comments to the existing ones ?
>
> Thanks in advance
>
>
>

Long datatypes have a lot of restrictions. I'd advise you to first read the sql reference present on http://tahiti.oracle.com .. In the index browse to LONG -> restrictions.

In your case you could do something like this (using pl/sql):

declare

v long;
begin

select student_comments into v from student where student_id = 1; v := v || 'comment';
update student set student_comment = v where student_id = 1; end;
/

Oracle advises converting long to clob (which have lesser restrictions .. especially 9i onwards).

Anurag Received on Tue Nov 18 2003 - 19:23:37 CST