PL/SQL (MOSC)

MOSC Banner

How to convert LONG to VARCHAR2 in PL/SQL

in PL/SQL (MOSC) 5 commentsAnswered

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;

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center