On May 5, 2013, at 7:34 AM, Garry T. Williams <gtwilli...@gmail.com> 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
