2007/2008 Competency-Based Task/Competency List

Tasks/competencies designated by bullets in the left-hand column are considered essential statewide and are required of all students. In some courses, all tasks/competencies have been identified as essential. Tasks/competencies without bullets are considered optional; they and/or additional tasks/competencies may be taught at the discretion of the school division. Tasks/competencies marked with an asterisk (*) are considered sensitive, and teachers should obtain approval by the school division before teaching them.

6662 36 weeks	Database Design and Management with PL/SQL (Oracle) TASKS/COMPETENCIES
	Implementing Virginia's CTE Course Requirements
•	001	Demonstrate Virginia's Workplace Readiness Skills in course activities.
•	002	Apply Virginia's All Aspects of Industry elements in course activities.
•	003	Identify Internet safety issues and procedures for complying with acceptable use standards.
	Participating in the Student Organization
•	004	Identify the purposes and goals of the student organization.
•	005	Explain the benefits and responsibilities of membership in the student organization as a student and in professional/civic organizations as an adult.
•	006	Demonstrate leadership skills through participation in student organization activities, such as meetings, programs, and projects.
	Introducing PL/SQL Programming Concepts
•	007	Explain the function of PL/SQL.
•	008	Differentiate between SQL and PL/SQL.
•	009	Explain the need for PL/SQL.
•	010	Describe the structure of a PL/SQL block.
•	011	Identify the different types of PL/SQL blocks.
	Using PL/SQL Program Data
•	012	Use variables in PL/SQL.
•	013	Describe valid and invalid identifiers in PL/SQL.
•	014	Describe reserved words, delimiters, literals, and comments in PL/SQL.
•	015	List data types used in PL/SQL.
•	016	Identify the benefits of anchoring data types with the %TYPE attribute.
•	017	Use built-in SQL functions in PL/SQL.
•	018	Differentiate between implicit and explicit conversions.
•	019	Demonstrate how functions can be used to explicitly convert data types.
	Using SQL in PL/SQL
•	020	Decide which SQL statements can be directly included in a PL/SQL executable block.
•	021	Hold the values returned by a SQL statement by using the INTO clause.
•	022	Manipulate data with DML statements in PL/SQL.
•	023	Use SQL cursor attributes.
•	024	Use transaction control statements in PL/SQL.
	Manipulating PL/SQL Program Structures
•	025	Identify the uses and types of conditional control structures.
•	026	Construct and use an IF statement.
•	027	Construct and use an IF-THEN-ELSE statement.
•	028	Use basic loops with exit conditions.
•	029	Use basic loops with exit when conditions.
•	030	Use WHILE loops.
•	031	Use FOR loops.
•	032	Use nested loops.
	Using Cursors
•	033	Distinguish between an implicit and an explicit cursor.
•	034	Declare and control explicit cursors.
•	035	Use cursor simple loop to fetch data.
•	036	Use cursor FOR loop to fetch data.
•	037	Declare and use cursors with parameters.
•	038	Lock rows using the FOR UPDATE clause.
•	039	Reference the current row with the WHERE CURRENT clause.
•	040	Use cursors with subqueries.
	Using Exception Handling
•	041	Describe the function of exceptions.
•	042	Describe the function of an exception handler.
•	043	Detect unhandled exceptions.
•	044	Handle exceptions in PL/SQL programs.
•	045	Trap predefined Oracle Server errors.
•	046	Trap non-predefined Oracle Server errors.
•	047	Trap user-defined errors.
	Creating and Removing Procedures
•	048	Identify the function of a stored procedure.
•	049	Invoke a stored procedure.
•	050	List the development steps for creating a procedure.
•	051	Create a procedure with parameters.
•	052	Invoke a procedure that has parameters.
•	053	List the types of parameter modes.
•	054	Describe the DEFAULT option for parameters.
•	055	Describe the method for propagating exceptions.
•	056	Remove a procedure.
•	057	Identify views in the data dictionary to manage procedures.
	Creating and Removing Functions
