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.