Convert LONG column to VARCHAR2 in ORACLE - problem related to LONG column value size relative to VARCHAR2

Question

CREATE OR REPLACE FUNCTION PRC_HAM_CONVT_LONG_VARCHAR2
( p_owner VARCHAR2, p_tabela VARCHAR2, p_coluna VARCHAR2, p_rowid UROWID    )RETURN VARCHAR2 IS

     v_cursor INTEGER; --cursor
     v_length INTEGER; --tamanho do long
     v_tamanho INTEGER; --tamanho do que foi retornado
     v_sql VARCHAR(2000); --stetament
     v_max VARCHAR2(32760); --variavel varchar2 com tamanho maximo do long
     v_clob CLOB; --variavel do tipo clob
     v_long LONG; --variavel do tipo long
     v_syscur SYS_REFCURSOR;

BEGIN
v_cursor := dbms_sql.open_cursor;

v_sql := 'SELECT '||p_coluna||' FROM '||p_owner||'.'||p_tabela||' WHERE ROWID = :row_id';

OPEN v_syscur FOR v_sql USING p_rowid;
FETCH v_syscur INTO v_long;
v_length := LENGTH(v_long);
CLOSE v_syscur;    
dbms_sql.parse(v_cursor,v_sql,dbms_sql.NATIVE);
dbms_sql.bind_variable(v_cursor,'row_id',p_rowid);
dbms_sql.define_column_long(v_cursor,1);    

IF(dbms_sql.execute_and_fetch(v_cursor) = 1) THEN
   dbms_sql.column_value_long( v_cursor, 1, v_length, 0, v_max, v_tamanho );
END IF;  

dbms_lob.createtemporary(v_clob, FALSE, dbms_lob.CALL);
v_clob := v_max;

RETURN v_max; --OU ENTAO V_MAX

END;

With this function above Oracle I can convert a LONG column normally to VARCHAR2, however, there are cases where the value of the LONG column exceeds the allowed range of VARCHAR2 which inevitably causes an error, how could I do to solve this problem? That is, make all the value of the LONG column be converted to VARCHAR2?

Answer 1

tl;dr
I don’t think it’s possible.

---

The question, basis is, why does it need to be varchar2? This type stores only 4000 bytes¹, as long as the LONGat maximum 2GB, logically the conversion would lose data as it does not "fit" the amount of information in a varchar2.

I suggest that:

• Work with this type of data in the application you are developing;
• Cut the number of characters in the limit of the varchar2;
• Some creative technical procedure. gambiarra . (unsaved)

^{1 Maximum size: 4000 bytes or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED. Source: Oracle - Datatype Limits 12c}