Oracle 9i

1
Oracle 9i
2
Agenda

• Start and exit SQL Plus (General)
• Start and exit SQL Plus (Tah 1006)
• Syntax
• Create a new user
• Create a new table
• Enter data into a new table
• Export import data

3
Start and Exit SQL Plus

• Start SQL Plus
• Start
• Select Program
• Select Oracle-ORACLE_9i
• Select Application Development
• Select SQL Plus
• Enter your name for User Name (system)
• Enter Enter your password for Password (manager)
• Exit SQL Plus
• Type exit at SQL prompt

4
Start and Exit SQL Plus (Tah 1006)

• Start Database
• Start the computer
• Wait for couple minutes
• Select Oracle-XP_SP1
• Press Enter for password (no password is needed)
• Double clicks the SQL Plus icon
• Enter your name for User Name (system)
• Enter your password for Password (manager)
• Exit SQL Plus
• Type exit at SQL prompt
• Close the database window

5
Syntax - General

• Use semicolon () to terminate a statement
• SQL is not case-sensitive
• SQL statement can be entered into several lines
• Use comma (,) to separate attributes
• Use a pair of single quotes () for any character
  string
• Use a pair of double quotes () for a single
  character string

6
Syntax - General

• Use percentage sign () for a group of wildcard
  characters
• Use underscore (_) for one wildcard character
• Escape character
• Selectfrom.where city like s/_f escape /
• Underscore _ after / will be interpreted as a
  regular meaning
• Subquery must be enclosed in a pair of parentheses

7
Syntax - Name

• Table name or attribute name limited to 30
  characters (characters, number, special symbols
  , _, and )
• Name has to begin with a character
• No blank
• Not case sensitive
• No reserved word

8
Syntax - Data Types

• Characters
• Varchar2(n) max 4,000 characters (ASCII,
  8-digit)
• Char(n) max 2,000 characters (ASCII, 8-digit)
• Nvarchar2(n) as char (Unicode, 16-digit)
• Nchar(n) as char (Unicode, 16-digit)
• Numbers
• Integer number(n)
• Fixed-point number(5,2), 999.99
• Floating-point number

9
Syntax - Data Types

• Date
• DateDD-MMM-YY HHMISS
• Timestamp(fractional seconds precision) with 6 as
  default value
• Large object (LOB) max 4 gigabytes for digitized
  sounds, images, and binary files (BLOB, CLOB,
  BFILE, NCLOB)

10
Syntax - Editor

• L or LIST - displays most current SQL statements
  from buffer
• Line number - changes the current line number to
  the entered line number and shows the listed line
  number
• LIST line number or nn nn - show the indicated
  line number text or between the nn and nn

11
Syntax - Editor

• A or APPEND text - adds the entered text to the
  end of the current line
• I or INPUT text create a new line after the
  current line
• C or CHANG/old text/new text/ - replaces an
  existing text string (old text) in the line with
  a new text string (new text)
• DEL or DELETE - deletes the current line
• START filename execute the contents of the SQL
  Plus command file

12
Syntax - Editor

• SAVE file name - saves the most current SQL
  statements to a file (filename.sql)
• GET file name - retrieves SQL statements from the
  file name (filename.sql)
• R or / - runs the most current statements
• SPOOL file name - saves SQL statements, its
  query, and other results to a file
• SPOOL OFF - terminates SPOOL

13
Spool File - Menu

• SPOOL file
• Select File
• Select Spool
• Select Spool file
• Type drive (such as a)
• Type file name (such as f1)
• Select spool file (.lst)
• SPOOL off
• File
• Spool
• Spool off

14
Save File Menu

• Select File
• Select Save
• Select Create to create a .sql file
• Select Replace to replace a .sql file
• Select Append to append a .sql file

15
Syntax - View Table Structure

• View all of the tables
• SELECT TABLE_NAME FROM USER_TABLES
• SELECT TABLE_NAME FROM ALL_TABLES
• View field definitions of a table
• DESCRIBE table name
• DESCRIBE student
• View all the constraints of a table
• SELECT CONSTRAINT_NAME, TABLE_NAME,
• CONSTRAINT_TYPE
• FROM USER_CONSTRAINTS
• SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
• FROM USER_CONSTRAINTS
• WHERE TABLE_NAME student

16
Oracle Constraint Type Identifier

