Remove quote() Workaround in Oracle Engine

[theory]

For some reason, the COLLECT() aggregate does not play nice with placeholders, at least in the copy of Oracle I ran. So there are a few methods I had to copy from DBIEngine to oracle.pm just to replace the placeholder with a quoted variable. These can be ripped out assuming I ever figure out what the problem was (StackOverflow question; Perl DBI enquiry. Grep oracle.pm for "stackoverflow" to find the bits to rip out.

[theory]

Fat-finered that close.

[theory]

Thanks to some detective work from Timothy Procter, it seems that the issue is that COLLECT needs its results cast to an existing data type. He got things to work with this patch:

--- lib/App/Sqitch/Engine/oracle.pm    2014-01-16 18:23:30.000000000 -0500
+++ lib/App/Sqitch/Engine/oracle.pm   2014-05-02 12:57:00.095527500 -0400
@@ -139,8 +139,8 @@
 }

 sub _listagg_format {
-    # http://stackoverflow.com/q/16313631/79202
-    return q{COLLECT(%s)};
+    ## http://stackoverflow.com/q/16313631/79202
+    return q{CAST(COLLECT(cast(%s as varchar2(512))) AS sqitch_array)};
 }

 sub _regex_op { 'REGEXP_LIKE(%s, ?)' }
@@ -622,17 +622,19 @@
         ora_type => DBD::Oracle::ORA_VARCHAR2()
     });
     $sth->execute;
+
+    my $aggcol = sprintf $self->_listagg_format, 'dependency';

     # Retrieve dependencies.
-    my ($req, $conf) = $dbh->selectrow_array(q{
+    my ($req, $conf) = $dbh->selectrow_array(qq{
         SELECT (
-            SELECT COLLECT(dependency)
+            SELECT $aggcol
               FROM dependencies
              WHERE change_id = ?
                AND type = 'require'
         ),
         (
-            SELECT COLLECT(dependency)
+            SELECT $aggcol
               FROM dependencies
              WHERE change_id = ?
                AND type = 'conflict'

[benzvan]

This seems to work really well for me. I vote for a merge.

[theory]

It’s on my list, lots of tavel lately and overwhelmed with a million little things. I plan to fix all those places in oracle.pm that have this line:

XXX Oy, placeholders do not work with COLLECT() in this query.

Which means I should actually be able to rip out a bunch of code. Yay!

[benzvan]

I'm totally in favor of ripping out code!

---

Ben Zvan
Photos - http://photography.zvan.net/
Blog - http://ben.personal.zvan.net/
Flickr - http://www.flickr.com/photos/ben-zvan-photography/

On Jun 1, 2014, at 11:43 AM, David E. Wheeler notifications@github.com wrote:

It’s on my list, lots of tavel lately and overwhelmed with a million little things. I plan to fix all those places in oracle.pm that have this line:

XXX Oy, placeholders do not work with COLLECT() in this query.
Which means I should actually be able to rip out a bunch of code. Yay!

—
Reply to this email directly or view it on GitHub.