Error getting Oracle dbms

kirby900 has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, oh wise and venerable monks. Please help me overcome errors generated when attempting to retrieve longer lines of dbms_output from Oracle. Below is a brief piece of code that generates an error in my environment, which is:
SunOS aztec 5.10 Generic_142900-02 sun4u sparc SUNW,Netra-T12
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
DBI ver 1.611
DBD::Oracle ver 1.24

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $sql = q{
declare
   v_ddl varchar2(4000);
begin
   v_ddl := dbms_metadata.get_ddl('VIEW','USER_TAB_COLUMNS','SYS');
   dbms_output.put_line(v_ddl);
end;
};

my $dbh = DBI->connect('dbi:Oracle:mydb','myuid','mypwd');
$dbh->debug(6);
$dbh->{LongReadLen} = 32000;
$dbh->{LongTruncOk} = 1;
$dbh->func(1000000,'dbms_output_enable');
$dbh->do( $sql );
my @dbms_lines = $dbh->func('dbms_output_get');
print "Current DBI error string: ", 
  (defined DBI->errstr() ? DBI->errstr() : 'none'), "\n" ;
print join("\n", @dbms_lines), "\n";
$dbh->disconnect();
[download]

I figured out by using substr() that errors are generated anytime the dbms_output line is longer than 404 characters. Here is the debug output:

    <- do= 1 at test_dbms_output_get.pl line 22
    -> dbms_output_get for DBD::Oracle::db (DBI::db=HASH(0x29ca0c)~0x2
+9c9a0)
1   -> prepare_cached in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(
+0x29c9a0)~INNER 'begin dbms_output.get_line(:l, :s); end;')
2   -> prepare for DBD::Oracle::db (DBI::db=HASH(0x29c9a0)~INNER 'begi
+n dbms_output.get_line(:l, :s); end;' undef)
    New 'DBI::st' (for DBD::Oracle::st, parent=DBI::db=HASH(0x29c9a0),
+ id=undef)
    dbih_setup_handle(DBI::st=HASH(0x29cd30)=>DBI::st=HASH(0x29cd24), 
+DBD::Oracle::st, 3dc890, Null!)
    dbih_make_com(DBI::db=HASH(0x29c9a0), 2a27a8, DBD::Oracle::st, 304
+, 0) thr#0
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), Err, DBI::db=HASH(0x29c9
+a0)) SCALAR(0x1b8bfc) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), State, DBI::db=HASH(0x29
+c9a0)) SCALAR(0x1b8c5c) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), Errstr, DBI::db=HASH(0x2
+9c9a0)) SCALAR(0x1b8c2c) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), TraceLevel, DBI::db=HASH
+(0x29c9a0)) 6 (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), FetchHashKeyName, DBI::d
+b=HASH(0x29c9a0)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), HandleSetErr, DBI::db=HA
+SH(0x29c9a0)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), HandleError, DBI::db=HAS
+H(0x29c9a0)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), ReadOnly, DBI::db=HASH(0
+x29c9a0)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x29cd24), Profile, DBI::db=HASH(0x
+29c9a0)) undef (not defined)
        dbd_preparse scanned 2 distinct placeholders
        OCIHandleAlloc(33ec48,3ded40,OCI_HTYPE_STMT,0,0)=SUCCESS
        OCIStmtPrepare(35e398,357088,'begin dbms_output.get_line(:l, :
+s); end;',40,1,0)=SUCCESS
        OCIAttrGet(35e398,OCI_HTYPE_STMT,3ded4c,0,OCI_ATTR_STMT_TYPE,3
+57088)=SUCCESS
        dbd_st_prepare'd sql BEGIN (pl1, auto_lob1, check_sql1)
        dbd_describe skipped for BEGIN
2   <- prepare= DBI::st=HASH(0x29cd30) at /export/home/ip00955/perl/li
+b/perl5/site_perl/5.8.8/sun4-solaris/DBI.pm line 1686
1   <- prepare_cached= DBI::st=HASH(0x29cd30) at /export/home/ip00955/
+perl/lib/perl5/site_perl/5.8.8/sun4-solaris/DBD/Oracle.pm line 838 vi
+a  at test_dbms_output_get.pl line 24
    -> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x29cd30)~0x
+29cd24 ':l' SCALAR(0x28bc84) 400 HASH(0x3dc830))
dbd_bind_ph(): bind :l <== undef (type 0 (DEFAULT (varchar)), inout 0x
+28bc84, maxlen 400, attribs: HASH(0x3dc830))
dbd_rebind_ph() (1): rebinding :l as NULL (not-utf8, ftype 1 (VARCHAR)
+, csid 0, csform 0(0), inout 1)
dbd_rebind_ph_char() (1): bind :l <== undef (NULL, size 0/0/400, ptype
+ 4(VARCHAR), otype 1 , inout)
dbd_rebind_ph_char() (2): bind :l <== '' (size 0/404, otype 1(VARCHAR)
+, indp -1, at_exec 1)
          bind :l as ftype 1 (VARCHAR)
        OCIBindByName(35e398,3defbc,357088,":l",placeh_len=2,value_p=3
+df098,value_sz=404,dty=1,indp=3defd4,alenp=0,rcodep=3defcc,maxarr_len
+=0,curelep=0 (*=0),mode=DATA_AT_EXEC,2)=SUCCESS
        OCIBindDynamic(35f5e8,357088,3def98,fefd32d4,3def98,fefd3570)=
