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

[Lasse Makholm]

On Thu, Sep 11, 2014 at 7:58 PM, Peter Rabbitson <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 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...

/L



>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/
> dbix-class at lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140911/4932a889/attachment.htm>