Home » SQL & PL/SQL » SQL & PL/SQL » Updating a LONG Column
Updating a LONG Column [message #36724] Fri, 14 December 2001 06:27 Go to next message
Frank Kings
Messages: 5
Registered: December 2001
Junior Member
I am trying to update a table where I want to update a long column with the row_id (VARCHAR2) concatenated with '-TEXT'. I have tried with the following:
DECLARE
l_row_id long;

CURSOR cur_note
IS
select row_id, note
from table.s_note;
BEGIN
FOR cur_con_note IN cur_note
LOOP

l_row_id := cur_con_note.row_id||'-TEXT';

UPDATE siebel.s_note
SET note = l_row_id
WHERE ROW_ID = cur_con_note.row_id
AND cur_con_note.note IS NOT NULL;

END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);

END;

I get the following error:
ERROR: ORA-01461: can bind a LONG value only for insert into a LONG column

Does this mean I can not update a LONG column and if I can, how?

----------------------------------------------------------------------
Re: Updating a LONG Column [message #36729 is a reply to message #36724] Fri, 14 December 2001 07:23 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
declare l_row_id as long

CURSOR cur_note
IS
select row_id, note
from table.s_note;
--declare long variable
l_row_id long;
BEGIN
FOR cur_con_note IN cur_note
LOOP

l_row_id := cur_con_note.row_id||'-TEXT';

UPDATE siebel.s_note
SET note = l_row_id
WHERE ROW_ID = cur_con_note.row_id
AND cur_con_note.note IS NOT NULL;

END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);

END;

----------------------------------------------------------------------
Re: Updating a LONG Column [message #36731 is a reply to message #36724] Fri, 14 December 2001 08:45 Go to previous message
Frank Kings
Messages: 5
Registered: December 2001
Junior Member
Yeah, I had already placed the variable as a long if you notice under the DECLARE. The problem has been fixed, apparently, it seemed to be something with the IS NOT NULL clause in the cursor. I removed that and it worked. Must be some sort of bug.

----------------------------------------------------------------------
Previous Topic: HELP PLEASE!
Next Topic: Sql Statements
Goto Forum:
  


Current Time: Tue Mar 31 05:45:15 CDT 2020