DBS501 - Stored Procedures Using Oracles PL/SQL

Outline info
Last revision date 2018-07-20 11:57:18.415
Last review date 2018-07-20 11:57:30.686

Subject Title
Stored Procedures Using Oracles PL/SQL

Subject Description
This subject uses Oracle's PL/SQL language to code PL/SQL blocks, procedures, functions, packages, and database triggers for applications developed using Oracle relational databases.

Credit Status
1 credit in the CPA/CPD/CPAC Programs.

Learning Outcomes
Upon successful completion of this subject the student will be able to:

- Use a 3rd generation language (3GL) to access data in an Oracle database

- Code PL/SQL objects to access and maintain data in an Oracle database

- Develop and maintain objects such as procedures, functions, packages and database triggers in the data dictionary

- Establish appropriate logic in PL/SQL objects to maintain the integrity of the data

Cheating and Plagiarism
Each student should be aware of the College's policy regarding Cheating and Plagiarism. Seneca's Academic Policy will be strictly enforced.

To support academic honesty at Seneca College, all work submitted by students may be reviewed for authenticity and originality, utilizing software tools and third party services. Please visit the Academic Honesty site on http://library.senecacollege.ca for further information regarding cheating and plagiarism policies and procedures.

All students and employees have the right to study and work in an environment that is free from discrimination and/or harassment. Language or activities that defeat this objective violate the College Policy on Discrimination/Harassment and shall not be tolerated. Information and assistance are available from the Student Conduct Office at student.conduct@senecacollege.ca.

Accommodation for Students with Disabilities
The College will provide reasonable accommodation to students with disabilities in order to promote academic success. If you require accommodation, contact the Counselling and Disabilities Services Office at ext. 22900 to initiate the process for documenting, assessing and implementing your individual accommodation needs.


Topic Outline
Fundamentals of PL/SQL coding - 40%

  • Introduction and Declaring Variables
    • Benefit of PL/SQL in comparison to SQL
    • Bind variables and using in PL/SQL block
    • Handling variables in PL/SQL and the types of variables
    • PL/SQL block structure
    • Scalar variable declaration
    • Using iSQL* Plus variables within PL/SQL block
  • Writing Executable Statements
    • Nested blocks and variable scope
    • Operators and identifiers in PL/SQL
    • PL/SQL block syntax
  • Interacting with the Oracle Server
    • Manipulating Data Using PL/S
    • Using iSQL* embedding SQL inside PL/SQL block
  • Working with Composite Data Type
    • Composite data type
    • PL/SQL records and record structure
  • Writing Control Structure
    • Case expression
    • Compound IF Statement
    • Controlling PL/SQL block flow of execution
    • Nested loops and and labels
  • Writing Explicit Cursor
    • Advanced explicit cursor
    • Controlling explicit cursors
    • Cursor for Loop and cursor attributes
  • Handling Exceptions

Procedures, packages, functions and triggers - 60%
  • Creating Procedures
    • Block structure for subprograms
    • Creating procedures
    • Creating procedures with parameters
  • Creating Functions and Managing Subprograms
    • Benefit of stored procedures and functions
    • Creating functions and the block structure for subprograms
    • Managing subprograms
  • Creating Packages
    • Advantages of using packages
    • Creating package specifications
    • Developing packages
    • Oracle supplied packages
    • PL/SQL tables and records in the package
  • Creating Database Triggers
    • Benefit of database triggers
    • Creating database triggers
    • Creating DML statement triggers
    • Row and table level triggers
  • Managing dependencies 

Mode of Instruction
Class lectures, discussions and exercises reinforced with lab sessions. 

It is the student's responsibility to save documents, articles and notes that the instructor has provided on BlackBoard or in class. Students will not be able to access BlackBoard as of the last day of the student’s class.

Prescribed Texts
Oracle 11g Notes – PL/SQL (available in bookstore)

Promotion Policy

Grading Policy
A+ 90%  to  100%
A 80%  to  89%
B+ 75%  to  79%
B 70%  to  74%
C+ 65%  to  69%
C 60%  to  64%
D+ 55%  to  59%
D 50%  to  54%
F 0%    to  49% (Not a Pass)
EXC Excellent
SAT Satisfactory
UNSAT Unsatisfactory

For further information, see a copy of the Academic Policy, available online (http://www.senecacollege.ca/academic-policy) or at Seneca's Registrar's Offices.

Modes of Evaluation
Since this is a professional credit subject, marking standards reinforce professional practice by demanding legible, tidy work. Written materials should be well organized and grammatically correct, with proper spelling and punctuation.


  • Students must retain a duplicate of all assignments.
  • Computer assignments should be documented to the instructor's standards.
  • Assignments must be handed in on the scheduled due date. Late assignments are penalized.
  • For particulars, please obtain standards, dates, etc. from your instructor.

Absenteeism and Tests
  • Students should be aware that absenteeism will impact on their ability to achieve satisfactory grades.
  • If you miss a test, you must provide the reason in writing to the instructor prior to the next scheduled class. If your reason is accepted, you will be permitted to write a make-up test. Otherwise, you will be given a zero for the test. You must submit an original doctor’s certificate identifying the date, length of time of expected absence and the specific reason for your absence, or other appropriate documentation.

Term Work and Final Exam
  • Students must attain a combined grade of at least 50% on term work and the final exam. Students must pass the final exam in order to pass the subject. 
  • For further information on evaluation and academic standing, see a copy of the Academic Policy available at Seneca registration offices.

Grading is based on the following marking scheme:

Assignments (minimum 2) 30%
Progress tests (minimum 2) 40%
Final Examination 30%

Approved by: Denis Gravelle