Insert datetime into Oracle using perl DBI parameterized INSERT statement

Question

I need to insert a datetime value into an Oracle database table using perl. The following code inserts the required value:

use strict;
use warnings;
use DBI ;
use DBD::Oracle ;

my $SRV1='server1';
my $DB1='database1';

my $Date_Str_To_Insert="TO_DATE('2003/05/03 21:02:44','yyyy/mm/dd hh24:mi:ss')";

my $db1 = DBI->connect("dbi:Oracle:$SRV1/$DB1", "user", "pword");
my $SQL1="insert into table1 (valdat) values ($Date_Str_To_Insert)";
my $SQL1_sth = $db1->prepare($SQL1);
$SQL1_sth->execute();
$SQL1_sth->finish();
$db1->disconnect if defined($db1) ;

However, if I replace

my $SQL1="insert into table1 (valdat) values ($Date_Str_To_Insert)";

by

my $SQL1="insert into table1 (valdat) values (?)";

and

$SQL1_sth->execute();

by

$SQL1_sth->execute($Date_Str_To_Insert);

Then I get the following error:

DBD ERROR: error possibly near <*> indicator at char 42 in 'insert  into t_datatable (valdat) values (:<*>p1)') [for Statement "insert into t_datatable (valdat) values (?)" with ParamValues: :p1='TO_DATE('2021-08-04 11:03:05','yyyy-mm-dd hh24:mi:ss')'] at perltestprog.pl line 6977.

How can I define the variable $Date_Str_To_Insert so that it works using the question mark notation?

Answer 1

Try this one:

my $SQL1 = "insert into table1 (valdat) values (TO_DATE(?,'yyyy/mm/dd hh24:mi:ss'))";
my $SQL1_sth = $db1->prepare($SQL1);
$SQL1_sth->execute('2003/05/03 21:02:44');