Oracle 8i Editions

1
Oracle 8i Editions

• Lite Edition Smallest database requirement. For
  mobile computers (e.g., laptop)
• Personal Edition For desktop computers and in a
  situation of single user.
• Standard Edition For standard business usage
• Enterprise Edition For high volumes of
  transaction and high level of availability

2
Oracle 8i Main Utilities

• SQLPlus A query editor, lack full windows
  support.
• Enterprise Manager A GUI provides most database
  administrating functions.
• Navigator Personal Edition version of Enterprise
  Manager.

3
Oracle Database Architecture I

• Table the basic unit of the storage of data in
  an Oracle database.
• View a visual table, consisting of a
  pre-compiled SQL SELECT statements.
• Index a collection of the index key values.
• Cluster physically make the records linked
  together.

4
Oracle Database Architecture II

• Trigger a defined rule to maintain the data
  integrity.
• Stored Procedure a pre-compiled (p-code) program
  written in PL/SQL. Can be either a Procedure or a
  Function
• Package a single object to group
  procedures/functions and the variables.
• Sequence a persistent object that can generate
  unique numbers according to the prescribed order.

5
Oracle Database Architecture III

• Synonyms An alias name for a database object.
  Purpose same object with different names to meet
  the application needs.
• Tablespace An integrated unit to hold the data.
  Normally it contains several physical files with
  a collection of tables and indexes.
• Schema A logical view of the database that
  represents a set of database objects can be
  accessed by a user.

6
Oracle Main Data Types I

• Char Fixed length string up to 2000 bytes
• Nchar Fixed unicode string
• Varchar2 Variable length string up to 4000
  bytes.
• Nvarchar2 Variable length unicode string
• Long Long string up to 2 GB

7
Oracle Main Data Types II

• Decimal/ Float/Number Decimal data up to 38
  digits of precision
• Number(p,s) p is precision (number of digits),
  s is scale (number decimal digits)
• Decimal Doesnt accept precision or scale
• Float(b) b is the number of binary digits
• Date
• Rowid A unique identifier of rows in a database.
  Note not just in a table.

8
Using Personal Navigator

• Tables
• Views
• Users Add new user
• Roles Add new role

9
Accessing SQLPlus

• Enter SQLPlus Windows version
• Enter SQLPlus DOS version (run sqlplus.exe)
• Useful Access Pair
• scott/tiger
• system/manager

10
Using SQLPlus I

• Ending SQL with or / or blank line
• Clear SQL/Buffer Empty the SQL buffer
• Connect userid/password Change to another user
• Describe schema.object Provide the object
  details. The object can be a table, a view or a
  procedure

11
Using SQLPlus II

• Edit Call the defined editor (default is
  Notepad) to edit the contents in SQL buffer.
• Define Define a user variable with the value.
• Define _Editor Specify the name of your editor.
  _Editor is a system variable
• Execute Execute a SQL statement, usually a
  stored procedure
• Execute id scott.test(parameter)

12
Using SQLPlus III

• ExitQuit Exit SQLPlus
• Get filename Load the contents from a file into
  the SQL buffer
• Help topic Get the help info for the topic
• Host DOSCommand Run the specific DOS command
• List List the contents in SQL buffer

13
Using SQLPlus IV

• Password userid Change the password for the
  specified user
• Run or / Run the SQL statement in the buffer
• Save filename Save the contents in SQL buffer
  into a file
• Set sysvariable value Set the system variable
  into a new vaule

14
Using SQLPlus V

• Show sysvariable Show the value in specified
  system variable
• Start filename Run the SQL statements in the
  specified file

15
Oracle Security I

• Authentication
• Only the authorized users can use Oracle
  (UserId/Password)
• The activities of each user can be traced
• Object Privileges Specify the operations allowed
  by specific users to the specific database
  objects
