This discussion is archived
4 Replies Latest reply: Oct 9, 2012 12:19 AM by 966814 RSS

ORA-01461 can bind a LONG value only for insert into a LONG column

966814 Newbie
Currently Being Moderated
We've got a weird error when we try to insert a blob attribute (> 4k) into a view where the base table has an unusable index on inactive (historical) partition.
Do you have an idea why this is working, when we insert directly to the base table or when we rebuild the unusable index?
We've found this error when we used Java Hibernate mapping, but we succeeded to reproduce this behaviour in pl/sql as well.

The error comes out only when these 4 conditions are met:
1. inserting a blob > 4k
2. inserting into a view
3. inserting using setBinaryStream (or in pl/sql EXECUTE IMMEDIATE 'insert into .. values (:1)' USING IN HEXTORAW (l_data))
4. unusuable index on an inactive partition (data is not inserting there anymore, just for historical purposes) of a table


--DROP TABLE canBindLong
CREATE TABLE canBindLong
(
id NUMBER,
b BLOB,
note varchar2(50)
)
PARTITION BY RANGE (ID)
(PARTITION negat
VALUES LESS THAN (0),
PARTITION act
VALUES LESS THAN (MAXVALUE));

CREATE UNIQUE INDEX cbl_idx ON canBindLong(id) local;

INSERT INTO canBindLong VALUES (1,NULL,null);
INSERT INTO canBindLong VALUES (-1,NULL,null);

CREATE OR REPLACE VIEW vCanBindLong AS SELECT id,b,note FROM canBindLong;

DECLARE
l_data LONG;
BEGIN
FOR i IN 1 .. 8000
LOOP
l_data := l_data || 'E';
END LOOP;

EXECUTE IMMEDIATE 'insert into vcanBindLong values (:1,:2,:3)' USING IN 10, HEXTORAW (l_data),'view';
EXECUTE IMMEDIATE 'insert into canBindLong values (:1,:2,:3)' USING IN 11, HEXTORAW (l_data),'table';

l_data := l_data || 'E';

EXECUTE IMMEDIATE 'insert into vcanBindLong values (:1,:2,:3)' USING IN 12, HEXTORAW (l_data),'view';
EXECUTE IMMEDIATE 'insert into canBindLong values (:1,:2,:3)' USING IN 13, HEXTORAW (l_data),'table';

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.Put_Line( sqlerrm||CHR(10)||dbms_utility.format_error_backtrace );

END;
/


select dbms_lob.getlength(b),t.* from canBindLong t ORDER BY id desc;

alter index cbl_idx modify partition negat unusable;
SELECT partition_name, status from user_ind_partitions ip where index_name=upper('cbl_idx') order by 1;

DECLARE
l_data LONG;
BEGIN
FOR i IN 1 .. 8000
LOOP
l_data := l_data || 'E';
END LOOP;

EXECUTE IMMEDIATE 'insert into vcanBindLong values (:1,:2,:3)' USING IN 20, HEXTORAW (l_data),'view';
EXECUTE IMMEDIATE 'insert into canBindLong values (:1,:2,:3)' USING IN 21, HEXTORAW (l_data),'table';

l_data := l_data || 'E';

EXECUTE IMMEDIATE 'insert into canBindLong values (:1,:2,:3)' USING IN 23, HEXTORAW (l_data),'table';
EXECUTE IMMEDIATE 'insert into vcanBindLong values (:1,:2,:3)' USING IN 22, HEXTORAW (l_data),'view';

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.Put_Line( sqlerrm||CHR(10)||dbms_utility.format_error_backtrace );

END;
/

select dbms_lob.getlength(b),t.* from canBindLong t ORDER BY id desc;

alter index cbl_idx rebuild partition negat;
SELECT partition_name, status from user_ind_partitions ip where index_name=upper('cbl_idx') order by 1;

DECLARE
l_data LONG;
BEGIN
FOR i IN 1 .. 8000
LOOP
l_data := l_data || 'E';
END LOOP;

EXECUTE IMMEDIATE 'insert into vcanBindLong values (:1,:2,:3)' USING IN 30, HEXTORAW (l_data),'view';
EXECUTE IMMEDIATE 'insert into canBindLong values (:1,:2,:3)' USING IN 31, HEXTORAW (l_data),'table';

l_data := l_data || 'E';

EXECUTE IMMEDIATE 'insert into vcanBindLong values (:1,:2,:3)' USING IN 32, HEXTORAW (l_data),'view';
EXECUTE IMMEDIATE 'insert into canBindLong values (:1,:2,:3)' USING IN 33, HEXTORAW (l_data),'table';

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.Put_Line( sqlerrm||CHR(10)||dbms_utility.format_error_backtrace );

END;
/

select dbms_lob.getlength(b),t.* from canBindLong t ORDER BY id desc;

Edited by: user12132728 on 8.10.2012 12:19

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points