Re: Convert LONG to VARCHAR2 ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 29 Oct 1998 20:20:04 GMT
Message-ID: <3649cd89.111769726@192.86.155.100>

A copy of this was sent to "Petre Alexandrescu" <Petre.Alexandrescu_at_mci.com> (if that email address didn't require changing) On Thu, 29 Oct 1998 15:11:53 -0500, you wrote:

>Who knows a function that convert long to varchar2?
>
>I need this because I want to do the following SQL statement to work:
>
>Select C1 from T1
>union
>Select C2 from T2
>
>Where C1, C2 are of LONG data type.
>
>Thanks,
>
>Petre
>
>

Can you use UNION ALL as opposed to UNION. The problem is that "a union b" is really "a plus (b minus a)" -- it must remove from B all rows already found in A and then add them together. With the long column, it cannot do the comparision of rows.

By using UNION ALL, "a union all b" is equivalent to "a concatenate b" or "a plus b" and it can do that:

SVRMGR> create or replace view v1
     2> as
     3> select view_name, text from all_views where rownum = 1
     4> union 
     5> select view_name, text from all_views where rownum = 1
     6> /
select view_name, text from all_views where rownum = 1
                  *

ORA-00997: illegal use of LONG datatype

SVRMGR> create or replace view v1
     2> as
     3> select view_name, text from all_views where rownum = 1
     4> union all
     5> select view_name, text from all_views where rownum = 1
     6> /

Statement processed

SVRMGR> select * from v1;

VIEW_NAME                      TEXT

------------------------------
--------------------------------------------------------------------------------
ALL_ADMINCATLOG                select

  id,
  source,
  userid,
  timestamp,
  role,
  request,
  oname,
  snam

ALL_ADMINCATLOG                select

  id,
  source,
  userid,
  timestamp,
  role,
  request,
  oname,
  snam
2 rows selected.
SVRMGR> Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Oct 29 1998 - 14:20:04 CST