Re: How To Insert DATE

"Harald Henkel" <Harald_at_Henkel.DAH.UUnet.DE> wrote in message news:95h3t0$h9o6s$1_at_ID-56141.news.dfncis.de...
> Hello Jokes !?
>
> (I hope this was not a joke ;-))
>
> <jokesmalaysia_at_my-deja.com> schrieb im Newsbeitrag
> news:95gn8d$qvp$1_at_nnrp1.deja.com...
> > hello
> >
> > I have a very tiny, tiny problem
> > I want to create a table with following attributes:
> >
> > create table Employee
> > (emp_id number(4)
> > name varchar(15)
> > age number(3)
> > date_hired date? <-- not sure about this command!!
> > address varchar (30));
>
> It should read:
>
> create table Employee
> (
> emp_id number (4) not null,
> name varchar2 (15),
> age number (3),
> date_hired date,
> address varchar2 (30)
> ) ;
>
>
> > I want to insert this values(example only)
> >
> > insert into emplyoee
> > values ('0001', 'John', '23', '18-JAN-2001', 'Chicago');
>
> If you write the table name correctly there is no problem with this
> statement.
>
> Yet, for numbers you can leave away the ' like this:
>
> insert into employee
> values ( 1, 'John', 23, '18-JAN-2001', 'Chicago');
>
> Depends of course if you require the preceeding '0's.
> You could of course receive this result be a select with some formatting
 of
> the output (e.g. by defining a view).
>
> We use numbers only very rarely for identifiers, because it's not sure,
 that
> a customer won't have non-numeric values for some "numbers".
> E.g. article und purchase or cusomer order "numbers" sometime contain
> non-numeric chars.
>
> > can someone teach me how to
> > 1) set emp_id as a primary key.
>
> 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.

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.

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;

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.

Regards
HJR
> > 2) insert date such as 18-JAN-2001.
>
> No problem with that. See above.
>
> > 3) save all data into a floppy.
>
> What do you mean by this ? What do you want to do with that floppy ?
> Make an export of your single table and then import it to another DB ?
>
> Use the programs exp and imp (sometimes called something like exp80, imp80
> etc. depending on oracle version.
>
> Open a DOS command window (or a shell on Unix), change drive and path as
> required (where the export file should be stored) and type exp
>
> The program will ask you for some things:
> user / schema name
> password
> buffersize (default is OK)
> export file name (usually expdat.dmp, of course you can include directory
> path here, too)
> User or tables. If you select user, the complete schema will be exported.
> So, if you want to export only your new tables chose tables.
> table data -> YES (otherwise only the definition will be exportet).
> compress extents -> default is ok (if you don't wan't to do anything else
> with the file)
> Table or partition to export. Simply enter one table name <ENTER> for
 every
> table you want to export. If finished press <ENTER> again.
>
> Import is similar. Mostly you can use the defaults.
>
> > do you have any suggestion on books, newsletters, web, magazine etc,
> > etc?
>
> You have Oracle ? Don't you have the Online-Documentation ? That would be
 a
> good start.
>
> Otherwise there some forums at www.oracle.com and a lot of homepages of
> oracle developers.
> >
> > please help me out here.
> >
> > i'm a newbie to SQL.
>
> Didn't realize that ;-))
>
> > thanx in advance.
>
> I hope I was able to help a little bit.
>
> With kind regards,
> Harald Henkel
>
>
>
Received on Sat Feb 03 2001 - 09:21:06 CST