Re: long to clob?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 29 Oct 1999 13:52:18 -0400
Message-ID: <g94ZOPtvXGi19B3WhpnbDtEWrqgh@4ax.com>

A copy of this was sent to "vtsien" <vtsien_at_lucent.com> (if that email address didn't require changing) On Fri, 29 Oct 1999 11:59:37 -0500, you wrote:

>Does anybody know how to convert a long data type column into clob? The
>data inside long contains many catrige returns(as a result of doc cut and
>paste). Therefore you cannot dump it out as text file and loaded in as
>clob.
>
>Vic
>

In Oracle8i, release 8.1, there is a function TO_LOB() you can use, eg:

insert into clob_table selct to_lob( long_column ) from t;

will convert them.

In 8.0, you can convert a long into clob via the following method:

tkyte_at_8.0> create or replace procedure long2clob( p_query in varchar2,

  2                                         p_bindvar in varchar2,
  3                                         p_clob  in out clob )
  4  as
  5      l_cursor    integer default dbms_sql.open_cursor;
  6      l_long_val  varchar2(20);
  7      l_long_len  number;
  8      l_buflen    number := 20;
  9      l_curpos    number := 0;
 10      l_n            number;
 11  begin
 12      dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
 13  
 13      dbms_sql.bind_variable( l_cursor, ':bv', p_bindvar );
 14      dbms_sql.define_column_long(l_cursor, 1);
 15      l_n := dbms_sql.execute(l_cursor);
 16  
 16      if (dbms_sql.fetch_rows(l_cursor)>0)
 17      then
 18          loop
 19              dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
 20                                          l_long_val, l_long_len );
 21              exit when l_long_len = 0;
 22              dbms_lob.write( p_clob, l_long_len, l_curpos+1, l_long_val );
 23              l_curpos := l_curpos + l_long_len;
 24        end loop;
 25     end if;
 26     dbms_sql.close_cursor(l_cursor);
 27  exception
 28     when others then
 29        if dbms_sql.is_open(l_cursor) then
 30           dbms_sql.close_cursor(l_cursor);
 31        end if;
 32        raise;

 33 end long2clob;
 34 /

Procedure created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> drop table test;

Table dropped.

tkyte_at_8.0> create table test ( y clob );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> declare
  2 l_clob clob;
  3 begin

  4      for x in ( select view_name from user_views where rownum < 25 )
  5      loop
  6          insert into test values ( empty_clob() ) returning y into l_clob;
  7          long2clob( 'select text from user_views where view_name = :bv',
  8                      x.view_name,
  9                      l_clob );
 10      end loop;
 11      commit;

 12 end;
 13 /

PL/SQL procedure successfully completed.

tkyte_at_8.0> select dbms_lob.getlength(y) from test   2 /

DBMS_LOB.GETLENGTH(Y)

---

                   86
                  190
                   46
                   36
                   45
                   68

6 rows selected.

Note: i used a big 20 byte buffer to test the >1 piecewise fetch. please use a larger buffer like 32k....

--
See http://osi.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 Fri Oct 29 1999 - 12:52:18 CDT