Enterprise Integration & Modeling: Metadatabase Research Home
 home || MDB Research | Virtual Lab | Case Tool | Downloads | Publications | Researchers | Links

Studio I.4 Oracle Database

(Updated: February 15, 2007)

The objective of this studio is to introduce you to the basic SQL processing on Oracle database server (Part I) and PostgreSQL (Part II). The materials given below should enable you to implement a database in Oracle and PostgreSQL to do some basic processing with it.

Part I

I. Setting Up Oracle on RCS:

You can do it via a Unix workstation or from a PC using SSH or SecureCRT, respectively. Connect to rcs-sun.rpi.edu and login with your RCS ID username and password. Then at the command prompt (your prompt may be different), do the following:

1. Setup oracle and get Kerberos ticket by entering

   source /campus/oracle/10.2.0/sun4x_58/oraenv

(Note the space after the command "source" in the above input.)

2. After this command the following output will be displayed.

   Setting up Oracle environmental variables..

   Getting Kerberos ticket...

   Kerberos Utilities for Solaris: Version 10.2.0.1.0 - Production on 26-JAN-2007 14:17:19

   Copyright (c) 1996, 2004 Oracle.  All rights reserved.

   Password for <RCSuserid>@RPI.EDU:

3. Enter your RCS password at the above prompt. You will be returned to the Unix prompt.

4. To run SQLPLUS, enter

   sqlplus /@ora10

(Note the space after the command "sqlplus" in the above input.)

5. After this command the following output will be displayed:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 15 16:05:33 2007

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

6. SQL> (Now, you've started !!!)

You will be ready to enter SQL commands at the prompt.

7. To get on-line help:

SQL> help

II. Creating an Oracle Database

You will use SQLplus to create the schema for your database. There are two ways to do this: (1) you batch-process a text file that contains the schema creation commands and (2) you interactively enter these commands. If you have used the IBMS CASE tool to create an Oracle 7 schema, then you are in the first situation. The following procedure assumes that you have a schema file <filename.sql>.

1. Put the schema file (you pick a name in the form of <filename.sql>) in your home directory by using FTP or RCS logon.

2. Type this command after the system prompt:

SQL> @<filename.sql>

for example:

SQL>@bank.sql

Then, you get the following messages.

Table created.
Table created.
....

* Common problems

a. If you get the following error message,

CREATE TABLE date 
             *
ERROR at line 1:
ORA-00903: invalid table name

it means that you have a invalid table name. Because 'date' is a reserved word on Oracle, you can not use it as your table or column name. The word 'time' is same. If you have those words, modify your schema file <filename.sql> by using an editor and do it again.

b. If you get the following error message,

CREATE TABLE customer 
              *
ERROR at line 1:
ORA-00955: name is already used by an existing object

it means that you have the table already. If the structures of the exist table and the new table are same, you can ignore that error messages. If you want to change the structure of the exist table, your can use 'alter table' command, or 'drop table' command and create table again.

3. Alternatively, you would type in "create table..." commands in SQLplus to create all base tables.

4. You can check the table list which you created by using following command

SQL> select * from tab;

See the structure of a particular table:

SQL> desc <table_name>;

III. Using Oracle Through SQLplus

You are now able to enter data into the database and make some use of it. The following commands are basic to your job:

1. Enter data into a table:  Use the following command.

INSERT INTO <table_name> [(<column_name> [, <column_name>]...)]    
            { VALUES (<expression> [,<expression] ...) | <query>}

For detail syntax, use on line help like as : SQL>help insert

Example:

SQL> desc customer; 
 Name                            Null?    Type
 ------------------------------- -------- ----
 CID                             NOT NULL NUMBER
 CUST_DOB                              VARCHAR2(8)
 CUST_NAME                             VARCHAR2(20)
 CUST_ADDR                             VARCHAR2(20)
 CUST_TEL_NO                           VARCHAR2(11)
SQL> insert into customer values(1234,'08/22/68','David','Troy','277-7777');    
1 row created.

1) Note, you must enter these values in the order that they are defined in the table structure. Values are separated by a "," and should be entered consecutively.

