Hello Monks,

I ma trying to uplaod a CSV into Oracle Databse, the problem is, my script gets stuck and I have to kill it with CTRL+C.. Here is hte csv: 

# cat test.csv
vm,farm,dmz
name1,farm1,z1
name2,farm2,z2
name3,farm3,z3

[download]

and here is the code: 

#!/usr/bin/perl -w

use strict;
use DBI;

open (my $FH, '<', "test.csv") or die "Cannot open test.csv: $! \n";

my $dbh = DBI->connect ("dbi:Oracle:host=<hostname>;sid=<SID>", '<user
+name>', ',password.', { RaiseError => 1, AutoCommit => 1, TraceLevel 
+=> 4})
                        or die "Canot create Databse Handle: $DBI::err
+str() \n";

my $table = "CLUSTER_MAPPING";

my @array = <$FH> ;
close $FH;
shift @array; ### this is to remove the column headers

my $sth = $dbh->prepare ("INSERT INTO $table (name, farm, dmz) VALUES 
+(?,?,?)");

while (<@array>)
{
        my @row = split /,/;
        my $col1_value = $row[0];
        my $var1 = $row[1];
        $var1 =~ s/"//g;
        $var1 =~ s/^ //g;
        my $col2_value = $var1;
        my $col3_value = $row[2];
        $sth->execute ($col1_value, $col2_value, $col3_value);
}

$sth->finish();
$dbh->disconnect();

[download]

here is the debug output from the script ( i have used level 2 as seen in AutoCommit => 2): 

    DBI::db=HASH(0x228e560) trace level set to 0x0/2 (DBI @ 0x0/0) in 
+DBI 1.622-ithread (pid 15876)
    -> STORE for DBD::Oracle::db (DBI::db=HASH(0x228e560)~INNER 'Usern
+ame' '<username>') thr#21f5010
    <- STORE= 1 at DBI.pm line 713
    -> connected in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(0x228
+e5f0)~0x228e560 'dbi:Oracle:host=<hostname>;sid=<SID>' '<username>' *
+*** HASH(0x2401778)) thr#21f5010
    <- connected= undef at DBI.pm line 720
    -> STORE for DBD::Oracle::db (DBI::db=HASH(0x228e560)~INNER 'dbi_c
+onnect_closure' CODE(0x228de28)) thr#21f5010
    <- STORE= 1 at DBI.pm line 729
    -> prepare for DBD::Oracle::db (DBI::db=HASH(0x228e5f0)~0x228e560 
+'INSERT INTO CLUSTER_MAPPING (name, farm, dmz) VALUES (?,?,?)') thr#2
+1f5010
        dbd_preparse scanned 3 distinct placeholders
    <- prepare= DBI::st=HASH(0x228e938) at upload.pl line 17
    -> execute for DBD::Oracle::st (DBI::st=HASH(0x228e938)~0x228e980 
+'name1' 'farm1' 'z1') thr#21f5010
dbd_bind_ph(1): bind :p1 <== 'name1' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph_char() (1): bind :p1 <== 'name1' (size 28/32/0, ptype 5(
+VARCHAR), otype 1 )
dbd_bind_ph(1): bind :p2 <== 'farm1' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph_char() (1): bind :p2 <== 'farm1' (size 25/32/0, ptype 5(
+VARCHAR), otype 1 )
dbd_bind_ph(1): bind :p3 <== 'z1' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph_char() (1): bind :p3 <== 'z1' (size 24/32/0, ptype 5(VAR
+CHAR), otype 1 )
   dbd_st_execute INSERT (out0, lob0)...
Statement Execute Mode is 32 (COMMIT_ON_SUCCESS)

[download]

After this, the script gets stuck at inserting the 1st record and then I have to kill it.

Could you tell me what I am doing wrong here. I think I have missed something obvious but not able to figure out that.

-Thanks.

In reply to DBD::ORacle: Not able to insert into Database by slayedbylucifer

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.