DBS501 Course Outline

Course Code: DBS501
Course Name: Stored Procedures Using Oracles PL/SQL
Offered Date: Winter - 2022 | Other versions
Print Outline
Course 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.
Prerequisite: DBS311
Mode of Instruction: 3 hours interactive lecture per week.
1 hour lab time per week.
Learning Outcomes:
- 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
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

Prescribed Text(s):
Oracle 11g Notes - PL/SQL (available in the bookstore)
Reference Material:
Promotion Policy:

To obtain a credit in this subject, a student must:

  • Satisfactorily complete all assignments
  • Achieve a weighted average of 50% or better on both tests
  • Achieve a grade of 50% or better on the overall course


Grading Policyhttp://www.senecacollege.ca/about/policies/grading-policy.html

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/about/policies/academics-and-student-services.html) or at Seneca's Registrar's Offices. (https://www.senecacollege.ca/registrar.html).

Assigns (2)              24%
Labs (min 5)            10%
Quizzes (min 2)      24%
Tests (2)                  42%

Approved By:
Kathy Dumanski
Cheating and Plagiarism:
Seneca upholds a learning community that values academic integrity, honesty, fairness, trust, respect, responsibility and courage. These values enhance Seneca's commitment to deliver high-quality education and teaching excellence, while supporting a positive learning environment. Ensure that you are aware of Seneca's Academic Integrity Policy which can be found at: http://www.senecacollege.ca/about/policies/academic-integrity-policy.html Review section 2 of the policy for details regarding approaches to supporting integrity. Section 2.3 and Appendix B of the policy describe various sanctions that can be applied, if there is suspected academic misconduct (e.g., contract cheating, cheating, falsification, impersonation or plagiarism).

Please visit the Academic Integrity website http://open2.senecac.on.ca/sites/academic-integrity/for-students to understand and learn more about how to prepare and submit work so that it supports academic integrity, and to avoid academic misconduct.
Discrimination and Harassment:
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.
Accomodation 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 Accessibility Services Office at ext. 22900 to initiate the process for documenting, assessing and implementing your individual accommodation needs.