2) If you want insert values to some of columns, not whole columns, you can specify the columns names.

3) Single quotation marks must be used to indicate a character string (e.g., 'RPI, Troy, NY 12180').

4) You can make a SQL file and run it. For example, you can make 'customer.sql' file like as

insert into customer values(1235,'05/12/68','Pinky Winky','Troy','271-7777');    
insert into customer values(1236,'09/29/69','La La','Troy','272-7777');
insert into customer values(1237,'03/22/73','Dipsy','Troy','273-7777');

And then, you can execute the file by using this command

SQL>@customer.sql

* Common problems

a. Problems of Inserting records

While you try to insert some records to your table, if you get the error messages such as

SQL> insert into loan(loan_no,branch_no,cid) values(7862,1255,3333); 
insert into loan(loan_no,branch_no,cid) values(7862,1255,3333)
           *
ERROR at line 1:
ORA-02291: integrity constraint (KIMB4.SYS_C0098061) violated - parent key not found

The reason is probably a Integrity Rule.
Remember you have 4 INTEGRITY RULES with your normalized database design.
Spcially, Referential integrity means that the database must not contain any unmatched foreign key values.
You need to insert records to the referrenced tables FIRST.
 

b. Handling Date data type.

DATE is a system variable in Oracle, representing a data type for dates. You may not use any system variable name as your table name or column name. If you have them in your schema, you must modify. The format for DATE is, for example, "16-APR-99" (use the first three letters of a month for the month). Or you can handle it by using to_date() or to_char() oracle functions.

For example,

SQL>Insert into Surgery values('s1111','p1111','16-APR-99','kkk','lll');    
SQL>Insert into Surgery values('s1111','p1111',to_date('990416','yymmdd'),'kkk','lll');
SQL>Select surgeon,x_date from Surgery;
SQL>Select surgeon,to_char(x_date,'yymmdd') from Surgery;

You can get more information about to_date and to_char by using on-line help on Oracle. There is also to_number() function.

SQL>help to_date 
SQL>help to_char

2. delete a row from a table:

delete from <table_name> where <condition>; 

For example, if the day-rep table has the following row and you want to delete it:

   ACCT_NO REASON_CODE X_DATE   X_TIME    
---------- ----------- -------- ----------
      4445         10 3/3/33   12:00  

SQL> delete from day_rep where ACCT_NO=4445;
1 row deleted.

3. Update values in a table (or a view):

update <table_/view_name> set column=<value> where <condition>;  

For example, in the inquiry table shown below, you want to change the value of branch_no from 1234 to 1235.
(NOTE: even though under some circumstances Oracle allows updates and inserts in views they are not recommended. Inserts and updates should be performed only on tables.)

   ACCT_NO ACCT_TYP  BRANCH_NO        CID    BALANCE CUST_DOB CUST_NAME
---------- -------- ---------- ---------- ---------- -------- --------------------
      4446 Savings        1234       8888       5000 1/1/72 Patrick
SQL> update inquiry set branch_no=1235 where acct_no=4446;
1 row updated.

4. Create a query:

select * from <view_/table_name> where <condition>;

For example,

SQL> select * from REPORT62 where ACCT_NO=4446 and balance<10000;

5. Create a view:

create view <view_name> (all the variables to be included in the view) from <all the table_names that supply these variables> where <conditions>;

For example:

SQL> create view report1
(branch_no,branch_name,bracnh_addr,mgr_name,cust_name,cid,acct_type,
acct_no,amount,x_date,x_time,x_type,reason_code,action_code,remarks)
as select
branch.branch_no,branch.branch_name,branch.branch_addr,branch.mgr_name,
customer.cust_name,customer.cid,account.acct_type,account.acct_no,
Dept_Withdrl.amount,x_date.x_date,x_time.x_time,Dept_Withdrl.x_type,
Reason.reason_code,exception.action_code,exception.remarks
from branch,customer,account,Dept_Withdrl,x_date,x_time,Reason,exception
where customer.cid=account.cid and
      account.acct_no=Dept_Withdrl.acct_no
