ORACLE Here i come ...

hari jumat 7 November 2008, berhubung ngak terlalu sibuk dengan kerjaan gw nyoba untuk "mengenal" lebih dalam ORACLE Database. Kemaren juga gw udah nanya server mana yang bisa gw obok-obok buat belajar dan diberikan TNSnamesnya. Sebenernya dari kemaren udah pengen banget nyoba, tapi ngak bisa konek ke server. ngak tau masalahnya kenapa... mungkin VPNnya yang dodol.

Yang pertama gw liat adalah buku oracle disamping gw -hehehe, bekas peninggalan orang yang sekarang ada dimeja gw-. Gw baca2, trus gw implementasiin di Oracle. Gw konek ke Oraclenya pake TOAD. Hal pertama kali yang gw lakuin di ORACLE is create table. Well, i created two tables this morning. One is Employees and the other is Departments -gw liat dibuku latihan-.

Setelah gw coba, emang ngak sama banget sama SQL Server. Ada yang error, tapi gw cari solusinya diinternet ... ya ngak beda-beda jauh lah.

CREATE TABLE EMPLOYEES
(
employee_id INT NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NULL,
last_name VARCHAR(20) NULL,
email VARCHAR(25) NULL,
phone_number VARCHAR(25) NULL,
hire_date date NOT NULL,
job_id VARCHAR(20) NULL,
salary DECIMAL(29,2) NULL
)

CREATE TABLE DEPARTMENTS
(
department_id INT NOT NULL PRIMARY KEY,
department_name VARCHAR(50) NULL,
manager_id INT NULL,
location_id INT NULL,
CONSTRAINT manger_id_fk FOREIGN KEY (manager_id) REFERENCES EMPLOYEES(employee_id)
)

Setelah gw buat table nya di ORACLE, kemudian gw buat SEQUENCE. kalo di SQL Server mungkin sama dengan IDENTITY. Tapi bedanya adalah, SEQUENCE dituliskan saat perintah INSERT sedangkan identity tidak. Selanjutnya adalah membuat data... hufffff ... ini nih yang paling sulit kalo buat database dari awal.

Here's the script :

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'RUDI','WIJAYA',
'rudi.wijaya@cne.com','0822882823','1 JANUARY 2001','ADM STAFF',2000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'PETER','PARKER',
'peter.parker@cne.com','08177237183','1 JANUARY 2001','ADM Manager',5000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'RAYMOND','BROWN',
'raymond.brown@cne.com','08177237183','18 MARCH 1998','Mkt Staff',2000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'CLARK','KENT',
'clark.kent@cne.com','08189137136','1 JANUARY 1996','Mkt Manager',5000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'SAM','WINCHESTER',
'sam.winchester@cne.com','0812123216','13 FEB 2000','Ship Staff',2000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'DEAN','WINCHESTER',
'dean.winchester@cne.com','0812123216','13 FEB 2000','Ship Manager',5000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'BRUCE','WAYNE',
'bruce.wayne@cne.com','0812123216','13 FEB 2000','IT Staff',2000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'JHON','WINCHESTER',
'jhon.winchester@cne.com','08151814714','4 July 2000','IT Manager',5000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'MARCELO','SALAS',
'marcelo.salas@cne.com','02199313718','4 July 2000','Sales Staff',2000000);

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'IVAN','ZAMORANO',
'ivan.zamorano@cne.com','0831736183','17 August 1988','Sales Manager',10000000)

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'DAVOR','SUKER',
'davor.suker@cne.com','08131316641','5 August 2000','Executive Staff',2000000)

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'ROBERTO','MANCINI',
'roberto.mancini@cne.com','0821863163','10 December 1998','Executive Manager',6000000)

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'GORAN','PANDEV',
'goran.pandev@cne.com','08227313516','15 December 2000','Accounting Staff',2000000)

INSERT INTO EMPLOYEES
VALUES (EMP_SEQUENCE.NEXTVAL,'MAURO','ZARATE',
'mauro.zarate@cne.com','08158731632','20 December 1998','Accounting Manager',5000000)

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'Administration',5,1700);

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'Marketing',7,1600);

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'Shipping',9,1500);

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'IT',11,1400);

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'Sales',13,1300);

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'Executive',15,1200);

INSERT INTO DEPARTMENTS
VALUES (DEP_SEQUENCE.NEXTVAL,'Accounting',17,1100);



Masalah muncul disini... ketika gw mo insert semua datanya langsung... Error terus. Errornya : SQL command not properly ended. hehehehe, awalnya gw bingung banget.. trus gw tambahin titik koma diakhir, eh malah errornya berubah. Errornya : Invalid identifier. Waaaaks... kenapa ya ? ya udah gw tanya aja sama DBAnya.... eh jawabannya simple.. "Di Toad ngak bisa insert langsung banyak gitu, harus satu persatu" .... weks... tapi akhirnya dia ngasih tau cara cepet buat insert banyak pake toad, caranya adalah Execute Via SQL*Plus. hehehehe ...

Akhirnya database pertama gw jadi, tinggal belajar selanjutnya neh ..... Come on, Lets Go to Oracle DBA huahuahua !!! (I Hope)