Overview

This course is for database administrators and developers that want to attempt the 1z0-149 examPass the Oracle PL/SQL 19c Professional exam 1z0-149 on the first try.This exam is for experts ready to write the oracle database program with PL/SQL 19c certified professional examtopics includeDeclaring PL/SQL VariablesRecognize valid and invalid identifiersList the uses of variables, declare and initialize variables, use bind variablesList and describe various data types using the %TYPE and %ROWTYPE attributesWriting Executable StatementsIdentify lexical units in a PL/SQL blockUse built-in SQL functions in PL/SQL and sequences in PL/SQL expressionsDescribe when implicit conversions take place and when explicit conversions have to be dealt withWrite nested blocks and qualify variables with labelsWrite readable code with appropriate indentationWriting SQL in PL/SQLCreate PL/SQL executable blocks using DML and transaction control statementsMake use of the INTO clause to hold the values returned by a SQL statementWriting Control StructuresIdentify the uses and types of control structures (IF, CASE statements and expressions)Construct and identify loop statementsUse EXIT and CONTINUE statements inside loopsWorking with Composite Data TypesCreate user-defined PL/SQL recordsCreate a record with the %ROWTYPE attributeCreate an INDEX BY table and INDEX BY table of recordsDescribe the differences among records, collections, and collections of recordsInitialize collections and recordsUsing Explicit CursorsDistinguish between implicit and explicit cursors and use SQL cursor attributesDeclare and control explicit cursors, use simple loops and cursor FOR loops to fetch dataDeclare and use cursors with parametersLock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clauseHandling ExceptionsDefine PL/SQL exceptionsRecognize unhandled exceptionsHandle different types of exceptions (internally defined exceptions, predefined exceptions and user-defined exceptions)Propagate exceptionsUsing PL/SQL SubprogramsDifferentiate between anonymous blocks and subprogramsCreate a simple procedure and invoke it from an anonymous blockIdentify benefits of subprogramsCreating Procedures and Using ParametersCreate a procedure with parameterrsUse named notationWork with procedures (create, invoke and remove procedures)Handle exceptions in procedures and display a procedure's informationCreating FunctionsDifferentiate between a procedure and a functionDescribe the uses of functionsWork with functions (create, invoke and remove functions)Creating PackagesIdentify the benefits and the components of packagesWork with packages (create package specification and body, invoke package subprograms, remove a package and display package information)Overload package subprograms and use forward declarationsWorking with PackagesUse package types and variablesUse packaged constants and functions in SQLUse ACCESSIBLE BY to restrict access to package subprogramsUsing Dynamic SQLDescribe the execution flow of SQL statementsUse Native Dynamic SQL (NDS)Bind PL/SQL types in SQL statementsDesign Considerations for PL/SQL CodeCreate standard constants and exceptionsWrite and call local subprogramsControl the run-time privileges of a subprogramPerform autonomous transactionsUse NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clauseUse bulk binding and the RETURNING clause with DMLCreating Compound, DDL, and Event Database TriggersCreate triggers on DDL statementsCreate triggers on system eventsDescribe different types of triggers and their usesUsing the PL/SQL CompilerDescribe the PL/SQL compiler and featuresUse the PL/SQL compiler initialization parametersUse the PL/SQL compile time warningsManaging PL/SQL CodeDescribe and use conditional compilationCode-based access control: granting roles to program unitsWhitelist code access with the ACCESSIBLE BY clauseMark code as deprecatedManaging DependenciesTrack and manage procedural dependencies

Skills

Reviews