and
      Dept_Withdrl.x_date=x_date.x_date and
      Dept_Withdrl.x_time=x_time.x_time and
      account.acct_no=exception.acct_no and
      exception.reason_code=Reason.reason_code and
      account.branch_no=branch.branch_no;
or,
SQL> create view report1
(branch_no,branch_name,bracnh_addr,mgr_name,cust_name,cid,acct_type,
acct_no,amount,x_date,x_time,x_type,reason_code,action_code,remarks)
as select
b.branch_no,b.branch_name,b.branch_addr,b.mgr_name,
c.cust_name,c.cid,a.acct_type,a.acct_no,
d.amount,d.x_date,d.x_time,d.x_type,
e.reason_code,e.action_code,e.remarks
from branch b,customer c,account a,Dept_Withdrl d,exception e
where c.cid=a.cid and
      a.acct_no=d.acct_no and
      a.acct_no=e.acct_no and
      a.branch_no=b.branch_no;

* Note that b,c,a,d,and e are alias of table names branch,customer,account,Dept_Withdrl, and exception.

6. Review all views created:

select * from tab;

will give you all view_names as well as table_names.

select * from tab where tabtype='VIEW';

will give you all view_names. Please note the singular 'tab' in the command. If you use 'tabs', you will get detailed listing of tables. Also VIEW needs to be CAPITALIZED.

desc <view_name>;

will give you the structure of a view.

7. Display a view or table:

select * from <view_/table_name>; 
SQL>select * from report1;

8. Drop a table or a view:

drop table <table_/view_name> cascade constraints; 

* Note : The "cascade constraints" phrase is not necessary when the table you want to drop does not have any referential constraints.

IV. Using a Command File of SQLPlus to Format Your Report

You may use the following steps to produce a finished report from your query results.

1. Create a command file:

Output Results to the Terminal

A command file is a file that stores one or more commands. You can create a command file using any of your favorite editor. You can also specify the format of a report using a command file.

For example, you may specify your report in the command file named "R_DESKTOP" as follows.

set autocommit on; 
set linesize 70 /*set the width of page*/
set pagesize 50 /*set number of lines per page*/
set newpage 0


/*set number of line between the beginning of
each page and the top title*/
ttitle center 'A LIST OF ALL DESKTOPS'


/*set top title at the center of page
/


/*Format column heading*/
column vname heading 'Vendor'


/*define column heading for attribute vname*/
column vname format a25


/*specify the output format of the column to be 25*/
column vname truncated


/*truncate the string at the end if the content
of vname is longer than 25 characters */

column bname heading 'Brand'
column bname format a15
column bname truncated
column descp heading 'Description'
column descp format a15
column descp truncated
column pname heading 'Component'
column pname format a10
column pname truncated
select vname, bname, descp, pname from item;
set autocommit off

For HTML Output

The following commands executed on the command line will spool the results in HTML format to output.html. This can also be also be included in command file. See below.

SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL output.html
select vname, bname, descp, pname from item;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON

For more information on HTML output, see http://www.oracle-base.com/articles/9i/SQLPlusWebReports.php

2. Run a command file:

You may run a command file using @command_file_name. For example, use the following command to run a command file named "R_DESKTOP".

SQL>@R_DESKTOP 

3. Storing and printing results from a command file:

To store the results in a file (still display them on the screen), insert the SPOOL command at the beginning of command file.

Command format: spool file_name

For example, use the command "spool temp" to store the results in file named "temp".
Note: If you do not follow the filename with a period and an extension, SPOOL adds a default file extension "LST".

To turn spooling off, insert the command SPOOL OFF into a command file at an appropriate position

To send the results to a Printer, insert the command "SPOOL OUT" at the end of a command file.
Note: SPOOL OUT does not delete the spool file after printing.

V. Tips

You may use the following commands to make your job a little easier. They are all issued in the SQLplus module.

1. Adjust the display option:

You can widen the line length and page size of the screen display by using following commands

