(Note: the text with green color is the
characters you input.
The text with red color is the paragraph you should pay more attention)
1. How to login to acme?
Use telnet acme.gatech.edu to login to
acme.
2. How to start to use Oracle on acme?
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name:
type in your gt account in prism, your initial password is gtxxxxxDDMM.
gtxxxxx is your account in prism, and DDMM is day and
month of you birthday. After login, you will see:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL>
Now, you should be able to run your SQL commands at this prompt. And
you could try some
SQL commands. Remember the following rules:
1) Use vi or pico to create a file with the extension .sql to denote
that it contains SQL statements to be executed. (If you are not familiar
with vi, I recommend you use pico)
For example, use the following command if you want to create the file
crtables.sql,
$ pico crtables.sql
After you finish editing the file, press Ctrl+x to save and exit.
2) Write the SQL statements needed to create the tables in this file.
For example:
DROP TABLE XCUSTOMERS;
CREATE TABLE XCUSTOMERS (
custno NUMBER(9) NOT
NULL PRIMARY KEY,
custname CHAR(20),
custcity CHAR(20));
DROP TABLE SUPPLIER;
CREATE TABLE SUPPLIER (
suppno NUMBER(9) NOT NULL PRIMRY
KEY,
suppname CHAR(20),
suppcity CHAR(20) );
3) run SQLPLUS to execute the sql script file.
For example:
SQL> @crtables.sql
or SQL> start crtables.sql
4)Now your tables should be created. You can use "desc" command in SQLPLUS
to query the table you created.
For example,
SQL> desc xcustomers
Name
Null?
Type
------------------------------- -------- ----
CUSTNO NOT NULL
NUMBER(9)
CUSTNAME
CHAR(20)
CUSTCITY
CHAR(20)
SQL> desc supplier
Name Null? Type
------------------------------- -------- ----
SUPPNO NOT NULL NUMBER(9)
SUPPNAME CHAR(20)
SUPPCITY CHAR(20)
If there is error saying "no object exist", that means the tables fails to be created (or you have no synonyms if different users use the tables).
Now, you should have created your tables.
Suggestion: Create
the tables referenced at first, then create the referencing tables (with
foreign keys). You can sort all your tables according to their reference
relation to decide the order to be created.
For example,
CREATE TABLE employee
(empno NUMBER
PRIMARY KEY,
ename
VARCHAR2(10) NOT NULL,
job VARCHAR2(9),
mgr NUMBER
REFERENCES employee(empno),
hiredate
DATE,
deptno
NUMBER(2) NOT NULL,
FOREIGN
KEY deptno REFERENCES department(deptno));
In the above example, the employee table references the table department.
So you should create department table before you create the table employee.
4. How to populate an oracle DB
There are several ways to populate the oracle DB.
1) Use SQL script file in SQLPLUS.
a) Use vi or pico to create a file with the extension .sql to denote
that it contains SQL statements to be executed.
For example,
$ pico data.sql
After you finish editing the file, press Ctrl+x to save and exit.
b) Write the SQL statements needed to populate the tables in this file.
For example:
INSERT INTO SUPPLIER VALUES(100,'BN','Atlanta');
INSERT INTO SUPPLIER VALUES(101,'Amazon','Atlanta');
INSERT INTO SUPPLIER VALUES(102,'Varisity Bookstore','New York');
INSERT INTO XCUSTOMERS VALUES(1000,'Guy, Buddy','Chicago');
INSERT INTO XCUSTOMERS VALUES(1001,'Rush, Otis','Chicago');
INSERT INTO XCUSTOMERS VALUES(1002,'Clapton, Eric','London');
INSERT INTO XCUSTOMERS VALUES(1003,'Green, Peter','London');
INSERT INTO XCUSTOMERS VALUES(1004,'Bloomfield, Mike','Chicago');
INSERT INTO XCUSTOMERS VALUES(1005,'King, Freddie','Dallas');
INSERT INTO XCUSTOMERS VALUES(1006,'King, B.B.','Memphis');
c) Run the sql script file in SQLPLUS.
For example:
SQL> @data.sql
d) You can use select statement to check whether populating is OK.
For example:
SQL> select * from supplier;
SUPPNO SUPPNAME
SUPPCITY
---------- -------------------- --------------------
100
BN
Atlanta
101
Amazon
Atlanta
102
Varisity Bookstore New York
SQL> select * from xcustomers;
CUSTNO CUSTNAME
CUSTCITY
---------- -------------------- --------------------
1000
Guy, Buddy
Chicago
1001
Rush, Otis
Chicago
1002
Clapton, Eric
London
1003
Green, Peter
London
1004
Bloomfield, Mike Chicago
1005
King, Freddie
Dallas
1006
King, B.B.
Memphis
7 rows selected.
Suggestion: Populate
the tables referenced at first, then populate the referencing tables (with
foreign keys).
If there are errors, check the UNIQUE, REFERENCE
constraint.
In this part, pay attention to the format of
DATE. The default format of DATE is "DD-MON-YYYY"
If you want to get the current date and time, use sysdate function.
If you want to generate a number which can increase automatically,
create a sequence. (For example, it can be used to generate order#). Sequence
is similar as autonumber in SQL SERVER.
2) Populate in Pro*C program (Similar as 1) )
3) Use SQL Loader (Read the oracle document about SQL loader)
You can only use 1) in this project.
5. How to query an oracle DB
1) Query user-defined table
You can use select statement to query the user-defined table.
For example:
SQL> select * from supplier;
SUPPNO SUPPNAME
SUPPCITY
---------- -------------------- --------------------
100
BN
Atlanta
101
Amazon
Atlanta
102
Varisity Bookstore New York
2) Query system catalog tables
6. How to write a Pro*C program?
What is Pro*C? Pro*C is the Oracle pre-compiler for the C programming
language. It allows you to write embedded SQL statements in your C program
-- which is supposed to make it easier to write database programs.
Here (ps,
pdf)
is the instruction of Pro*C.
Here is a sample
Pro*C program.
7. How to make a Pro*C program?
1) Follow the description in How to start to use Oracle
on acmeb? to set up the oracle environment.
2) Use proc.mk
to make your .pc file.
For example, if your .pc file name is "sample.pc",
$ make -f proc.mk build EXE=sample OBJS="sample.o"
After this, you should generate "sample.c" and "sample" files, try
to run "sample"(Note: it doesn't run under SQLPLUS).
You can compile multiple .c files into an executable by specifying them
in the OBJS section. For example, if you want to make sample1.pc sample2.pc
and sample3.pc,
$ make -f proc.mk build EXE=sample OBJS="sample1.o
sample2.o sample3.o"
Here are links to the makefile proc.mk and to a sample Pro*C program
Suggestion: You can use ! to execute the
shell command in SQLPLUS.
For example: SQL> !pico
sample.pc
You should download proc.mk file instead of using
copy and paste.