Re: Oracle Freakishness

On May 5, 2013, at 7:34 AM, Garry T. Williams <[email protected]> wrote:

>    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions031.htm#SQLRF06304 
> 
>    "COLLECT is an aggregate function that takes as its argument a
>    column of any type and creates a nested table of the input type
>    out of the rows selected. To get accurate results from this
>    function you must use it within a CAST function."
> 
> Do you have to define a type and cast COLLECT() to that type?

No, I get the same error if I cast it to a varray. What’s bizarre is that Oracle says that the error is on the join to tags, not the collect. Here’s another example (with the cast):

    ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 419 in '
        SELECT c.change_id AS id, c.change AS name, c.project, c.note,
               to_char(c.planned_at AT TIME ZONE 'UTC', '"year":YYYY:"month":MM:"day":DD') || to_char(c.planned_at AT TIME ZONE 'UTC', ':"hour":HH24:"minute":MI:"second":SS:"time_zone":"UTC"') AS timestamp, c.planner_name, c.planner_email,
               cast(COLLECT(t.tag) AS sqitch_array) AS tags
          FROM changes   c
          LEFT JOIN <*>tags t ON c.change_id = t.change_id
         WHERE c.project = :p1
         GROUP BY c.change_id, c.change, c.project, c.note, c.planned_at,
               c.planner_name, c.planner_email, c.committed_at
         ORDER BY c.committed_at ASC
    ')

The problem is not solved if I change the name of the collected column from "tags" to something else. However, the problem goes away if I quote the project directly and include it in the statement, rather than use a placeholder. I find this bizarre, though there is no doubt a very good explanation for it.

Thanks,

David