SQL> set line 200; 
SQL> set pages 100;

2. Make a report :

You could use the Report module to generate a report. However, a "quick-n-dirty" way to print out some results is to use the following procedure, through an example:

SQL> spool myfile; 
SQL> select * from tab;
SQL>desc <table/view name>;
SQL> select * from <table/view name>;
SQL> Anything what you want to include in your report;
SQL> spool off;

The above commands spool all things which you typed and the results  in between the two spool commands, to a file named "myfile.lst". You can view this file by using a editor like as vi,emacs, WordPad, WinWord, or any other editor. Then, you can edit and print it.

3. Using unix commands on Oracle:

From the SQL prompt you can run all the unix commands by preceding them with "!". For example,

SQL>!vi myfile.lst

will allow you to view and edit myfile.lst using vi without exiting Oracle.

4. Change the default editor to another (such as vi):

define _EDITOR="<your_choice>"; 

for example:

define_EDITOR="vi"; 

You may use the define command to alter some other system defaults. To run the file you saved, use the above procedure for schema creation.

5. Switch on the scrolling function for screen display:

set pause on 

6. Get help:

help <name of the command>

7. See all table structures that you have created:

select * from tabs; 

8. See all table names:

select table_name from tabs; 

9. See the structure of a particular table:

desc <table_name>; 

10. Some useful SQL commands:

Detail information can be reached by on-line help, e,g SQL>help count

COUNT,SUM,AVG,MAX,MIN 
SORT BY, ORDER BY, GROUP BY,
DISTINCT, UNIQUE,
IN,INTERSECT, MINUS,UNION

11. How to delete all created tables:

Use a command file to delete all created table.
For example, suppose you have created three tables named:
CUSTOMER, CORDER, and ORDER_DETAIL.
And you want to delete all of these tables by using a single command.
What you need to do in the first step is to create a command file in which contains following three SQL commands.

DROP TABLE CUSTOMER; 
DROP TABLE CORDER;
DROP TABLE ORDER_DETAIL;

For more details on how to create and run a command file, See Section I.IV.

Part II

We repeat Part I, but use PostgreSQL as the database management systems and make the adjustments in commands where necessary.

II.I Setting Up PostgreSQL

Follow the instructions in Studio I.1 to get PostgreSQL running on your Windows machine. From this point on you will access the database through the PostgreSQL terminal, psql.

II.II Creating an PostgreSQL Database

You will use psql to create the schema for your database. There are two ways to do this: (1) you batch-process a text file that contains the schema creation commands and (2) you interactively enter these commands. If you have used the IBMS CASE tool to create an Oracle schema, then you are in the first situation. The following procedure assumes that you have a schema file <filename.sql>. Since the <filename.sql> file contains an Oracle schema, a few, but slight changes must be made to make this compatible with PostgreSQL, which can be accomplished manually. Make the following changes to the hospital.sql file in a text editor such as Notepad:

  • Remove all Oracle REMARK statements
  • Remove all AUTOCOMMIT statements
  1. Put the schema file (you pick a name in the form of <filename.sql>) in the run-time directory for PostgreSQL, typically C:\Program Files\PostgreSQL\8.0\bin)
  2. Type this command after the system prompt:
C:\> createdb database_name
C:\> psql database_name < filename.sql

for example:

C:\>psql database_name < bank.sql

Then, you get the following messages.

Table created.
Table created.
....
  1. Alternatively, you would type in "create table..." commands in the psql terminal to create all base tables.
  2. You can check the table list which you created by using following command. Note database_name is the name of the current working database.
database_name=# \d;

See the structure of a particular table:

database_name=# \d <table_name>;

II.III Using PostgreSQL Through psql

You are now able to enter data into the database and make some use of it. The following commands are basic to your job:

1. Enter data into a table:  Use the following command.

database_name=# INSERT INTO <table_name> [(<column_name> [, <column_name>]...)]                    
{ VALUES (<expression> [,<expression] ...) | <query>}

For detail syntax, use on line help like as :

database_name=#\? or \h

