Hi list,
Datetime column is a datatype date, when I insert records using the following code I
am getting 'ORA-01858: a non-numeric character was found where a numeric which is
because of the date record. When I changed the date column to datatype varchar2 it is
working fine but it not working with date.
What is the format in dbi to insert date records?.
#!/usr/bin/perl -w
use DBI;
use strict;
use DBI qw(:sql_types);
DBI->trace( 2, 'dbitrace.log' );
my $dbh1 = DBI->connect("DBI:CSV:");
$dbh1->{'csv_tables'}->{'processed'} = {
'eol' => "\n",
'sep_char' => "|",
'quote_char' => undef,
'escape_char' => undef,
'file' => '/home/user1/reports/processed.csv',
'col_names' => ["hostname", "datetime", "url","username", "company"]
};
my $dbh2 = DBI->connect("dbi:Oracle:db1", "report", "report" ) or die "Can't make 2nd
database connect: $DBI::errstr\n";
my($hostname, $url, $username, $company, $datetime);
#$sel1->bind_columns(undef, \$hostname, \$datetime, \$url, \$username, \$company);
my $sel1 = $dbh1->prepare("SELECT hostname,datetime,url,username,company FROM
processed");
$sel1->execute();
my $array_ref = $sel1->fetchall_arrayref();
$sel1->bind_columns( undef, \$hostname, \$datetime, \$url, \$username, \$company );
foreach my $row (@$array_ref) {
my ($hostname, $url, $username, $company, $datetime) = @$row;
$dbh2->do("INSERT INTO webtest (hostname,datetime,url,username,company) VALUES (
?,?,?,?,? )", undef, $hostname, $datetime, $url, $username, $company );
}
$dbh1->disconnect();
bash-2.03$ cat processed.csv
test.aegislimited.com| 16/Aug/2004:15:06:14|/index.html|demo1|Demo Company|
test.aegislimited.com| 16/Aug/2004:15:06:15|/index.jhtml|demo1|Demo Company|
test.aegislimited.com| 16/Aug/2004:15:06:54|/index.jhtml|demo1|Demo Company|
Thanks in advance
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
