Will February 2016

Converting a Long to a varchar

I'm trying to convert a value of Long to a varchar so far I have approached it as convert to Long ->CLOB -> Varchar2:

INSERT into ALL_POSTAL_CODES (ZIP_CODE,LATITUDE,LONGITUDE,ISO_COUNTRY,CITY,STATE, TIME_ZONE)
Select  ZIP_CODE,
        LATITUDE,
        TO_CHAR(SUBSTR(TO_LOB(LONGITUDE))),
        'USA',
        CITY,
        STATE,
        TIME_ZONE
from POSTAL_CODES

;

However I am hitting some syntax error:

Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Answers


Will February 2016

I created a Temp table to get around this as there was no elegant solution

CREATE TABLE t1
  AS
  SELECT ZIP_CODE,
           to_lob(LONGITUDE) as LONGITUDE
    FROM POSTAL_CODES;

update ALL_POSTAL_CODES AP
Set AP.LONGITUDE = 
( select P.LONGITUDE
    from
    T1 P
    where 
    P.ZIP_CODE = AP.ZIP_CODE
     and
      rownum = 1 
    )
    where exists (
      select 
        null
      from 
        T1 P
      where 
        P.ZIP_CODE = AP.ZIP_CODE
      );

Drop table T1;

Post Status

Asked in February 2016
Viewed 3,317 times
Voted 9
Answered 1 times

Search




Leave an answer