2

I am using this query with the Perl DBI:

SELECT c.change_id
     , COLLECT(t.tag) AS the_tags
  FROM changes   c
  LEFT JOIN tags t ON c.change_id = t.change_id
 WHERE c.project = ?
 GROUP BY c.change_id

The DBI uses OCI to prepare this statement, bind the value I pass, and get the results. But Oracle, for some reason, does not like it. The error output is:

     ORA-00932: inconsistent datatypes: expected - got - (DBD ERROR: error possibly near <*> indicator at char 41 in '
            SELECT c.change_id
                 , <*>COLLECT(t.tag) AS the_tags
              FROM changes   c
              LEFT JOIN tags t ON c.change_id = t.change_id
             WHERE c.project = :p1
             GROUP BY c.change_id
        '

Not very informative. However, I can make this error go away not only by changing the call to COLLECT() also by replacing the placeholder with the actual value:

     SELECT c.change_id
          , COLLECT(t.tag) AS the_tags
       FROM changes   c
       LEFT JOIN tags t ON c.change_id = t.change_id
      WHERE c.project = 'tryoracle'
      GROUP BY c.change_id

That version works perfectly. Why doesn't Oracle like the prepared statement with the COLLECT()?

In case it's any help, here is a trace of the OCI-related calls extracted via ora_verbose = 6 (h/t @bohica).

theory
  • 9,178
  • 10
  • 59
  • 129
  • theory, A DBD::Oracle ora_verbose=6 trace might be more informative. – bohica May 07 '13 at 08:30
  • Done, though now it is working. Trying to get it to fail again. Will update that gist… – theory May 07 '13 at 16:03
  • There we go. Had to restart the Oracle VM. Whatever. – theory May 07 '13 at 16:12
  • This is indeed strange. The parameter is bound as a varchar and given your parameter looks like a string that seems ok - I presume project is a string. The weird bit is the error "ORA-00932: inconsistent datatypes: expected - got" which is missing what what was "expected" and what was "got". It is normally something like expected x - got y. Are you by an chance Oracle 10.1.0.0-10.2.0.2 as bug 4381035 might apply. – bohica May 09 '13 at 10:33
  • Nope, using an [11.2 VirtualBox image](http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html). – theory May 09 '13 at 16:32
  • I've had the same issue with Oracle XE 11gR2 in SQL Developer directly.I don't think it's related to Perl / OCI, etc, just a plain old Oracle bug. I even got ORA-00600 on some configurations – Lukas Eder Jan 20 '15 at 18:43
  • @LukasEder Yeah, would be nice if the error message was more informative. The answer I supplied below fixed the issue for me. – theory Jan 27 '15 at 00:48

1 Answers1

2

Finally got a solution to this issue, thanks to some digging by a user. The problem was not with the placeholder; why it worked without the placeholder on the VirtualBox image I have no idea. No, the issue was with the COLLECT(). Seems that both the values being collected need to be cast to a specific type, and the resulting array also needs to be cast to a pre-defined array data type. Just so happens that my code has a custom array type:

CREATE TYPE sqitch_array AS varray(1024) OF VARCHAR2(512);

So I'm able to get the query to work by casting the COLLECT() like so:

CAST(COLLECT(CAST(t.tags as VARCHAR2(512))) AS sqitch_array)
theory
  • 9,178
  • 10
  • 59
  • 129
  • 1
    Is there a way to do this in one line? Something along the lines of `CAST(COLLECT(CAST(t.tags as VARCHAR2(512))) AS (varray(1024) OF VARCHAR2(512)))`? – Kurt Mueller Mar 16 '15 at 20:10
  • I mysteriously got ORA-00932 with `select distinct myid, myblobcolumn from mytable`. Removing `distinct` helped. – Kjetil S. Mar 08 '18 at 13:20