Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Traslating LONG RAWs to CLOBs..

Re: Traslating LONG RAWs to CLOBs..

From: Peter & Mag <petermag_at_singnet.com.sg>
Date: Fri, 06 Aug 1999 00:35:39 +0800
Message-ID: <37A9BD5B.382A2A14@singnet.com.sg>


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.

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
>
> 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

--

      \\\|///          Peter Teoh
    \\  - -  //        Oracle Certified Professional
     (  @ @  )
__oOOo-(_)-oOOo____________________________________
|                                           _____  |
|                                          (~._.~) |
|Email:petermag_at_singnet.com.sg             _{ Y }_ |
|Phone:97813086(HP)                       ()_~~~_()|
|Pager:96047971                            (_)-(_) |
|http://web.singnet.com.sg/~petermag/              |
|__________________________________________________|
Received on Thu Aug 05 1999 - 11:35:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US