View source

{{FormReward}}

This is an example of how to create a database schema and a user in oracle 9i.

{{FormKeywords
|keywords=create database in oracle, creating users in oracle9i
}}

== the article ==
This simple script illustrates how one can create a user and grant privileges to the user and also how to create a database schema, after creating the database schema we shall generate scripts to create tables in the database and also insert some data into the tables.

== create database ==
<source lang="Sql">--creating a user called 'amutuku' with a password 'amutuku'


CREATE USER amutuku IDENTIFIED BY amutuku;

COMMIT



--Granting privileges to the new user

GRANT CREATE SESSION, CREATE VIEW TO amutuku;

COMMIT



GRANT CONNECT TO amutuku;

COMMIT



GRANT RESOURCE TO amutuku;

COMMIT



--connect to the database as the new user

CONN amutuku/amutuku;


--create a database called 'hr_payroll'

CREATE DATABASE hr_payroll2;


--save the changes 
COMMIT
;</source>[[Language::Sql| ]]
== create_company_table.sql ==
<source lang="Sql">REM START CREATE A TABLE CALLED company

CREATE TABLE company

	(

	COMP_NO NUMBER(5) NOT NULL,

	CNAME VARCHAR2(25) NOT NULL,

	REGNO VARCHAR2(25) NOT NULL,

	CPIN VARCHAR2(25) NOT NULL,

	CNHIF VARCHAR2(25) NOT NULL,

	CNSSF VARCHAR2(25) NOT NULL,

	CLOC VARCHAR2(100) ,

	CADD VARCHAR2(25) ,

	CPOS_CODE VARCHAR2(25) ,

	C_CITY VARCHAR2(25) ,

	C_COUNTRY VARCHAR2(25) ,

	CPHONE VARCHAR2(20) ,

	CCELL VARCHAR2(20) ,

	CFAX VARCHAR2(15) ,

	CMAIL VARCHAR2(25) ,

	PRIMARY KEY (COMP_NO)

	);



COMMIT;

REM END company


REM START CREATE A SEQUENCE CALLED comp_seq


   CREATE SEQUENCE  comp_seq  MINVALUE 1 MAXVALUE 999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;

 

REM END comp_seq



COMMIT;



REM START CREATE A TRIGGER CALLED comp_trg



  CREATE OR REPLACE TRIGGER comp_trg before insert on COMPANY    for each row begin     

	if inserting then       

		if :NEW.COMP_NO is null then          

			select COMP_SEQ.nextval into :NEW.COMP_NO from dual;       

		end if;    

	end if; 

  end;

/

ALTER TRIGGER comp_trg ENABLE;

 

REM END comp_trg



COMMIT;

REM INSERTING DATA INTO THE COMPANY
 TABLE


INSERT INTO company (CNAME,REGNO,CPIN,CNHIF,CNSSF,CLOC,CADD,CPOS_CODE,C_CITY,C_COUNTRY,CPHONE,CCELL,CFAX,CMAIL) 

VALUES ('Activate','45411','a15545g','8478478456','578979','Nairobi, Mombasa Rd, Visions Plaza Ground Flr','4953','00506','Nairobi','Kenya','020-52494','0722-259541','020-2155223','activate@activate.co.ke');



COMMIT;</source>[[Language::Sql| ]]

== create_account_table.sql ==
<source lang="Sql">REM START CREATE A TABLE CALLED account
 THE TABLE HAS A FOREIGN KEY THAT REFERENCES THE PRIMARY KEY IN THE COMPANY TABLE.


CREATE TABLE account

	(

	ACC_ID NUMBER(5) NOT NULL,

	COMP_NO NUMBER(5) NOT NULL,

	BNK_NAME VARCHAR2(30) NOT NULL,

	BNK_BRANCH VARCHAR2(25) NOT NULL,

	ACC_NO VARCHAR2(25) NOT NULL,

	O_DATE DATE ,

	PRIMARY KEY (ACC_ID),

	FOREIGN KEY (COMP_NO) REFERENCES COMPANY (COMP_NO)

	);



COMMIT;



REM END account



REM CREATE A SEQUENCE CALLED acc_seq


   CREATE SEQUENCE  acc_seq  MINVALUE 0 MAXVALUE 99 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;



REM END acc_seq



COMMIT;



REM START CREATE A TRIGGER CALLED acc_trg

  

  CREATE OR REPLACE TRIGGER acc_trg before insert on ACCOUNT    for each row begin     

	if inserting then       

		if :NEW."ACC_ID" is null then          

			select ACC_SEQ.nextval into :NEW.ACC_ID from dual;       

		end if;    

	end if; 

  end;

/

ALTER TRIGGER acc_trg ENABLE;

 

REM END acc_trg



COMMIT;

REM INSERTING DATA INTO THE ACCOUNT
 TABLE


INSERT INTO account (COMP_NO,BNK_NAME,BNK_BRANCH,ACC_NO,O_DATE) 

VALUES (1,'Stanbic','Industrial Area','45421158',to_date('12-JUN-06','DD-MON-RR'));



COMMIT;



INSERT INTO account (COMP_NO,BNK_NAME,BNK_BRANCH,ACC_NO,O_DATE) 

VALUES (1,'KCB','Kencom House','1545454',to_date('20-SEP-04','DD-MON-RR'));



COMMIT;



INSERT INTO account (COMP_NO,BNK_NAME,BNK_BRANCH,ACC_NO,O_DATE) 

VALUES (1,'Barclays','Queensway','1452546',to_date('15-FEB-05','DD-MON-RR'));



COMMIT;</source>[[Language::Sql| ]]

{{FormResource
|resource=Oracle9i
}}
{{FormCategory
|Category=Databases
}}
{{FormSuggestNewCategory}}

{{formComments}}