Re: Traslating LONG RAWs to CLOBs..

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 05 Aug 1999 12:03:08 GMT
Message-ID: <37a97cbc.537472@newshost.us.oracle.com>

A copy of this was sent to ghubert_at_netpath.net (Gene Hubert) (if that email address didn't require changing) On Thu, 05 Aug 1999 04:00:25 GMT, you wrote:

>On Tue, 03 Aug 1999 18:05:28 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
>wrote:
>
>>If you really meant that you have a table with LONGs (not long raws) and want to
>>convert that into CLOBS, we can do that in 8.0 with plsql alone.
>>
>
>I just spent a fair chunk of time doing this. The docs seem to say
>specifically that you have to write them to flat files and then use
>dbms_lob.loadfromfile. If there is a better way I'd really like to
>know what it is. I tried a bunch of things with pl/sql and never got
>it to work.
>
>Gene Hubert
>SkillsPoint.com
>Morrisville, NC

for LONGs (not long raws) we can use dbms_sql to piecewise READ a long and dbms_lob.write to put that piece into a clob. we can read 32k of a long at a time and write it piecewise to the lob. Here is an example:

create or replace function long_to_lob( p_query in varchar2,

                                        p_clob in out clob )

return number
as

    l_cursor    integer default dbms_sql.open_cursor;
    l_n         number;
    l_long_val  varchar2(250);
    l_long_len  number;
    l_buflen    number := 250;
    l_curpos    number := 0;

begin

    dbms_sql.parse( l_cursor, p_query, dbms_sql.native );

    dbms_sql.define_column_long(l_cursor, 1);     l_n := dbms_sql.execute(l_cursor);

    if (dbms_sql.fetch_rows(l_cursor)>0)     then

        loop
            dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
                                        l_long_val, l_long_len );
            exit when l_long_len = 0;
            dbms_lob.write( p_clob, l_long_len, l_curpos+1, l_long_val);
            l_curpos := l_curpos + l_long_len;
      end loop;

   end if;
   dbms_sql.close_cursor(l_cursor);
   return l_curpos;
end long_to_lob;
/
show errors

That routine expects you to give is a query that fetches exactly 1 column and 1 row (eg: in the format "select LONG_COLUMN from T where primary_key = x")

it'll piecewise read the long (250 bytes at a time, you would want to INCREASE that to 32,000 or so -- use 250 to show it works a piece at a time in this example only)

Here is an example of it at work:

drop table demo1;
drop table demo2;

create table demo1( x long );
declare

    l_str long default rpad( '*', 32000, '*' ); begin

    insert into demo1 values ( l_str );     l_str := rpad( '*', 32123, '*' );
    insert into demo1 values ( l_str ); end;
/

create table demo2 ( x clob );

declare

    l_clob clob;
    l_bytes number;
begin

    for y in ( select rowid r from demo1 ) loop

        insert into demo2 values ( empty_clob() ) returning x into l_clob;
        l_bytes := long_to_lob( 'select x from demo1 where rowid = '''||
                                    y.r || '''',
                                l_clob );
        dbms_output.put_line( to_char(l_bytes) || ' copied...' );

    end loop;
end;
/
select dbms_lob.getlength( x ) from demo2; set echo off

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 05 1999 - 07:03:08 CDT