Re: Convert to BLOB?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 28 Dec 1999 08:32:26 -0500
Message-ID: <2keh6skje31qlbo9n7tr5hgn9sgi4cl7rl@4ax.com>

A copy of this was sent to Sandijs Jercums <sandijs_at_it.lv> (if that email address didn't require changing) On Tue, 28 Dec 1999 11:39:43 GMT, you wrote:

>Hi!
>
>Can anyone help me? I need to know, how to convert varchar, long, long raw
>and CLOB to BLOB using PL/SQL (Oracle8 Enterprise Edition Release
>8.0.5.1.0).
>
>Thanks,
>
>Sandijs

In Oracle8i, release 8.1, you can use the builtin TO_LOB() function to do this.

In 8.0.5 you can do it using dbms_lob and a little procedural code for all varchar's, longs (of any size) and LONG RAWS that are 32k or less.

Here is an example that converts any size long into a CLOB:

create or replace procedure long2clob( p_query in varchar2,
                                       p_bindvar in varchar2,
                                       p_clob  in out clob )

as

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

begin

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

    dbms_sql.bind_variable( l_cursor, ':bv', p_bindvar );     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);
exception

   when others then

      if dbms_sql.is_open(l_cursor) then
         dbms_sql.close_cursor(l_cursor);
      end if;
      raise;

end long2clob;
/

drop table test;
create table test ( y clob );

declare

    l_clob clob;
begin

    for x in ( select view_name from user_views where rownum < 25 )     loop

        insert into test values ( empty_clob() ) returning y into l_clob;
        long2clob( 'select text from user_views where view_name = :bv',
                    x.view_name,
                    l_clob );

    end loop;
    commit;
end;
/
select dbms_lob.getlength(y) from test
/

Here is an example that converts a LONG RAW that is 32k or less into a BLOB

declare

    l_blob blob;
begin

    for x in ( select LONG_RAW_COLUMN from T )     loop

        insert into test values ( empty_blob() ) returning blob_column
        into l_blob;
        dbms_lob.write( l_blob, utl_raw.length(x.long_raw_column), 1, l_blob );

    end loop;
    commit;
end;
/

the varchar2 example would be very similar, or it can be simply:

tkyte_at_8.0> create table t ( x clob );

Table created.

tkyte_at_8.0> insert into t select username from all_users where rownum < 5;

4 rows created.

tkyte_at_8.0> select * from t;

X

---

SYS
SYSTEM
....

(a simple insert into will do for varchars)

--
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 Tue Dec 28 1999 - 07:32:26 CST