DROP TABLE temp_unicode_table

CREATE TABLE temp_unicode_table ( 
	id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, 
	ts VARCHAR2(10 CHAR), 
	tx VARCHAR2(30 CHAR) 
)

TRUNCATE TABLE temp_unicode_table

INSERT INTO temp_unicode_table (ts, tx) VALUES ('1', '=Σ=')

INSERT INTO temp_unicode_table (ts, tx) VALUES ('1', UTL_RAW.CAST_TO_VARCHAR2('F09F988A'))

COMMIT

CREATE OR REPLACE 
FUNCTION temp_test_dbms_lob_error(text IN VARCHAR2) RETURN VARCHAR2 IS 
	c CLOB; 
	t VARCHAR2(32767); 
BEGIN 
	dbms_lob.createtemporary(lob_loc=>c, cache=>TRUE); 
	dbms_lob.writeappend(lob_loc=>c, amount=>LENGTH(text), buffer=>text); 
	t := dbms_lob.substr(lob_loc=>c); 
	dbms_lob.freetemporary(lob_loc=>c); 
	RETURN t; 
 
EXCEPTION 
	WHEN OTHERS THEN 
		IF (dbms_lob.istemporary(lob_loc=>c) = 1) THEN 
			dbms_lob.freetemporary(lob_loc=>c); 
		END IF; 
		RETURN dbms_utility.format_error_backtrace(); 
END; 

CREATE OR REPLACE 
FUNCTION temp_test_dbms_lob_success(text IN VARCHAR2) RETURN VARCHAR2 IS 
	c CLOB; 
	t VARCHAR2(32767); 
BEGIN 
	dbms_lob.createtemporary(lob_loc=>c, cache=>TRUE); 
	dbms_lob.writeappend(lob_loc=>c, amount=>dbms_lob.getlength(text), buffer=>text); 
	t := dbms_lob.substr(lob_loc=>c); 
	dbms_lob.freetemporary(lob_loc=>c); 
	RETURN t; 
 
EXCEPTION 
	WHEN OTHERS THEN 
		IF (dbms_lob.istemporary(lob_loc=>c) = 1) THEN 
			dbms_lob.freetemporary(lob_loc=>c); 
		END IF; 
		RETURN dbms_utility.format_error_backtrace(); 
END; 

SELECT 
	'temp_test_dbms_lob_error', 
	id, 
	ts, 
	tx, 
	LENGTHB(tx) "original_length_byte", 
	temp_test_dbms_lob_error(tx) "converted_text", 
	LENGTHB(temp_test_dbms_lob_error(tx)) "converted_length_byte", 
	CASE WHEN tx = temp_test_dbms_lob_error(tx) THEN 'SUCCESS' ELSE 'ERROR' END "status" 
FROM temp_unicode_table