How to convert LONG to VARCHAR2 in PL/SQL
I want to convert LONG to VARCHAR2 in PL/SQL on the fly.
Note that we are using AL32UTF8 characterset.
Below works only when view text is smaller than 8K.
declare
v_res varchar2(32767);
begin
select text
into v_res
from dba_views
where view_name='VIEW_SMALL';
end;
When the view text is larger than 8K I get error:
06502. 00000 - "PL/SQL: numeric or value error%s"
I know you can convert to VARCHAR2 with a temporary table with below command.
create table temp_user_views as select view_name, text_length, to_lob(text) text from dba_views;