Binding a date in a call to an Oracle function

pete_c has asked for the wisdom of the Perl Monks concerning the following question:

Please help...

I'm attempting to call an oracle function which takes a date as one of it's input parameters... I do this...

$cursor = $dbh->prepare("BEGIN create_person( ?, ?, ?, ?, ?, ?, ?, ?, 
+?, ? ); END;");
$cursor->bind_param(1, $title);
$cursor->bind_param(2, $name);
$cursor->bind_param(3, $surname);
$cursor->bind_param(4, $sex);
$cursor->bind_param(5, $date);
$cursor->bind_param(6, $origin);
$cursor->bind_param(7, $user);
$cursor->bind_param_inout(8, \$person, 10);
$cursor->bind_param_inout(9, \$errCode, 1024);
$cursor->bind_param_inout(10, \$errNum, 1024);

$cursor->execute();
[download]

I get:
ORA-06512: at "OWNER.CREATE_PERSON", line 164 ORA-06512: at line 1 ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR: OCIStmtExecute)

Narrowed it down to the date field (but i could be wrong).... Any ideas what's going on?

Cheers in advance! _{janitored by ybiC: <code> tags around code, <tt> tags around error message}

Comment on Binding a date in a call to an Oracle function Download Code

Replies are listed 'Best First'.
Re: Binding a date in a call to an Oracle function by hmerrill (Friar) on Oct 03, 2003 at 17:45 UTC
As other's have mentioned, chances are you need to use TO_DATE to convert the string date into an object that Oracle understands. I think it will look something like this: `$sth = $dbh->prepare(qq{ BEGIN create_person( ?, ?, ?, ?, TO_DATE(?, 'MM/DD/YYYY HH:SS'), ?, ?, + ?, ?, ? ); END; });` [download] Notice the TO_DATE - has a placeholder(?) in it that will still be bound to bind_param 5. And you'll have to change the date format to match the format of the date in $date. But I think(?) that's the general idea. It's been quite a while since I've worked with Oracle so take this with a grain of salt :-) HTH.	[reply] [d/l]
Re: Binding a date in a call to an Oracle function by dragonchild (Archbishop) on Oct 03, 2003 at 16:17 UTC
Try using TO_DATE(), which is an oracle function to convert a string to a date. ------ We are the carpenters and bricklayers of the Information Age. The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6 Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply]
Re: Re: Binding a date in a call to an Oracle function by seaver (Pilgrim) on Oct 03, 2003 at 16:39 UTC
If you can, post the description of the table itself: describe table If the offending column is a 'datetime' column, then it will only accept 'datetime' objects, an oracle specific 'format'. Oracle doesn't care what's IN the 'datetime' objects, except that they must be a 'datetime' object. The ToDate() function dragonchild mentions converts whatever the string is into a 'datetime' object according to the /pattern/ you use in the function, for example: `TO_DATE('01-FEB-2002' ,'dd-mon-yyyy')` [download] The /pattern/ MUST be able to recognise the entire string, so every string you pass in must follow the same pattern. Be warned, when you look for your dates in the table, they will NOT neccessarily come back to you in the same format as you declared in the TO_DATE function. This is because Oracle has its own 'datetime' format constant, which is applied to all 'datetime' formats when they are called FROM the database. This can be changed, though I forget what the constant is called. Cheers Sam	[reply] [d/l]
Re: Binding a date in a call to an Oracle function by VSarkiss (Monsignor) on Oct 03, 2003 at 16:17 UTC
Oracle can be picky about date parameters. What does your `$date` string look like? If it's something like `17760704` (for example), it won't fly. Try formatting it as `1776/07/04`.	[reply] [d/l] [select]
Re: Binding a date in a call to an Oracle function by pete_c (Initiate) on Oct 06, 2003 at 14:17 UTC
As it turns out i was completely wrong in my assumption that it was the date field that was failing!!!! It was really the next field in the parameter list: $origin The oracle function has the input field declared as simply char, with no size implied.... I changed the function to assign a specific type and size and it all worked swimmingly... Many thanks to those who responded tho' cheers pete	[reply]