You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Suppose we have two tables, both with BLOB field.
First table was created with undefined blob sub-type, second - with blob that we know forecast that it will store only text data.
Let's create and add multi-line content to these tables:
If we try to extract BLOBs without any casting then all will be fine.
But if we cast blob to varchar (with providing enough length to accomodate its content) then blob from "b_undefined_type" will be extracted with loosing CRLF characters:
=======
set list on;
set echo on;
select b from b_known_as_text;
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;
select b from b_undefined_type;
select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;
Output (compare data in lines [1] and [2]):
select b from b_known_as_text;
B 91:0
line1
line2
line3
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;
Attempt to change blob sub_type to text will FAIL with message:
SQL> alter table b_undefined_type alter b type blob sub_type text;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE B_UNDEFINED_TYPE failed
-Cannot change datatype for column B. Changing datatype is not supported for BLOB or ARRAY columns.
If this table ('b_undefined_type') has many dependencies then it will be difficult to drop it and recreate again with proper blob sub_type.
Please consider ability to change blob sub-type by ALTER TABLE ALTER <COLUMN> statement.
The text was updated successfully, but these errors were encountered:
Sub_type is equivalent to file extension and should be possible to change it. But i do not know if this can not have side effects when it is used in expression like e.g. substring and is used already in index? My concern is blob filter here..
Submitted by: @pavel-zotov
Suppose we have two tables, both with BLOB field.
First table was created with undefined blob sub-type, second - with blob that we know forecast that it will store only text data.
Let's create and add multi-line content to these tables:
set blob all;
commit;
recreate table b_undefined_type(b blob);
insert into b_undefined_type values(
'line1
line2
line3'
);
commit;
------------------------------------------
recreate table b_known_as_text(b blob sub_type text);
insert into b_known_as_text values(
'line1
line2
line3'
);
commit;
If we try to extract BLOBs without any casting then all will be fine.
But if we cast blob to varchar (with providing enough length to accomodate its content) then blob from "b_undefined_type" will be extracted with loosing CRLF characters:
=======
set list on;
set echo on;
select b from b_known_as_text;
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;
select b from b_undefined_type;
select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;
Output (compare data in lines [1] and [2]):
select b from b_known_as_text;
B 91:0
line1
line2
line3
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;
BLOB_TO_VARCHAR line1 <<<<<<<<<<<<<<<<<<<<<<< [ 1 ]
line2
line3
select b from b_undefined_type;
B 90:0
line1
line2
line3
select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;
BLOB_TO_VARCHAR line1line2line3 <<<<<<<<<<<<< [ 2 ]
=======
Attempt to change blob sub_type to text will FAIL with message:
SQL> alter table b_undefined_type alter b type blob sub_type text;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE B_UNDEFINED_TYPE failed
-Cannot change datatype for column B. Changing datatype is not supported for BLOB or ARRAY columns.
If this table ('b_undefined_type') has many dependencies then it will be difficult to drop it and recreate again with proper blob sub_type.
Please consider ability to change blob sub-type by ALTER TABLE ALTER <COLUMN> statement.
The text was updated successfully, but these errors were encountered: