Portland Community College | Portland, Oregon Portland Community College

CCOG for CIS 277O Spring 2024

View archive version »
Course Number:
CIS 277O
Course Title:
Advanced Database Concepts in Oracle
Credit Hours:
4
Lecture Hours:
30
Lecture/Lab Hours:
0
Lab Hours:
30

Course Description

Covers concepts with Oracle including PL/SQL programming concepts review. Includes design considerations for PL/SQL program units and packages. Advanced interface methods, features for PL/SQL, performance and tuning, and advanced features of Oracle supplied packages also covered. Recommended: CIS 276. Audit available.

Intended Outcomes for the course

On completion of this course the student should be able to design, develop, and maintain Oracle Database Objects, Advanced packages,stored procedures, and triggers.

Course Activities and Design

The course consists of lectures, group discussion, and lab assignments. Lab assignments include both individual lab assignments and group lab assignments.

Outcome Assessment Strategies

Through exams and lab assignments students will be assessed to determine if they are able to:

  • Develop Oracle Database Objects
    • Procedures
    • Functions
    • Packages
  •  Develop Advanced package concepts
    • Overloading
    • Forward declarations
    • One time procedures
    • Package functions restrictions
    • PL/SQL compilation checking
    • Persistent states:
    • Package variables
    • Package cursors
  • Produce readable output using SQL*Plus
    • SQL script writing
    • SQL*Plus commands to format output
  • Use Oracle supplied packages
    • DBMS_PIPE
    • DBMS_OUTPUT
    • Dynamic SQL
    • EXECUTE IMMEDIATE
    • DBMS_DLL
    • DBMS_JOB
  • Create database triggers
    • Design
    • Database triggers
    • Trigger components
    • Firing sequence
    • Conditional predicates
    • Row trigger development
    • Old and New qualifiers
    • Instead of triggers
    • DDL Triggers
    • System event triggers
    • Call statements
    • Mutating table issues
    • Enforcement of data integrity
    • Using triggers
    • Using server features
    • Referential integrity with triggers
    • Replication
    • Using triggers
    • Using server features
  • Manage subprograms and triggers
    • Required privileges
    • Data dictionary concepts
    • USER_OBJECTS
    • USER_SOUCE
    • Listing source code of procedures, functions and triggers
    • USER_ERRORS construct concepts
    • USER_TRIGGER table concepts
  • Manage object dependencies
  • Manipulate and create Oracle large objects
    • LOB Datatypes
    • CLOB
    • BLOB
    • NCLOB
    • BFILE
    • DBMS_LOB Read and write
    • DML with LOBs