Re: Oracle Freakishness

"David E. Wheeler" <[email protected]> Mon, 2 Jun 2014 10:59:33 -0700

Newsgroups	gmane.comp.lang.perl.modules.dbi.general
Message-ID	<[email protected]>

On May 5, 2013, at 5:02 PM, David E. Wheeler <[email protected]> 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 (application/pgp-signature, 842 B)