Example:

database_name=# \d customer; 
 Column                          Type    Modifiers
 ------------------------------- -------- ----
 CID                             NOT NULL INTEGER
 CUST_DOB                              CHARACTER VARYING(8)
 CUST_NAME                             CHARACTER VARYING(20)
 CUST_ADDR                             CHARACTER VARYING(20)
 CUST_TEL_NO                           CHARACTER VARYING(11)
database_name=# insert into customer values(1234,'08/22/68','David','Troy','277-7777');    
INSERT 0 1.
  1. Note, you must enter these values in the order that they are defined in the table structure. Values are separated by a "," and should be entered consecutively.
  2. If you want insert values to some of columns, not whole columns, you can specify the columns names.
  3. Single quotation marks must be used to indicate a character string (e.g., 'RPI, Troy, NY 12180').
  4. You can make a SQL file and run it. For example, you can make 'customer.sql' file like as
insert into customer values(1235,'05/12/68','Pinky Winky','Troy','271-7777');    
insert into customer values(1236,'09/29/69','La La','Troy','272-7777');
insert into customer values(1237,'03/22/73','Dipsy','Troy','273-7777');

And then, you can execute the file by using this command. Note that this file must be in the current working directory.

database_name=# \i customer.sql

* Common problems

a. Problems of Inserting records

While you try to insert some records to your table, if you get the error messages such as

database_name=# insert into loan(loan_no,branch_no,cid) values(7862,1255,3333); 
ERROR: duplicate key violates unique constraint "loan_no"

The reason is probably a Integrity Rule.
Remember you have 4 INTEGRITY RULES with your normalized database design.
Spcially, Referential integrity means that the database must not contain any unmatched foreign key values.
You need to insert records to the referenced tables FIRST.
 

b. Handling Date data type.

DATE is a system variable in PostgreSQL, representing a data type for dates. You may not use any system variable name as your table name or column name. If you have them in your schema, you must modify. The format for DATE is, for example, "2005-08-05". Or you can handle it by using NOW() function.

For example,

database_name=# Insert into Surgery values('s1111','p1111','2005-08-05','kkk','lll');    
database_name=# Insert into Surgery values('s1111','p1111',NOW(),'kkk','lll');
database_name=# Select surgeon,x_date from Surgery;


c. delete a row from a table:

database_name=# delete from <table_name> where <condition>; 

For example, if the day-rep table has the following row and you want to delete it:

   ACCT_NO REASON_CODE X_DATE   X_TIME    
---------- ----------- -------- ----------
      4445         10 3/3/33   12:00  

database_name=# delete from day_rep where ACCT_NO=4445;
DELETE 1.

d. Update values in a table (or a view):

database_name=# update <table/view_name> set column=<value> where <condition>;  

For example, in the inquiry table shown below, you want to change the value of branch_no from 1234 to 1235.
(NOTE: even though under some circumstances PostgreSQL allows updates and inserts in views they are not recommended. Inserts and updates should be performed only on tables.)

   ACCT_NO ACCT_TYP  BRANCH_NO        CID    BALANCE CUST_DOB CUST_NAME
---------- -------- ---------- ---------- ---------- -------- --------------------
      4446 Savings        1234       8888       5000 1/1/72 Patrick
database_name=# update inquiry set branch_no=1235 where acct_no=4446;
UPDATE 1.

e. Create a query:

database_name=# select * from <view/table_name> where <condition>;

For example,

database_name=# select * from REPORT62 where ACCT_NO=4446 and balance<10000;

f. Create a view:

create view <view_name> (all the variables to be included in the view) from <all the table_names that supply these variables> where <conditions>;

For example:

database_name=# create view report1
(branch_no,branch_name,bracnh_addr,mgr_name,cust_name,cid,acct_type,
acct_no,amount,x_date,x_time,x_type,reason_code,action_code,remarks)
as select
branch.branch_no,branch.branch_name,branch.branch_addr,branch.mgr_name,
customer.cust_name,customer.cid,account.acct_type,account.acct_no,
Dept_Withdrl.amount,x_date.x_date,x_time.x_time,Dept_Withdrl.x_type,
Reason.reason_code,exception.action_code,exception.remarks
from branch,customer,account,Dept_Withdrl,x_date,x_time,Reason,exception
where customer.cid=account.cid and
      account.acct_no=Dept_Withdrl.acct_no
