[Dbix-class] $rs->create() with no column values bombs on Oracle

[Peter Rabbitson]

On 09/11/2014 11:39 PM, Lasse Makholm wrote:
>
>
> On Thu, Sep 11, 2014 at 7:58 PM, Peter Rabbitson <rabbit+dbic at rabbit.us
> <mailto:rabbit+dbic at rabbit.us>> wrote:
>
>     On 09/11/2014 05:48 PM, Lasse Makholm wrote:
>
>         Hi,
>
>         We're porting a DBIx::Class application from MySQL to Oracle,
>         containing
>         some insert statements with no column specifications by way of
>         $rs->create({}). This doesn't seem to work on Oracle.
>
>         DBIx::Class::SQLMaker generates "INSERT INTO table DEFAULT VALUES
>         RETURNING id INTO ?" which, as far as I can figure is not valid
>         syntax
>         on Oracle. Neither the is the other common variant for such
>         statements;
>         "INSERT INTO table () VALUES ()".
>
>
>     This is clearly a bug that we've never tested for previously. Can
>     you please confirm that the following works on your Oracle RDBMS
>     (yes, the first ()pair is gone):
>
>     INSERT INTO $tablename VALUES(DEFAULT);
>
>
> That only works for a table with only one column. For more, you'd need
> to repeat DEFAULT for each column. Otherwise you get: "SQL Error:
> ORA-00947: not enough values".

I see.

>
> I wonder how/if an explicit DEFAULT interacts with the typical scenario
> of having a before insert trigger pulling IDs from a sequence to
> implement auto increment columns...
>
> I guess, in a sense, that question boils down whether there's any
> difference between:
>
> INSERT INTO t (a, b) VALUES (DEFAULT, 42)
>
> and:
>
> INSERT INTO t (b) VALUES (42)
>
> In any case, I don't know the answer but I suspect we have some resident
> Oracle experts who do... I'll discuss it with them and report back...
>

Yes please do. What we are looking for basically is the most "universal" 
statement we could come up with. Putting it in is just a SMOP after that.

Cheers