What is your NLS_DATE_FORMAT set to? ----------------- Ron Reidy Senior DBA Array BioPharma, Inc.
-----Original Message----- From: Robert [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 1:25 PM To: [EMAIL PROTECTED] 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 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
