Re: How To Insert DATE

In our last gripping episode "Harald Henkel" <Harald_at_Henkel.DAH.UUnet.DE> wrote:
> Hello Howard.
>
> "Howard J. Rogers" <howardjr_at_www.com> schrieb im Newsbeitrag
> news:3a7c21e5_at_news.iprimus.com.au...
> >
> > "Harald Henkel" <Harald_at_Henkel.DAH.UUnet.DE> wrote in message
> > news:95h3t0$h9o6s$1_at_ID-56141.news.dfncis.de...
>
> <SNIP>
>
> > > ALTER TABLE employee
> > > ADD CONSTRAINT employee_PK PRIMARY KEY
> > > (emp_id) ;
 

> > > Of course in the real world, you would have to add tablespace and
 table
 size
> > > clauses to the create table and alter table statements for
 indexes.  

> > Can't let that one pass! In the real, real world, you ought to get
 out of
> > the habit of specifying a storage clause (and hence 'table size'
 things
 like
> > iniital and next extents) at segment level. If you've done your job
> > properly, the tablespace will be sitting there with an appropriate
 default
> > storage clause that can fill in the gaps.
>
> One default, that will be OK for any kind of table ?
> Or do you suggest to create many different tablespaces for
 different "types"
> of tables ?
>
> > On the other hand, I don't like to see any syntactical advice given
 on
> > Primary or Unique keys that doesn't EXPLICITLY include a tablespace
 clause
> > for the indexes associated with those constraints, because fishing
 indexes
> > out of DATA tablespace is a royal pain in the butt to have to keep
 doing,
 is
> > one of the simplest things to avoid, and all newbies should be
 taught
 about
> > it properly upfront.
>
> Hmm. Perhaps you're right. But I thought, that he's a real beginner,
 just
> starting with Oracle's default database, which has only one table
 space
> (USER_DATA). I didn't want to confuse somebody, who doesn't even know
 the
> base syntax, with information he didn't request.
>
> > Hence, the CORRECT syntax for declaring something to be a primary
 key
 after
> > the event is:
 

> > Alter table employee
> > add constraint employee_empid_pk Primary Key (emp_id)
> > Using Index
> > Tablespace INDEXBLAH ;
> >
> > And for doing it at the time of creating the table in the first
 place:
> >
> > Create table employee (
> > emp_id number (4) constraint employee_empid_pk Primary Key
> > using index
> > Tablespace INDEXBLAH,
> > name varchar2 (15),
> > age number (3),
> > date_hired date,
> > address varchar2 (30))
> > Tablespace DATABLAH;
>
> If I would have written that, I'm sure the next reply would have been:
> "Why am I getting the error:
>
> Tablespace DATABLAH
> *
> FEHLER in Zeile 9:
> ORA-00959: Tablespace 'DATABLAH' nicht vorhanden"
>
> (Sorry this is in German ;-) )
>
> > In short, you are asking for trouble if you don't specify
 tablespaces for
> > both the table and any associated constraint-inspired indexes, and
 the
> > sensible thing to do is (a) to get into the habit of always
 declaring them
> > and (b) always including those parts of the syntax when advising
 others.
> >
> > It is probably a bit picky of me, I know: but honestly, the amount
 of time
 I
> > spend advising on how to split tables apart from their indexes
 beggars
> > belief -and it's largely because few seem to realise that certain
> > constraints imply indexes, and those indexes must be housed
 appropriately.
>
> Sure.
> But I think to understand this, the guy has still to learn a lot and I
> didn't think it's been appropriate to be to explicit here.
> I gave a hint, that he would have to declare tablespaces in a real
 database.
> That seemed to be enough information.
> After all, there are some books about Oracle syntax out there, he
 could look
> up.
> I didn't intend to give him a complete Oracle DB setup and
 administration
> course in here, but simply answer his questions.
>
> With kind regards,
> Harald Henkel
>
>

I must take issue with the DATE insert instructions provided. The presumption is that the default date format is 'DD-MON-YYYY', and that may or may not be true. To insert a date one should really get into the habit of utilising the TO_DATE function:

 insert into employee
 values ( 1, 'John', 23, TO_DATE('18-JAN-2001', 'DD-MON- YYYY', 'Chicago');

This will ensure that the date string is translated correctly.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/