•	058	Define "stored function."
•	059	Create a function.
•	060	List the procedures for invoking a function.
•	061	List the advantages of user-defined functions in SQL statements.
•	062	List circumstances in which user-defined functions can be called from within an SQL statement.
•	063	Describe the restrictions on calling functions from SQL statements.
•	064	Remove a function.
•	065	Identify views in the data dictionary to manage stored objects.
•	066	Identify differences between invoker and definer rights.
	Designing Packages
•	067	Identify a package specification and body.
•	068	Create packages (e.g., related variables, cursors, constants, exceptions, procedures, and functions).
•	069	Invoke a package construct.
•	070	Designate package constructs as public or private.
•	071	Drop packages.
•	072	Identify benefits of packages.
•	073	Create packages that use the overloading feature.
•	074	Identify restrictions on using packaged functions in SQL statements.
•	075	Invoke packaged functions from SQL.
•	076	Identify persistent states in package variables and cursors.
•	077	Describe the use and application of some Oracle server-supplied packages (e.g., DBMS_SQL, DBMS_OUTPUT, and UTL_FILE).
•	078	Use EXECUTE IMMEDIATE.
•	079	Describe the benefits of EXECUTE IMMEDIATE over DBMS_SQL for Native Dynamic SQL.
	Creating and Removing Triggers
•	080	Describe database triggers and types and the uses of each.
•	081	Create a DML trigger.
•	082	List the DML trigger components.
•	083	Create a statement-level trigger.
•	084	Describe the trigger-firing sequence options.
•	085	Use conditional predicates in a DML trigger.
•	086	Create a row-level trigger.
•	087	Use the OLD and NEW qualifiers in a database trigger.
•	088	Create an INSTEAD OF trigger.
•	089	Describe events that cause database triggers to fire.
•	090	Create a trigger for a DDL statement.
•	091	Create a trigger for a system event.
•	092	Describe the functionality of the CALL statement.
•	093	Describe the cause of a mutating table.
•	094	View trigger information in the dictionary views.
•	095	Alter a trigger status.
•	096	Remove a trigger.
	Using Advanced Data Types
•	097	Describe the function of LOB datatypes.
•	098	Migrate from LONG to LOB.
•	099	Manage Bfiles.
•	100	Use PL/SQL tables in packages.
•	101	Use PL/SQL records in packages.
	Understanding Procedural Dependencies
•	102	Describe the implications of procedural dependencies.
•	103	Describe dependent objects and referenced objects.
•	104	View dependency information in the dictionary views.
•	105	Use the UTLDTREE script.
•	106	Use the IDEPTREE and DEPTREE views.
•	107	List the procedures for minimizing dependency failures.
	Preparing for Industry Certification
•	108	Describe the process and requirements for obtaining industry certifications related to this course.
•	109	Identify testing skills/strategies for a certification examination.
•	110	Demonstrate ability to successfully complete selected practice examinations (e.g., practice questions similar to those on certification exams).
	Developing Employability Skills
•	111	Investigate job opportunities, using the Internet and other sources.
•	112	Investigate careers in the information technology field.
•	113	Research certification and educational opportunities.
•	114	Compose or update a printed résumé.
•	115	Prepare an electronic résumé.
•	116	Create a letter of application.
•	117	Complete an electronic application form.
•	118	Create an interview follow-up letter.
•	119	Identify the steps to follow in resigning from a position.
•	120	Develop a portfolio containing representative samples of student's work (e.g., program design, source code, technical documentation and design, source code, and output).
•	121	Deliver an oral presentation of portfolio.
•	122	Identify potential employment barriers for nontraditional groups and ways to overcome barriers.


		2002 Bremo Road • Lower Level • Richmond, VA 23226 • Ph: (804) 673-3778 • Fax (804) 673-3798 • info@cteresource.org The CTE Resource Center is administered by Henrico County Public Schools for the Virginia Department of Education.