+SUCCESS
        OCIAttrGet(35f5e8,OCI_HTYPE_BIND,3defa8,0,31,357088)=SUCCESS
dbd_rebind_ph(): bind :l <== undef (inout, not-utf8, csid 1->0->1, fty
+pe 1 (VARCHAR), csform 0(0)->0(0), maxlen 404, maxdata_size 0)
        OCIAttrSet(35f5e8,OCI_HTYPE_BIND, ffbfec34,0,Attr=31,357088)=S
+UCCESS
    <- bind_param_inout= 1 at /export/home/ip00955/perl/lib/perl5/site
+_perl/5.8.8/sun4-solaris/DBD/Oracle.pm line 842 via  at test_dbms_out
+put_get.pl line 24
    -> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x29cd30)~0x
+29cd24 ':s' SCALAR(0x28bc9c) 20 HASH(0x3dc830))
dbd_bind_ph(): bind :s <== undef (type 0 (DEFAULT (varchar)), inout 0x
+28bc9c, maxlen 20, attribs: HASH(0x3dc830))
dbd_rebind_ph() (1): rebinding :s as NULL (not-utf8, ftype 1 (VARCHAR)
+, csid 0, csform 0(0), inout 1)
dbd_rebind_ph_char() (1): bind :s <== undef (NULL, size 0/0/20, ptype 
+4(VARCHAR), otype 1 , inout)
dbd_rebind_ph_char() (2): bind :s <== '' (size 0/32, otype 1(VARCHAR),
+ indp -1, at_exec 1)
          bind :s as ftype 1 (VARCHAR)
        OCIBindByName(35e398,3df03c,357088,":s",placeh_len=2,value_p=1
+c92f0,value_sz=32,dty=1,indp=3df054,alenp=0,rcodep=3df04c,maxarr_len=
+0,curelep=0 (*=0),mode=DATA_AT_EXEC,2)=SUCCESS
        OCIBindDynamic(35f4b0,357088,3df018,fefd32d4,3df018,fefd3570)=
+SUCCESS
        OCIAttrGet(35f4b0,OCI_HTYPE_BIND,3df028,0,31,357088)=SUCCESS
dbd_rebind_ph(): bind :s <== undef (inout, not-utf8, csid 1->0->1, fty
+pe 1 (VARCHAR), csform 0(0)->0(0), maxlen 32, maxdata_size 0)
        OCIAttrSet(35f4b0,OCI_HTYPE_BIND, ffbfec34,0,Attr=31,357088)=S
+UCCESS
    <- bind_param_inout= 1 at /export/home/ip00955/perl/lib/perl5/site
+_perl/5.8.8/sun4-solaris/DBD/Oracle.pm line 843 via  at test_dbms_out
+put_get.pl line 24
    -> execute for DBD::Oracle::st (DBI::st=HASH(0x29cd30)~0x29cd24)
   dbd_st_execute BEGIN (out2, lob0)...
          with :s = '' (len 0(0)/32, indp -1, otype 1, ptype 6)
          with :l = '' (len 0(0)/404, indp -1, otype 1, ptype 6)
Statement Execute Mode is 32 (COMMIT_ON_SUCCESS)
                in  ':l' [0,0]: len  0, ind -1, value=undef
                in  ':s' [0,0]: len  0, ind -1, value=undef
        OCIStmtExecute(357014,35e398,357088,1,0,0,0,mode=COMMIT_ON_SUC
+CESS,32)=ERROR
        OCIAttrGet(35e398,OCI_HTYPE_STMT,ffbfebd6,0,OCI_ATTR_PARSE_ERR
+OR_OFFSET,357088)=SUCCESS
        OCIErrorGet(357088,1,"<NULL>",ffbfe7cc,"ORA-06502: PL/SQL: num
+eric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_OUTPUT", line 148
ORA-06512: at line 1
",1024,2)=SUCCESS
        OCIErrorGet after OCIStmtExecute (er1:ok): -1, 6502: ORA-06502
+: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_OUTPUT", line 148
ORA-06512: at line 1
[download]

The output doesn't make a lot of sense to me, but I trust that one of the monks will come through for me!
Cheers,
Dean

Comment on Error getting Oracle dbms_output from DBI Select or Download Code

Replies are listed 'Best First'.
Re: Error getting Oracle dbms_output from DBI by runrig (Abbot) on Jun 16, 2010 at 21:15 UTC
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; } [download] You could rewrite this function or write a similar function yourself and give yourself a higher limit.	[reply] [d/l]
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.	[reply]
Re^3: Error getting Oracle dbms_output from DBI by kirby900 (Initiate) on Jun 17, 2010 at 19:01 UTC
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; } [download] Once again, thanks for the quick and informed assistance!	[reply] [d/l]
Re: Error getting Oracle dbms_output from DBI by mje (Curate) on Jun 17, 2010 at 08:25 UTC
I see you've got an answer but just thought I'd warn you about a possible problem you may hit with dbms_output. It works fine so long as your Perl keeps reading it but if something happens in your Perl to stop reading the output (or not read it quickly enough) and it hits the buffer limit you've set your pl/sql will fail every time dbms_output is called. For this reason I usually write debug out to a file from Oracle as that only fails when max file size is reached.	[reply]