EXTREME-Performance PL/SQL Data Validation Package

(1) Introduction [FIRST LECTURE]

a. Introductory Greeting

b. Personal background/experience – summarized briefly

c. Targeted audience: Those working Data Migration to Oracle R12 or Oracle Cloud:

     i. Functional resources requiring high-level familiarity with PL/SQL programming    concepts

     ii. Technical resources, not familiar with PL/SQL, who want to or need to code using PL/SQL,Especially for the Data Validation and Cleansing required for a Data Migration/

d. Why use PL/SQL ?

Purpose of course:

While Data Migrations, from legacy sources into Oracle, may be accomplished by use of toolsets such as Azure Data Factory, Azure Synapse Analytics, Informatica, Oracle Data Integrator or similar tools, some organizations may prefer to avoid the costs involved and/or do not have available resources with the knowledge to utilize such tools.

Therefore, Oracle R12 Data Migration projects may often be worked by legacy or Oracle functional resources and legacy programmers not proficient in PL/SQL.

PL/SQL has long been and continues to be a primary development tool in the Oracle R12 realm.

i. Teach PL/SQL concepts for functional resources.

ii. Teach PL/SQL coding approach and techniques targeting programmers who work with languages other than PL/SQL.

iii. Leave the student with a working PL/SQL Data Validation package. We will develop an effective, efficient “Data Validation” package and accessory objects, all of which can be downloaded by the student and modified to their specific requirements.

Data Validation (and Cleansing) is crucial to the successful import of VALID legacy data

into the target Oracle R12 or Cloud system.

e. Nod to Steve Feuerstein re PL/SQL [“GoTo Source-of-Truth”] (6th Edition) as source of detailed programming techniques

Visio presentation of PL/SQL Package Structure + screenshot depicting the Visio and the SQL Developer display of the Package Spec

Students will (a) have complete instructions as to how to create and load data into a new Staging Table and how to create an Error Log table

Some basic SQL knowledge questions

(1) Which DECLARATION statement/s is/are correct ?

(A) A DECLARATION section must exist in the Package Spec and one at the top of the Package Body

(B) A DECLARATION section must exist in the Package Spec but none is required for the Body unless the is a CURSOR or one or more Global Variables

(C) A DECLARATION section must exist in the Package Spec and one is mandatory for each Procedure

(D) A DECLARATION section must exist in the Package Spec and for the Package Body and each Procedure that is to have a CURSOR and/or a Global variable

Enable the student to understand what is being presented in the Data Validation Summary and Detailed Error Reports with a review of the coding used to present this data.

Students will gain knowledge of basic tests to ensure that all source data is actually being processed/migrated.

Instructions on how to modify the demo Data Validation package to SUIT YOUR REQUIREMENTS.

Some reference works to be explored when more detailed knowledge is required !