About Oracle and BLOB

[Andrea LUCIANO]

I saw around the way suggested to write a blob:
allocating a big buffer and binding it in sql prepared statements with =
attributes 'ora_type' =3D> 24.
I think a better way is not to allocate that big buffer at all but using =
a script (that can be iterated over a file stream reading loop),=20
through 2 steps:

step 1) the empty blob:

INSERT INTO TEST VALUES (1, EMPTY_BLOB());

step2) appending data into blob field:

#!/usr/local/bin/perl
use Oraperl;

my $sql_update_record =3D qq{ DECLARE
   lob_dest    BLOB;
   len         BINARY_INTEGER:=3D:1;
BEGIN
   SELECT text INTO lob_dest FROM test WHERE key =3D 1 for update;
   DBMS_LOB.OPEN(lob_dest, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.WRITEAPPEND( lob_dest, len, :2 );
   DBMS_LOB.CLOSE( lob_dest);
   COMMIT;
END; };

my $buf=3D"ciao a tutti";

my $conn=3D&ora_login('mydb', 'myuser', 'mypassword');

my $stmt=3D $conn->prepare($sql_update_record);

$stmt->bind_param( 1, length($buf) );
$stmt->bind_param( 2, $buf, { 'ora_type' =3D> 24 } );

$stmt->execute || warn "ora_do: $ora_errno: $ora_errstr\n";
$stmt->finish;

&ora_logoff($conn);