<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 12, 2014 at 6:25 AM, Peter Rabbitson <span dir="ltr"><<a href="mailto:rabbit+dbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On 09/11/2014 11:39 PM, Lasse Makholm wrote:<br>
</span><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">
<br>
<br>
On Thu, Sep 11, 2014 at 7:58 PM, Peter Rabbitson <<a href="mailto:rabbit%2Bdbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a><br></span><span class="">
<mailto:<a href="mailto:rabbit%2Bdbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a>><u></u>> wrote:<br>
<br>
On 09/11/2014 05:48 PM, Lasse Makholm wrote:<br>
<br>
Hi,<br>
<br>
We're porting a DBIx::Class application from MySQL to Oracle,<br>
containing<br>
some insert statements with no column specifications by way of<br>
$rs->create({}). This doesn't seem to work on Oracle.<br>
<br>
DBIx::Class::SQLMaker generates "INSERT INTO table DEFAULT VALUES<br>
RETURNING id INTO ?" which, as far as I can figure is not valid<br>
syntax<br>
on Oracle. Neither the is the other common variant for such<br>
statements;<br>
"INSERT INTO table () VALUES ()".<br>
<br>
<br>
This is clearly a bug that we've never tested for previously. Can<br>
you please confirm that the following works on your Oracle RDBMS<br>
(yes, the first ()pair is gone):<br>
<br>
INSERT INTO $tablename VALUES(DEFAULT);<br>
<br>
<br>
That only works for a table with only one column. For more, you'd need<br>
to repeat DEFAULT for each column. Otherwise you get: "SQL Error:<br>
ORA-00947: not enough values".<br>
</span></blockquote>
<br>
I see.<span class=""><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
I wonder how/if an explicit DEFAULT interacts with the typical scenario<br>
of having a before insert trigger pulling IDs from a sequence to<br>
implement auto increment columns...<br>
<br>
I guess, in a sense, that question boils down whether there's any<br>
difference between:<br>
<br>
INSERT INTO t (a, b) VALUES (DEFAULT, 42)<br>
<br>
and:<br>
<br>
INSERT INTO t (b) VALUES (42)<br>
<br>
In any case, I don't know the answer but I suspect we have some resident<br>
Oracle experts who do... I'll discuss it with them and report back...<br>
<br>
</blockquote>
<br></span>
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.<br></blockquote><div><br></div><div>So, to the best of my knowledge and that of those I've talked to, there is no way insert a row in Oracle without listing at least one column.</div><div><br></div><div>That leaves picking an arbitrary list of columns and supplying the DEFAULT keyword as value. </div><div><br></div><div>I guess that leaves the question of how many and which columns to specify in the insert statement. Choosing one arbitrary column seems weird but including all columns seems overly redundant/wasteful. I'm not sure which solution is less ugly...</div><div><br></div><div>Thoughts?<br></div><div><br></div><div>/L</div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Cheers<div class="HOEnZb"><div class="h5"><br>
<br>
______________________________<u></u>_________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-<u></u>bin/mailman/listinfo/dbix-<u></u>class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/<u></u>repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/<u></u>dbix-class@lists.scsys.co.uk</a><br>
</div></div></blockquote></div><br></div></div>