• Grant ltprivilegesgt On ltobjectgt To ltusersgt
• Revoke ltprivilegesgt On ltobjectgt From ltusersgt
• Object Privileges are listed on Page. 594
• Multi privileges/users are separated by comma

16
Oracle Security II

• System Privileges The ability to execute
  specific Oracle commands
• Grant ltprivilegesgt To ltUsersgt
• System Privileges are listed on Page. 595
• Example Grant create table To demo
• Role A collection of privileges that can be
  assigned to users.
• Grant ltprivilegegt To ltrolegt
• Grant ltrolegt To ltusersgt
• Show example of add/grant user/role together with
  Navigator

17
PL/SQL Concepts I

• Comments
• Use --
• Use / /
• Constants
• String Use single quotations
• Number
• Date Use string format
• Identifier Name of a database object or local
  variable. Use double quotation if the identifier
  contains spaces.

18
Interactive Result

• Table dual Provides the dual communication
  between client and server
• DBMS_Output A system package assisting to show
  the output
• .Put(ltvaluegt) Display a single value
• .New_line Change to another line
• .Put_line(ltvaluegt) Equals .Put() .New_line
• Set Serveroutput OnOff EnableDisable server
  output

19
System Functions

• String
• ASCII(A), Chr(65)
• Length(str) The length ( of characters) of str
• Lower(str), Upper(str) Convert the letter case
• Ltrim(str), Rtrim(str) Remove the leading or
  tailing spaces

20
System Functions II

• Instr(str1,str2) The start position of str2 in
  str1
• Substr(str,s,l) Get a substring from str
• s the start position. If sgt0, count from left
  if slt0, count from right if s0 then s1
• l The required length of substring. If l is
  ignored, then the substring goes to the end of
  str.
• Note No Left() or Right() functions
• Replace(str,str1,str2) Replace str1 existing in
  str with str2.

21
System Functions III

• Numerical
• Ceil(num) Return smallest integer greater than
  or equal to num
• Floor(num) Return biggest integer less than or
  equal to num
• Round(num,dec) Round num to a new number with
  dec digits decimal.

22
Procedures, Functions and Packages

• Procedure vs Function
• Both consisted of a set of statements and
  pre-compiled
• Function must return a value.
• Procedure is executed as a line of command
• Function is executed as part of an expression
• Package A database object consisted of several
  procedures/functions and global variables.

23
Procedure Structure

• Declare
• Begin
• Statements
• Exception
• End
• Uncommon Operators
• Assignment var expression (Adopted from ADA)
• String concatenation

24
Flow Control I

• Block
• Begin End
• If Statement
• If ltconditiongt Then Elseif Else End If
• Repetition
• Loop
• Statement Block
• End Loop

25
Flow Control II

• Repetition
• While ltconditiongt Loop
• Statement Block
• End Loop
• For var In varstart varend Loop
• Statement Block
• End Loop
• Exit When ltconditiongt Exit the loop

26
Cursor I

• Definition A special type of variable that allow
  to access the contents at the row level.
• Syntax
• Cursor ltcursornamegt AsIs
• Select Statement

27
Cursor II

• Cursor Attributes
• Found True if the last operation was successful
• Isopen True if the cursor is open
• Notfound
• Rowtype Return a record variable contains the
  current row
• Type Return the data type of current column

28
Cursor III

• Cursor Methods
• Open ltcursornamegt Open a cursor
• Open ltcursornamegt Close a cursor
• Fetch ltcursornamegt Into rowvarvar1,var2,
  Retrieve next record into the variable
• Update lttablegt Set ltcol1gtltval1gt, Where Current
  Of ltcursorgt To update the current record.
• Delete lttablegt Where Current Of ltcursorgt To
  delete the current record.
• For above two methods cursor must be declare as
  updatable

29
Stored Procedure

• Create or Replace Procedure ltproceduregt AsIs
• Procedure block
• Drop Procedure ltproceduregt
• Exec ltproceduregt