Re: inserting plain text into LOB in Oracle

You very well can use DBMS_LOB for this. Something like this will do:

declare
 b BLOB;
begin
 insert into mytable (id, lob_column)
  values(1, empty_blob())
 returning lob_column into b;
 dbms_lob.writeAppend(b, <chunk length>,

           utl_raw.cast_to_raw('whatever'));  update mytable
  set lob_column = b
  where id = 1;
 commit;
end;

You can also use temporary LOBs - in this case you will create a temporary BLOB using DBMS_LOB.createTemporary(), populate it using write() or writeAppend(), then insert it, and dispose it with freeTemporary() or trim() and reuse it for the next row.

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Guy" <ni4ni_at_hotmail.com> wrote in message news:d2eb532b.0303312354.548560de_at_posting.google.com...


> Hello all

> 

> I need to insert rows into an Oracle table which contains a BLOB field

> (Oracle 8.1.7 on win2k).

> The data I want to insert is just text, and its not very large at this

> time.

> This is gonna be part of an installation program so I wont be able to

> access any files on the disk.

> Given those conditions, and reading older posts I realize that I

> cannot use dbms_lob, since It only helps accessing disk files.

> 

> I will be using PL/SQL. Is there a way to just insert constant text

> into a BLOB field (like "insert into table values (1,'hello world')")

> ?

> 

> Thanks

> 

> Guy