Re: how to set a DEFAULT value !!

I wrote:

Whoa. Be careful what you say about Oracle.

Oracle does have default values for table columns, defined
by the DEFAULT clause in CREATE/ALTER TABLE.

However, the _only_ way to get a column set to the default value
is to leave the column out of the INSERT statement altogether.
There is nothing you can put in a VALUES(..) list which
will do the trick, nor is there anything that can be bound to
a placeholder which will result in the default being set.

and Greg Sabino Mullane replied:

You sure about that? You might want to check your docs, or
update to a newer version of Oracle. If I recall correctly,
this ability was added in 9i.

Doh. You are right. Oracle 9i does support the DEFAULT
keywords in VALUES(...) lists. I apologise for my mistake.

My feeble excuse is that this new use of the word DEFAULT
is not indexed in either the 9i or the 10g SQL Reference Manual.

However, this does not help as much as you might think.
DBD::Oracle does not implement placeholder binding by
re-writing statements itself. Instead it uses Oracle's
internal implementation of placeholders.

The second half of my statement above still appears to be
true. If a statement like the following has been prepared

INSERT ... VALUES ( .... ? .... )

there is no way of binding anything to the placeholder which
make the statement act like

INSERT ... VALUES ( .... DEFAULT .... )

The only possibilities are to bind a value or to bind a
NULL.

I would be very happy to be proved wrong on this point,
but I have checked the 9i and 10g OCI manuals, and I
am depressingly sure that I am right.

So - my point remains. DBD::Oracle, as designed, cannot
implement the suggested feature.

--
Charles Jardine - Computing Service, University of Cambridge
cj10@xxxxxxxxx Tel: +44 1223 334506, Fax: +44 1223 334679
.

Relevant Pages

  • RE: Finding out the type expected for a placeholder
    ... Subject: RE: Finding out the type expected for a placeholder ... >> entering the SQL to use, with bind variables, and I would like to ... > Oracle, like most databases, offers no natural way to do this. ... You would have to query the database object tables prior to executing ...
    (perl.dbi.users)
  • RE: Using to_dsinterval or INTERVAL with placeholder
    ... Using to_dsinterval or INTERVAL with placeholder ... these are very obscure Oracle types so I could see that they may not work. ... Set the NLS Date Format ...
    (perl.dbi.users)
  • Re: how to set a DEFAULT value !!
    ... Oracle does have default values for table columns, ... by the DEFAULT clause in CREATE/ALTER TABLE. ... the _only_ way to get a column set to the default value ...
    (perl.dbi.users)