Oracle Database blob type conversion to varchar2

It is said that data can be directly converted if there is no data, and can be converted if you have tried it, but it is generally only modified when the data is ready. If you have trouble, you cannot directly modify it. You can only use functions to solve this problem.

It is said that data can be directly converted if there is no data, and can be converted if you have tried it, but it is generally only modified when the data is ready. If you have trouble, you cannot directly modify it. You can only use functions to solve this problem.

It is said that data can be directly converted if there is no data, and can be converted if you have tried it, but it is generally only modified when the data is ready. If you have trouble, you cannot directly modify it. You can only use functions to solve this problem;

The general idea is: create A temporary field B → place field A to be converted into A temporary field (Use Function Conversion) → Delete A → change B name to

Create Conversion Function

Create or replace FUNCTION blob_to_varchar (blob_in BLOB)
RETURN VARCHAR2
IS
V_varchar VARCHAR2 (2000 );
V_start PLS_INTEGER: = 1;
Vanderbilt buffer PLS_INTEGER: = 2000;

BEGIN
If DBMS_LOB.GETLENGTH (blob_in) is null then
Return empty_clob ();
End if;
DBMS_OUTPUT.put_line ('test: '| CEIL (DBMS_LOB.GETLENGTH (blob_in )));
FOR I IN 1 .. CEIL (DBMS_LOB.GETLENGTH (blob_in)/v_buffer)
LOOP
V_varchar: = UTL_RAW.CAST_TO_VARCHAR2 (utl_raw.convert (DBMS_LOB.SUBSTR (blob_in, v_buffer, v_start), 'American _ the netherlands. utf8', 'American _ the netherlands. utf8 '));
V_start: = v_start + v_buffer;
End loop;
RETURN v_varchar;
End blob_to_varchar;

Start modifying table data:

Alter table requestion ADD INTRODUCE_TEMP VARCHAR2 (2000 );
UPDATE requestion SET INTRODUCE_TEMP = blob_to_varchar (INTRODUCE );
Alter table requestion drop column introduce;
Alter table requestion rename column INTRODUCE_TEMP to introduce;
Drop FUNCTION blob_to_varchar;
Garbled characters may occur during this process. You need to adjust the encoding, that is, the red part. We recommend that you finish the green part first to check whether the encoding is correct and then execute the blue part of the code.

Of course, if the blob function to be converted to varchar still needs to be deleted, do not delete it.