Re: LONG COLUMN updates (8.0.5)

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Mon, 11 Oct 1999 00:42:08 +0200
Message-ID: <38011640.187D0141@0800-einwahl.de>

I tried in Oracle8i NT 4.0

SQL> 
SQL> whenever sqlerror exit failure rollback
SQL> 
SQL> create table longtable1 (
  2  	     long_text long

  3 )
  4 /

Tabelle wurde angelegt.

 real: 40
SQL>
SQL> insert into longtable1 (long_text)   2 values (
  3 'itit'
  4 )
  5 /

1 Zeile wurde erstellt.

 real: 10
SQL>
SQL> select * from longtable1
  2 /
itit

1 Zeile wurde ausgewählt.

 real: 0
SQL>
SQL> declare
  2
  3 cursor c
  4 is
  5 select long_text
  6 from longtable1
  7 for update of long_text;
  8
  9 r c%rowtype;
 10
 11 v_nt long;
 12
 13 i number:=0;
 14
 15 begin
 16
 17 open c;
 18 loop

 19  	 fetch c into r;
 20  	 exit when c%notfound;
 21  	 i:= i+1;
 22  
 23  	 v_nt := replace(r.long_text,'it',null);
 24  	 -- v_nt := replace(r.long_text,'===',null);
 25  
 26  	 update longtable1
 27  	 set long_text = v_nt
 28  	 where current of c;

 29
 30 end loop;
 31 dbms_output.put_line('#recs '||i);  32 close c;
 33 end;
 34 /
#recs 1

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

 real: 40
SQL>
SQL> select * from longtable1
  2 /

1 Zeile wurde ausgewählt.

 real: 10

SQL> 
SQL> whenever sqlerror continue
SQL> 
SQL> spool off

I cannot reproduce your problem...

Martin

Jeremy Ovenden wrote:
>
> What exactly does the mesg ORA-01461 mean? The manual explains it:
>
> ORA-01461: can bind a LONG value only for insert into a LONG column
> Cause: An attempt was made to insert a value from a LONG datatype into
> another datatype. This is not allowed.
> Action: Do not try to insert LONG datatypes into other types of columns.
>
> See the folowing simple pl/sql block
>
> declare
>
> cursor c
> is
> select long_text
> from table1
> for update of long_text;
>
> r c%rowtype;
>
> v_nt long;
>
> i number:=0;
>
> begin
>
> open c;
> loop
> fetch c into r;
> exit when c%notfound;
> i:= i+1;
>
> v_nt := replace(r.long_text,'===',null);
>
> update table1
> set long_text = v_nt
> where current of c;
>
> end loop;
> dbms_output.put_line('#recs '||i);
> close c;
> end;
> /
>
> When I run this, it will execute no problems. However, when I simply changed
> the replacement text from '===' to simply 'it', Oracle comes back with the
> ORA-01461 message. Any ideas what could be causing this? Is it possibly a
> bug, or am I mis-coding something here?
>
> Thanks.........
>
> Jeremy Ovenden
Received on Sun Oct 10 1999 - 17:42:08 CDT