comment on

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

In reply to Error getting Oracle dbms_output from DBI by kirby900

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.