DBI limitation???

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

I am having trouble creating a large table using perl DBI do and/or prepare execute. The table being created is approximately 2.2B records. When the code is executed the tables is created in my oracle 11.2g database but code dies if do(sql) or die / execute(sql) or die. However, errstr is null if you try to print the error message. If we reduce the number of rows created, the script runs fine.

use strict;
use DBI;

###build connection strings
my $HOST=`hostname`;
my $sid="$ENV{ORACLE_SID}";
my $dbh;

my $ctas_sql = q(create table perl_test_tb as select a.lvl
                from
                (select level lvl from dual connect by level <= 100000
+00) a
                ,(select level lvl from dual connect by level <= 220) 
+b
                );
connect_to_db();



sub connect_to_db {
    my $errormessage;
  # get the base filename for this script
  my $user = 'fdsglobal';
  my $pass = 'snafu';

  # Just connect to the db.
  my $connection = qq(dbi:Oracle:host=$HOST;sid=$sid);
  $dbh = DBI->connect("$connection", "$user", "$pass",
    {PrintError => 0,
     PrintWarn => 0,
     LongReadLen => 2000000100 # add 100 to the longest possible long,
+ as per DBI man page
    })
    or die "Could Not Connect To Database: ". $DBI::errstr;
}


my $rows = $dbh->do($ctas_sql) or die "Cannot run code: $ctas_sql \n".
+ $dbh->errstr ."\n\n";
my $ctas_error = $dbh->errstr;

print "perl test table created with $rows rows\n";
[download]

Comment on DBI limitation??? Download Code

Replies are listed 'Best First'.
Re: DBI limitation? by hippo (Archbishop) on Apr 05, 2016 at 17:53 UTC
The table being created is approximately 2.2B records. That's close to a known number. I'll guess you are on a 32-bit perl? Returning 0 instead of an int overflow seems like a sensible compromise. You should test for undef on error, not false.	[reply]
Re^2: DBI limitation? by kyledba2013 (Initiate) on Apr 05, 2016 at 20:09 UTC
yes, i think this is how we will have to go. Is there a way to avoid the integer overflow. (please don't say reduce the number of rows created :) ) possibly changing the dataype to a floating number instead of a integer?	[reply]
Re: DBI limitation??? by BrowserUk (Patriarch) on Apr 05, 2016 at 17:54 UTC
Have you tried running the same sql from the oracle prompt? With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". I knew I was on the right track :) In the absence of evidence, opinion is indistinguishable from prejudice.	[reply]
Re^2: DBI limitation??? by kyledba2013 (Initiate) on Apr 05, 2016 at 19:15 UTC
Thank you for the quick reply. Yes, the scripts runs fine in a sqlplus prompt. In fact the table is created from the perl code, however it always hits the die command.	[reply]
Re: DBI limitation??? by derby (Abbot) on Apr 06, 2016 at 14:14 UTC
The table being created is approximately 2.2B records. .... `LongReadLen => 2000000100 # add 100 to the longest possible long, + as per DBI man page` [download] Why are you setting LongReadLen so big? It has nothing to do with the number of records you want to retrieve but the size of possible blob data in a record. It could be the underlying OCI lib is running out of space based on the LongReadLen ... if you have no blob data in the records, just remove LongReadLen. If you do have blob data, and you have 2G of blob data for each of the 2.2B records, well .. yikes. -derby	[reply] [d/l]
Re^2: DBI limitation??? by kyledba2013 (Initiate) on Apr 06, 2016 at 15:09 UTC
This was copied from another code that was getting metadata from another table in oracle that comes in a large blob format. you are right this is not needed.	[reply]
Re^3: DBI limitation??? by derby (Abbot) on Apr 06, 2016 at 16:58 UTC
Gotcha. Well ... instead of a `$dbh->do(...)` ... which would read all the rows into memory before returning, I'd try the prepare/execute/fetchrow approach and hope the underlying DBD/OCI does the right thing memory wise. `my $sth = $dbh->prepare( $ctas_sql ); $sth->execute(); while( my $row = $sth->fetchrow_arrayref ) { # do whatever with $row }` [download] -derby Update: Doh! just reread this and saw it was a create statement, not a select. In that case I'm guessing you're timing out somewhere. You may want to look into using DBI->trace to try and track that down.	[reply] [d/l] [select]
Re: DBI limitation??? by chacham (Prior) on Apr 06, 2016 at 13:42 UTC
First thing that comes to mind is a timeout. If the connection dies, checking the error code would establish a new connection and show no error. Right? To test, watch the process (if you have the tool installed). Or, just do less records at a time until you hit the number that fails. BTW, just a bit of golfing, there's no need to alias the second table or its column. Indeed, you can just use *.	[reply]