Re: Oracle and bindcolumns

On 2006-10-18 10:19:24 -0400, Hardy Merrill wrote:
I think I get it yes. So here is what I am doing. Access has a date
field that I am pulling out and when I print the "$start_date" variable

it looks like this:

2006-09-15 00:00:00

That is a string now to Perl...correct? Now I am inserted that string
into the Oracle database as a DATE. So I am doing, using the variable
from the bindcolumn parameter:

TO_DATE($start_date, 'MM/DD/YYYY')

to insert that string into Oracle as a DATE and passing in the date
format along with it.

The date format is wrong. If you tell Oracle to expect a date in
MM/DD/YYYY format and then feed it a string like '2006-09-15 00:00:00'
it will complain that they don't match. You will eitther have to change
the format string to match the actual date format (i.e., 'YYYY-MM-DD
HH24:MI:SS') or change the date format to match the format string.

Also, TO_DATE($start_date, 'MM/DD/YYYY') will almost certainly result in
a syntax error. That expands into something like
TO_DATE(2006-09-15 00:00:00, 'MM/DD/YYYY')
which is not not correct SQL (the quotes are missing). Use placeholders.

Finally, you might want to look at the execute_array method. For recent
versions of DBD::Oracle is a lot faster then execute for bulk inserts.

hp

--
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@xxxxxxxxx | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

Attachment: pgpW1hmAeI7v6.pgp
Description: PGP signature

Relevant Pages

  • Re: Date format detection
    ... Specifies the locale for which the date string is to be formatted. ... date format for this locale. ... the system default-date format for the specified locale. ... be enclosed within single quotation marks in the date format picture. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Date format detection
    ... > Specifies the locale for which the date string is to be formatted. ... > date format for this locale. ... > the system default-date format for the specified locale. ... > be enclosed within single quotation marks in the date format picture. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Date confusion
    ... Read my reply elsewhere in this string. ... > integer portion of the value represents the date as the number of days ... >> If you are working with a numeric date and you want the format DDMMYYYY, ... Another reason for preferring to store a date as text would be personal preference. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Regular Expression - old regex module vs. re module
    ... The ReportTemplate class reads a template file or string containing a ... header and line format for multiple calls with sequence data. ... def _format: ...
    (comp.lang.python)
  • Re: Regular Expression - old regex module vs. re module
    ... If you want to include backslashes in a string, ... a one character string, which is unlikely to be what you wanted. ... formating characters before a format, then you should use a negative ... #read in and parse a format template ...
    (comp.lang.python)