On May 5, 2013, at 5:02 PM, David E. Wheeler <da...@kineticode.com> wrote:
> 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
> ')
For the archives, I finally got a fix for this, thanks to some digging by a
user. Turns out the problem was that COLLECT() needs the *collected* value to
be cast to a specific type. The fix is to change this:
cast(COLLECT(t.tag) AS sqitch_array) AS tags
To this:
CAST(COLLECT(cast(t.tag as varchar2(512))) AS sqitch_array) AS tags
Crazy, right?
Thanks,
David
signature.asc
Description: Message signed with OpenPGP using GPGMail
