Re: How to convert LONG to VARCHAR2

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 13 Apr 2013 10:53:03 +0400
Message-ID: <CAOVevU6womRquuQKoUROs2K=ZKV9AAknZzOnT5husrVcqfv73g_at_mail.gmail.com>

Eriovaldo, you can do it without pl/sql with TO_LOB function. For example:

    insert into teste
    select

       table_name
     , column_name
     , to_lob(data_default)

    from user_tab_columns;

or

    create table teste as
    select

       table_name
     , column_name
     , to_lob(data_default) as data_defaults

    from user_tab_columns;

On Sat, Apr 13, 2013 at 7:16 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com>wrote:

> Hi People.
>
> Thanks a lot for attention and solutions...
> I got sucess doing it:
>
> 1.) create a table with datatype varcha2 like this:
>
> create table teste
> (table_name VARCHAR2(30),
> COLUMN_NAME VARCHAR2(30),
> DATA_DEFAULT VARCHAR2(4000));
>
> BEGIN
> for r in (select table_name , column_name , data_default from
> user_tab_columns
> where table_name = 'THE_TABLE_NAME' and column_name = 'THE_COLUMN_NAME')
> loop
> INSERT INTO teste (table_name, column_name, data_default)
> values (r.table_name, r.column_name, r.data_default);
> end loop;
> END;
> /
>
> Thanks and Regards
> Eriovaldo
>
>
>
>
> On Fri, Apr 12, 2013 at 5:05 PM, Sayan Malakshinov <xt.and.r_at_gmail.com>wrote:
>
>> Just one note: dbms_xmlgen.getXMLtype truncates "LONG" columns to 4000
>> bytes, although it works fine with clob
>>
>>
>> --
>> Best regards,
>> Sayan Malakshinov
>> Senior performance tuning engineer
>> PSBank
>> http://orasql.org
>>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Apr 13 2013 - 08:53:03 CEST