Re: Traslating LONG RAWs to CLOBs..

A copy of this was sent to Peter & Mag <petermag_at_singnet.com.sg> (if that email address didn't require changing) On Fri, 06 Aug 1999 00:35:39 +0800, you wrote:

>Actually a complete example titled "Copying data from LOngs to LOBs" in
>Application Developer Guide 8.0.4, page 6-40 also provides a complete
>breakdown of steps and the sample PL/SQL needed to implement such
>features.
>

The above referenced example does what the original requestor said the docs already said was the way to do it (the plsql example shows dbms_lob.loadfromfile() against a long that was unloaded to the file system).

This shows a different way.

The example I provided shows how to do an in place (no flat file) copy of a long to a clob -- very different from the examples.

>Thomas Kyte wrote:
>>
>> 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
>>

[snip]

--
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 - 12:15:32 CDT