Prepare statement failing when using a command line argument for date value

I am executing my script on the command line with parameter ('?')  values for $firstday and $lastday.  I am passing values sysdate-60 and sysdate for these 2 values of '?'  These values are called from a oracle database function of a DATE datatype.  But the script is failing with on a non-numeric character found on  '?'.   When I run the SELECT query by itself on the database, it works and returns rows.

Is the way I have written the prepare statement in call a database function date parameter,  the issue?

My prepare and bind/execute statements are:

my ($db, $user, $pw, $wkdir, $firstday, $lastday)  = @ARGV;
..
...
...
my $gen_text = $dbh->prepare(qq(SELECT col1, col2, col3  FROM region WHERE status = 'Y' and partition_key = (Select common.get_region('REGION',?, ?) from dual)))
                  								or die("Could not get data from table '$_'".DBI->errstr);

  ...            						
 $gen_text->bind_param(1,$firstday);
 ....
 $gen_text->bind_param(2,$lastday);
 ..
 $gen_text->execute();

Open in new window

steve2312Asked:
Who is Participating?
 
steve2312Author Commented:
I found the issue was with format of parameter passed on to the database. I needed to use to_date (?,...) function inside the prepare statement and around the bind parameters to resolve the issue.

select ..... , to_date(?,'YYYY-MM-DD'), to_date(?,'YYYY-MM-DD') from dual;

Open in new window


Thanks for the suggestion.
0
 
Jan SpringerCommented:
Have you tried to escape the "?" with a backslash "\"?
0
 
steve2312Author Commented:
Thanks for your suggestion.  I did try to use the backslash with ?, the prepare fails with error message...

Code used:
SELECT common.get_region('REGION',"\"?, "\"?) from dual

Open in new window


Error:
illegal zero-length identifier (DBD ERROR: OCIStmtPrepare)

Open in new window

0
 
Jan SpringerCommented:
I'm not sure.  Any command that works talking directly to the sql database should work with the proper escaping and quoting from within a perl script.
0
 
steve2312Author Commented:
Found out the issue was on the database parameter format and not with Perl Prepare statement error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.