New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Perl DateTime error with Oracle 10g #316
Comments
Validated with Debian Docker Image (FROM perl:latest) and installed Sqitch and DBD::Oracle with
Error is a following:
|
What's the output of this query on your install of Oracle?
|
It should be something like
But it looks like the string you get from the database does not include the day (22 in this example) or the time zone (UTC in this example). Which is just weird. Isn't |
I've tried it with 'sqlplus' at the database server itself, Oracle Instantclient (12.1.0.2.0-1), and Oracle SQL-Developer (4.x), but the output is as following:
It seems to be a weird behaviour of the variable binding / substitution. I'm using 'systimestamp' for simplification:
If there are other literals within quotes, the last characters get truncated:
The only workaround is to concat the string, which could be in fact quite combersome:
|
Good grief Oracle. Okay, please try this:
|
The correct statement would be:
Changes to your previous statement:
|
Okay, well, this seems kind of stupid. The reason there were two calls to |
Seems to be an Oracle 10g issue. I've quick test it with an Oracle 11g XE docker image:
I'm gonna fork and maintain a custom Sqitch repository, until we can get rid of Oracle 10g .. |
This seems crazy. I posted a StackOverflow question to see if someone knows of a configuration variable to set or something. |
Hey @johannwilfling, can you take look at this SO answer? The issue might be with SQL*Plus… |
It could deal with NLS. I posted the answer to the same SO question. Enough buffer could be allocated independently from any other conditions or parameters if you explicitly typecast the expression "as varchar2(N char)"
|
Hrm, I thought maybe I could just have one call to
But then in a DBI call I got this error:
Will try keeping the date and time separate again. :-( |
Okay, this seems to work:
I'll commit that; would be great if you could try it out, @johannwilfling. |
Sorry for responding so late due to sickness / vacation. But unfortunately the cast to varchar didn't work either:
I do really think that this is a bug of the 10.2.0.1.0 release (SQLDeveloper, Toad didn't work either). |
Hrm. Maybe try this:
|
Sorry .. :-(
|
Hi, sorry for the radio silence. Is this still an issue, @johannwilfling? |
It looks like your Oracle allocates a character buffer for wrong length semantica. You are using
P.S. |
That sounds like the likely culprit, @nmaqsudov — thanks! @johannwilfling can you let me know if that version fo the query works for you? Hoping to release v0.9998 this week. |
Sorry for the late response .. unfortunately building with Perl 5.28.0 and Sqitch v.0.9998 results with the following error:
|
That's pretty fucked up. Jebus Oracle. Just out of curiosity, does this patch make a difference? --- a/lib/App/Sqitch/Engine/oracle.pm
+++ b/lib/App/Sqitch/Engine/oracle.pm
@@ -139,7 +139,7 @@ sub _log_conflicts_param {
}
sub _ts2char_format {
- q{CAST(to_char(%1$s AT TIME ZONE 'UTC', '"year":YYYY:"month":MM:"day":DD') AS VARCHAR2(100 byte)) || CAST(to_char(%1$s AT TIME ZONE 'UTC', ':"hour":HH24:"minute":MI:"second":SS:"time_zone":"UTC"') AS VARCHAR2(168 byte))}
+ q{CAST(to_char(%1$s AT TIME ZONE 'UTC', '"year:"YYYY":month:"MM":day:"DD') AS VARCHAR2(100 byte)) || CAST(to_char(%1$s AT TIME ZONE 'UTC', '":hour:"HH24":minute:"MI":second:"SS":time_zone:UTC"') AS VARCHAR2(168 byte))}
}
sub _ts_default { 'current_timestamp' } |
New day - new error .. 😉 I've applied the above patch onto tag version v0.9998 and got the following error:
As we have a very old version of OracleDB, I want to mention that we can live with my previously working patch and stick with Sqitch version v0.9995 .. |
You mean this one? |
Yeah .. until now, we are working with this patch: Seems to be ugly, but it works .. |
That workaround just…offends me. :-( I've pulled in your fix in 38d13cf and it will be in the v0.9999 release. Thanks for bearing with me. |
Error
Steps to reproduce
After hitting the same error with self compiled Sqitch and Perl packages with CentOS, I've used the Debian packages from "unstable" (sid):
apt-get install sqitch libdbd-oracle-perl
Versions
(Oracle Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production)
Sqitch
The text was updated successfully, but these errors were encountered: