Hi, I our site we encapsulates all the database access using plsql packages. This works fine until we got to using perl and clob/blob-s. There does not seem to be an option to call a stored procedure from perl with clob as inout bind param and then write to it from perl (lob_write or so). My code in the database looks like:

CREATE TABLE LOB_PERL_TEST ( 
  ID   NUMBER, 
  CLB  CLOB);

procedure lob_perl_testp(id in number, clb in out clob) is
begin
    insert into lob_perl_test(id, clb)
    values(id, empty_clob())
    returning clb
    into clb;
end;
/
[download]

The perl code:

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

$ENV{"ORACLE_HOME"} = "E:\Oracle\Ora81";

connectToDB("orcl","scott","tiger");
insertElement(10, "clob");
 
#######################

sub insertElement {
  my($id,$clob)=@_;
  my $st = qq{
   begin
    lob_perl_testp( $id, :vclob);
  end;};

  print "$st\n";
  $sth = $db->prepare($st);
  $sth->bind_param_inout(":vclob", \$vclob , {ora_type => ORA_CLOB},0 
+);
  $rv = $sth->execute;
}


sub connectToDB {
  my($dbname,$user,$passwd) = @_;
  print "connectToDB $user/$passwd\n";
  $db = DBI->connect("dbi:Oracle:" . $dbname, $user, $passwd)
     || die "Unable to connect to $dbname: $DBI::errstr\n";
  print "connected   $user/$passwd\n";
}
[download]

Any ideas ? Hadar Paiss hadar@net-post.com