• Primary key p
• Foreign key r
• Check condition c
• Not null n
• Unique u

17
Error Message

• Type http//otn.oracle.com for the Internet
  address
• Click Search
• Check Oracle Technology Network Documentation
  box
• Type ORA-error messages in the search field
• Click Search icon

18
Create A New Table

• CREATE TABLE student
• (stuid char(5) NOT NULL,
• stuname char(10),
• major char(10),
• credit number(3),
• CONSTRAINT pkstudent PRIMARY KEY
• (stuid))

19
Create A New Table

• CREATE TABLE faculty
• (facid char(5) NOT NULL,
• facname char(10),
• dept char(10),
• rank char(10)
• check (rank in (F,Aso, Ast)),
• CONSTRAINT pkfaculty PRIMARY
• KEY (facid))

20
Create A New Table

• CREATE TABLE class
• (course char(5) NOT NULL,
• facid char(5),
• sched char(10),
• room char(10),
• CONSTRAINT pkclass PRIMARY KEY
• (course),
• CONSTRAINT fkclassfaculty
• FOREIGN KEY (facid)
• REFERENCES faculty
• (facid))

21
Create A New Table

• CREATE TABLE enrollment
• (course char(5) NOT NULL,
• stuid char(5) NOT NULL,
• grade char(10),
• CONSTRAINT pkenroll PRIMARY KEY
• (course, stuid),
• CONSTRAINT fkenrollclass
• FOREIGN KEY (course)
• REFERENCES class (course),
• CONSTRAINT fkenrollstudent
• FOREIGN KEY (stuid)
• REFERENCES student (stuid))

22
Create A New User

• CREATE USER username IDENTIFIED BY password
• CREATE USER tsai IDENTIFIED BY tsai
• GRANT privilege1, privilege2,TO username or role
  name
• GRANT CREATE USER, CREATE TABLE, DROP TABLE TO
  tsai (current users schema user level)
• GRANT role name TO username
• GRANT DBA TO tsai (any users schema DBA level)

23
Delete An User

• REVOKE privilege FROM role name
• REVOKE CREATE USER form tsai
• REVOKE role name FROM username
• REVOKE DBA from tsai

24
Input Data Into A Table

• The ampersand () signal the SQL compiler to
  prompt the user for a value that is then
  substituted into the query
• prompt_variable is the name of the attribute for
  which the user is prompted to specify a value
• Example
• INSERT INTO student VALUES
• (stuid, stuname, major, credit)

25
Load Data Into A Table

• Tsaitest.dat file contains
• 11111,Ching,MIS,100
• 22222,Anderson,MIS,200
• 33333,Martin,ACT,300
• Tsaitest.ctl contains
• LOAD DATA
• INFILE tsaitest.dat
• APPEND
• INTO TABLE student
• FIELDS TERMINATED BY ,
• (stuid, stuname, major, credit)
• The following command should be issued in command
  line
• SQLLDR USERIDtsai/tsai CONTROL tsaitest.ctl
  LOGtsaitest.LOG
• Tsaitest.dat, tsaitest.ctl, and tsaitest.log have
  to be in the same directory such as C

26
Export Data

• Program
• MS-DOS Prompt
• Type exp user name/password (such as exp
  nancy/nancy)
• Specify export file (such as aextf1.dmp where a
  is the drive)
• Answer the questions to execute the expert program

27
Import Data

• Program
• MS-DOS Prompt
• Type imp user name/password (such as imp
  nancy/nancy)
• Specify Import file name (such as aextf1.dmp)
• Answer the questions to execute the import
  program

28
Process for Oracle Homework

• Use notepad to prepare the sql codes for creating
  tables, data, problems, and control files
• Save each control file in an individual file
• Save each table data in an individual file
• Use copy and paste to move the sql codes of
  create table from notepad to oracle then press /
  to run
• Copy 4 data files and control files in the root
  of C drive

29
Process for Oracle Homework

• Type four sqlldr statements at Cgt to load the
  data into the tables (one at the time)
• Use copy and paste to move the sql codes of
  problem from notepad to oracle then press / to
  run
• Use save command to store the solution
• Use spool to save every problem together
• Use get command to get the saved solution and
  press / to run
• Use spool off to turn off the spooling
• Use Word to print the saved spool file

30
Process for Oracle Homework

• Use Imp and exp to import and export records and
  tables