0

I am getting the following error while trying to update a timestamp on oracle database using per dancer.

ORA-01858: a non-numeric character was found where a numeric was expected.

The code:

   my $fmt = 'YYYY-MM-DD HH24:MI:SS:FF3';
   database->do("alter session set nls_timestamp_format = '$fmt'");


    my $time_start =params->{'start1'};#2014-04-29 10:21:42:037

    my $time_stop = params->{'stop1'};  #2014-04-29 10:42:08:355
    my $id = params->{'id'};

    my $timestart ="TO_TIMESTAMP('$time_start','YYYY-MM-DD HH24:MI:SS:FF3')";
    my $timestop ="TO_TIMESTAMP('$time_stop','YYYY-MM-DD HH24:MI:SS:FF3')";
    database->quick_update($table, {ID => $id}, {SBYS_START_TIME => $timestart, SBYS_STOP_TIME =>$timestop});
user2838042
  • 57
  • 6
  • How about some elementar debugging? `select TO_TIMESTAMP('2014-04-29 10:21:42:037','YYYY-MM-DD HH24:MI:SS:FF3') from dual;` in `sql+` or sql developer? – mpapec Apr 29 '14 at 10:22

1 Answers1

0

There's 2 possibilities I see here:

  • Your input values don't match what you're commented (for example, a missing 0 on that stop time, maybe on the seconds)

To check this, add a quick debug line:

my $time_start = params->{'start1'};
debug $time_start;
my $time_stop = params->{'stop1'};
debug $time_stop;
  • Your code is passing $time_start as a literal sting to the database.

Try going old school for this one.:

my $timestart ="TO_TIMESTAMP('" . $time_start . "','YYYY-MM-DD HH24:MI:SS:FF3')";
my $timestop ="TO_TIMESTAMP('" . $time_stop . "','YYYY-MM-DD HH24:MI:SS:FF3')";

That will eliminate that possibility.

Casao
  • 542
  • 2
  • 6