Re: Oracle BLOB

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 25 Aug 1999 11:22:22 GMT
Message-ID: <37c3d000.419903@newshost.us.oracle.com>

A copy of this was sent to "frederic servais" <frederic.servais_at_advalvas.be> (if that email address didn't require changing) On Wed, 25 Aug 1999 09:38:49 +0200, you wrote:

>I'm trying to fill in a Blob with varchar2 but allways get the following
>error (in Oracle 8.05):
>
>SQLWKS> declare
> 2> ablob blob;
> 3> astrid varchar2(14);
> 4> len binary_integer;
> 5> poss integer := 1;
> 6> BEGIN
> 7> len := length(astrid);
> 8> dbms_lob.write(ablob, len, 1, astrid);
> 9> END;
> 10> /
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "SYS.DBMS_LOB", line 518
>ORA-06512: at line 8
>
>any idea
>
>

In Oracle8.0, a BLOB must live in a table -- you would typically insert an EMPTY_BLOB() into a table and then write to that blob. For example:

tkyte_at_8.0> create table t ( x blob );
Table created.

tkyte_at_8.0> declare

  2      l_blob    blob;
  3      l_str    varchar2(255) default 'How Now Brown Cow';
  4  begin
  5      insert into t values ( empty_blob() ) returning x into l_blob;
  6  
  6      dbms_lob.write( l_blob, length(l_str), 1,
  7                      utl_raw.cast_to_raw(l_str) );
  8  
  8      commit;

  9 end;
 10 /

PL/SQL procedure successfully completed.

tkyte_at_8.0> select utl_raw.cast_to_varchar2( dbms_lob.substr( x, 200, 1 ) )   2 from t
  3 /

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(X,200,1))

---

How Now Brown Cow

Notice the use of utl_raw to convert (cast) the varchar2 into a RAW and a RAW back into a varchar2. You need this if you are stuffing strings into blobs and vice-versa (a clob would be a more natural fit for a string actually and would remove the need for utl_raw). UTL_RAW may not be installed on your database, to install

• cd $oracle_home/rdbms/admin
• ls *raw* (you'll find a spec in a .sql file and a body in a .plb file)
• svrmgrl
• connect internal
• run the spec
• run the body

do this as INTERNAL or SYS *ONLY* it will not work as any other user.

In Oracle8i, release 8.1 a blob does not have to live in a table -- it can be a temporary blob. for example:

tkyte_at_8i> declare

  2      l_blob    blob;
  3      l_str    varchar2(255) default 'How Now Brown Cow';
  4  begin
  5      dbms_lob.createtemporary( l_blob, TRUE );
  6  
  7      dbms_lob.write( l_blob, length(l_str), 1,
  8                      utl_raw.cast_to_raw(l_str) );
  9  
 10      dbms_output.put_line(
 11          utl_raw.cast_to_varchar2( dbms_lob.substr(l_blob,200,1) ) );

 12 end;
 13 /
How Now Brown Cow

PL/SQL procedure successfully completed.

--
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 Wed Aug 25 1999 - 06:22:22 CDT