You could either set the NLS date format, or wrap a to_date around you
placeholder, something like (untested)
to_date(?, 'DD/Mon/YYYY:24HH:MI:SS') for 16/Aug/2004:15:06:54
This may need a leading space, or you may need to trim your field as there
seems to be a blank in front
Dan
Robert <[EMAIL PROTECTED]>
27/08/2004 07:25
To: [EMAIL PROTECTED]
cc: (bcc: Dan Horne/IT/AKLWHG/WHNZ)
Subject: DBI insert records
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
********************************************************************************
NOTICE
This email and any attachments are confidential. They may contain privileged
information or copyright material. If you are not an intended recipient, you
should not read, copy, use or disclose the contents without authorisation as
we request you contact us as once by return email. Please then delete the
email and any attachments from your system. We do not accept liability in
connection with computer viruses, data corruption, delay, interruption,
unauthorised access or unauthorised amendment. Any views expressed in this
email and any attachments do not necessarily reflect the views of the
company.
********************************************************************************
