in reply to Error getting Oracle dbms_output from DBI

There used to be a put_line limit of 255 in Oracle, now the limit is 32k or so. There is, however, a limit of 400 in the dbms_output_get function in DBD/Oracle.pm:
sub dbms_output_get { my $dbh = shift; my $sth = $dbh->prepare_cached("begin dbms_output.get_line(:l, + :s); end;") or return; my ($line, $status, @lines); # line can be greater that 255 (e.g. 7 byte date is expanded o +n output) $sth->bind_param_inout(':l', \$line, 400, { ora_type => 1 }); $sth->bind_param_inout(':s', \$status, 20, { ora_type => 1 }); if (!wantarray) { $sth->execute or return undef; return $line if $status eq '0'; return undef; } push @lines, $line while($sth->execute && $status eq '0'); return @lines; }
You could rewrite this function or write a similar function yourself and give yourself a higher limit.

Replies are listed 'Best First'.
Re^2: Error getting Oracle dbms_output from DBI
by kirby900 (Initiate) on Jun 17, 2010 at 01:29 UTC
    Ah, so that's where the limitation lies! Thank you for identifying the source of the problem and for the possible remedies.
      FYI, I opted to write my own version of the DBD::Oracle procedure so that I can successfully extract dbms_output lines up to the maximum length:
      sub get_dbms_output { my $dbh = shift; my $sth = $dbh->prepare_cached('begin dbms_output.get_line(:line, : +status); end;') or return; my ($line, $status, @lines); # Since 10g r2, Oracle has supported line sizes up to 32767. $sth->bind_param_inout(':line', \$line, 32767, { ora_type => 1 }); $sth->bind_param_inout(':status', \$status, 20, { ora_type => 1 }); if (!wantarray) { $sth->execute or return undef; return $line if $status eq '0'; return undef; } push @lines, $line while($sth->execute && $status eq '0'); return @lines; }

      Once again, thanks for the quick and informed assistance!