and
      Dept_Withdrl.x_date=x_date.x_date and
      Dept_Withdrl.x_time=x_time.x_time and
      account.acct_no=exception.acct_no and
      exception.reason_code=Reason.reason_code and
      account.branch_no=branch.branch_no;
or,
database_name=# create view report1
(branch_no,branch_name,bracnh_addr,mgr_name,cust_name,cid,acct_type,
acct_no,amount,x_date,x_time,x_type,reason_code,action_code,remarks)
as select
b.branch_no,b.branch_name,b.branch_addr,b.mgr_name,
c.cust_name,c.cid,a.acct_type,a.acct_no,
d.amount,d.x_date,d.x_time,d.x_type,
e.reason_code,e.action_code,e.remarks
from branch b,customer c,account a,Dept_Withdrl d,exception e
where c.cid=a.cid and
      a.acct_no=d.acct_no and
      a.acct_no=e.acct_no and
      a.branch_no=b.branch_no;

* Note that b,c,a,d,and e are alias of table names branch,customer,account,Dept_Withdrl, and exception.

g. Review all views created:

database_name=# \dv;

h. Display a view or table:

database_name=# select * from report1;

i. Drop a table or a view:

database_name=# drop table <table/view_name> cascade; 

* Note : The "cascade" phrase is not necessary when the table you want to drop does not have any referential constraints.

IV. Using a Command File of PostgreSQL to Format Your Report

You may use the following steps to produce a finished report from your query results.

For HTML Output

The following commands executed on the command line will spool the results in HTML format to output.html. This can also be also be included in command file. See below.

SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL output.html
select vname, bname, descp, pname from item;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON

2. Run a command file:

You may run a command file using \i command_file_name. For example, use the following command to run a command file named "R_DESKTOP".

database_name=# \i R_DESKTOP 

3. Using unix commands on PostgreSQL:

From the prompt you can run all the unix commands by preceding them with "!". For example,

database_name=# !vi myfile.lst

will allow you to view and edit myfile.lst using vi without exiting Oracle.

4. Change the default editor to another (such as vi):

define _EDITOR="<your_choice>"; 

for example:

define_EDITOR="vi"; 

You may use the define command to alter some other system defaults. To run the file you saved, use the above procedure for schema creation.

5. Switch on the scrolling function for screen display:

set pause on 

6. Get help:

database_name=# \h <SQL COMMAND>

7. See all table structures that you have created:

database_name=# \dt

8. See all table names:

database_name=# \dT

9. See the structure of a particular table:

database_name=# \d <table_name>; 

10. Some useful SQL commands:

Detail information can be reached by on-line help

COUNT,SUM,AVG,MAX,MIN 
SORT BY, ORDER BY, GROUP BY,
DISTINCT, UNIQUE,
IN,INTERSECT, MINUS,UNION

11. How to delete all created tables:

Use a command file to delete all created table.
For example, suppose you have created three tables named:
CUSTOMER, ORDER, and ORDER_DETAIL.
And you want to delete all of these tables by using a single command.
What you need to do in the first step is to create a command file in which contains following three SQL commands.

DROP TABLE CUSTOMER; 
DROP TABLE ORDER;
DROP TABLE ORDER_DETAIL;


database_name=# \i COMMAND.SQL
 

viu.eng.rpi.edu is hosted by Professor Cheng Hsu.
Rensselaer Polytechnic Institute
Department of Industrial and Systems Engineering (formally Decision Sciences & Engineering Systems)
110 8th St., Center for Industrial Innovation, Room 5123, Troy, NY 12180-3590

Copyright © 1997-2016. MetaWorld, Nothing on this site may be commercially used without written consent.

Valid XHTML 1.0! Valid CSS!