• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2521
  • Last Modified:

Oracle convert LONG data type to varchar2.


I need to be able to convert the contents of a LONG field type to text in an SQL select statement.  I have tried converting the contents to XML first, but the query fails if there are any invalid XML characters such as "&" are in the field.

putting it simply, i want to be able to use a statement like

select field1, field2, field3, to_char(long_field) from table_name

Many thanks
1 Solution
slightwv (䄆 Netminder) Commented:
Tom Kyte posted a user defined function that will do this:

The problem with going to varchar2 is you have a limit of 4000 characters.

If you have more than that, you'll need a user defined function that returns a CLOB.  I'm sure those are out there as well.
dbscpmAuthor Commented:
The response was exactly what I needed.  Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now