Re: ORA-01406 from a perl script using DBI package

Hello!

Your script works just fine for me, with a small change (see
below).

On Mon, 17 Dec 2007, Grech, Raymond wrote:

> Can anyone help me resolve this problem?
>
> > I am trying to print DDL statements using the DBI package in a perl script.
> >
> > I am getting this error:
> >
> > DBConnect Successful: dbi:Oracle:DBINST
> > DBD::Oracle::st fetchrow_arrayref failed: ORA-01406: fetched column value was truncated (DBD: ORA-01406 error on field 1 of 1, ora_type 112) at test4.pl line 32.
> >
> > All the references I checked for this type of error adviced to include the following stmts, which I did:
> >
> > $dbh->{LongReadLen} = 9000000; <= to increase the buffer size
> > $dbh->{LongTruncOk} = 0; <= to ignore error and truncate output if buffer is still not large enough
> >
> > However I still get the same error, with or without the above settings.
> >
> > 1) Any idea on how to change the script to fix issue?
> > 2) Is there a way to change the format for the output created by dbms_metadata.get_ddl to resemble out some other commercial database tools?
> >
> >
> > The full perl scipt is listed below.
> >
> >
> > use strict;
> > use DBI;
> > use File::Path ;
> >
> >
> > my $connection = "dbi:Oracle:DBINST";
> > my $user = "SCHEMA_NAME";
> > my $password = "***************";
> > my $dbh;
> >
> > $dbh = DBI->connect($connection, $user, $password, { RaiseError => 1, AutoCommit => 0 });
> >
> > if (defined $dbh) {
> > print " DBConnect Successful: $connection\n";
> > }
> > else {
> > print " DBConnect Failure: $connection : $!\n";
> > exit;
> > }
> >
> > $dbh->{LongReadLen} = 9000000;
> > $dbh->{LongTruncOk} = 0;
> >
> > my $sql_stmt = "select dbms_metadata.get_ddl('PACKAGE','PKG_ETL','$user') from dual";
> > my $sth = $dbh->prepare( $sql_stmt );
> > $sth->execute;
> > my $ddl_stmt;
> > while( ( $ddl_stmt ) = $sth->fetchrow_arrayref ) {
The condition here is always true, as the result of array of 1
element scalar evaluation. Like:

#perl -e 'my @a=(undef); print scalar(@a),"\n";'
1
#

> > print S"$ddl_stmt\n";
> > }
> > $sth->finish();
> >
> >
> > exit;

When I change the loop to:

my $row;
while( $row = $sth->fetchrow_arrayref ) {
print "ddl is ",( defined($row->[0]) ? "defined. Length=".length($row->[0]) : "undefined." ), "\n";
}

it works just fine:

#perl -w ./testmeta.pl
DBConnect Successful: dbi:OracleDBINST:
ddl is defined. Length=1722832
#

(This is the biggest package I have.)

Bye. Alex.