Oracle DB for system administrators, part3

11. Lets login as user romeo and check from other console the connection
[oracle@rh-or ~]$ sqlplus romeo/pass1234

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 14 07:31:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

and check from other console the connection
SQL> select username,status, saddr from v$session where username is not null;

USERNAME                       STATUS   SADDR
------------------------------ -------- --------
ROMEO                          INACTIVE 38A9DDA8
SYS                            ACTIVE   38A88A48

SQL>
12. Next step is to create one table.
I will make HR table (usual example) with fields:
NOM - unique identification number of employee
FNAME - his or her first name (cant be empty)
LNAME - his or her last name
JOB - job description
MGR - ID of his or her manager (see NOM)
HIREDATE - date on which this employee is hired. By default will be get system date when record is inserted
SAL - salary of employee, cant be empty
DEPTNO - number of department where employee work, cant be empty (similar to NOM)
SQL> CREATE TABLE hr (
         nom      NUMBER(5) PRIMARY KEY,
       fname      VARCHAR2(15) NOT NULL,
                 lname      VARCHAR2(15),
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         sal        NUMBER(7,2) NOT NULL,
         deptno     NUMBER(3) NOT NULL)
   TABLESPACE TEST01;    2    3    4    5    6    7    8    9   10

Table created.

SQL>

and check the status of this database
SQL> select * from hr;

no rows selected

SQL>
13. The next step will be to fill this table with data
Here are possible two ways (actually many, but i try to simplify and not go indeep)
13.1. Using direct enter of data via INSERT commands
INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (198.0, 'Donald', 'OConnell', to_date('2007-06-21', 'YYYY-MM-DD'), 'SH_CLERK', 2600.0, 124.0, 50.0);


INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (199.0, 'Douglas', 'Grant', to_date('2008-01-13', 'YYYY-MM-DD'), 'SH_CLERK', 2600.0, 124.0, 50.0);
... snip....

but this method can be boring if you have more that 10 records.
13.2. Other way is to use SQL*Loader to create mass import of data
Here is how data looks like:
[oracle@rh-or ~]$ cat EMPLOYEES.csv
NOM,FNAME,LNAME,HIREDATE,JOB,SAL,MGR,DEPTNO
198,Donald,OConnell,2007-06-21,SH_CLERK,2600,124,50
199,Douglas,Grant,2008-01-13,SH_CLERK,2600,124,50
... snip ...

I prepare two files, one shell file to start the program
[oracle@rh-or ~]$ cat EMPLOYEES.sh
sqlldr USERID=romeo/pass1234 CONTROL=EMPLOYEES.ctl skip=1

where sqlldr is the name of the program, USERID is username and password CONTROL is the name of control file and skip is becasue i do not want to insert header ot CSV file in to the table.
This is control file
[oracle@rh-or ~]$ cat EMPLOYEES.ctl
load data
infile 'EMPLOYEES.csv' "str '\r\n'"
append
into table HR
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
           ( NOM CHAR(4000),
             FNAME CHAR(4000),
             LNAME CHAR(4000),
             HIREDATE DATE "YYYY-MM-DD",
             JOB CHAR(4000),
             SAL CHAR(4000),
             MGR CHAR(4000),
             DEPTNO CHAR(4000)
           )

It just define serie of commands to be executed be SQL*Loader as filename from where to get the date, name of the table where the data will be inserted, field separator, format of the date, etc.
And here is the result:
[oracle@rh-or ~]$ sh EMPLOYEES.sh

SQL*Loader: Release 11.2.0.1.0 - Production on Sun Mar 14 08:09:20 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 9
Commit point reached - logical record count 18
Commit point reached - logical record count 27
Commit point reached - logical record count 36
Commit point reached - logical record count 45
Commit point reached - logical record count 54
Commit point reached - logical record count 63
Commit point reached - logical record count 72
Commit point reached - logical record count 81
Commit point reached - logical record count 90
Commit point reached - logical record count 99
Commit point reached - logical record count 107
[oracle@rh-or ~]$
14. And lets check what is content of this table
SQL>  select * from hr;

       198 Donald          OConnell        SH_CLERK          124 21-JUN-07
      2600         50

       199 Douglas         Grant           SH_CLERK          124 13-JAN-08
      2600         50

       200 Jennifer        Whalen          AD_ASST           101 17-SEP-03
      4400         10


REMARK: Data, used in above example is selection from sample database, provided by Oracle as part of Oracle 11g2 software