Oracle 1z0-149 exam PL/SQL

This course is for database administrators and developers that want to attempt the 1z0-149 exam

Pass 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 exam

topics include

Declaring PL/SQL Variables

• Recognize valid and invalid identifiers

• List the uses of variables, declare and initialize variables, use bind variables

• List and describe various data types using the %TYPE and %ROWTYPE attributes

Writing Executable Statements

• Identify lexical units in a PL/SQL block

• Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions

• Describe when implicit conversions take place and when explicit conversions have to be dealt with

• Write nested blocks and qualify variables with labels

• Write readable code with appropriate indentation

Writing SQL in PL/SQL

• Create PL/SQL executable blocks using DML and transaction control statements

• Make use of the INTO clause to hold the values returned by a SQL statement

Writing Control Structures

• Identify the uses and types of control structures (IF, CASE statements and expressions)

• Construct and identify loop statements

• Use EXIT and CONTINUE statements inside loops

• Working with Composite Data Types

  • Create user-defined PL/SQL records

  • Create a record with the %ROWTYPE attribute

  • Create an INDEX BY table and INDEX BY table of records

  • Describe the differences among records, collections, and collections of records

  • Initialize collections and records

  Using Explicit Cursors

  • Distinguish between implicit and explicit cursors and use SQL cursor attributes

  • Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data

  • Declare and use cursors with parameters

  • Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause

  Handling Exceptions

  • Define PL/SQL exceptions

  • Recognize unhandled exceptions

  • Handle different types of exceptions (internally defined exceptions, predefined exceptions and user-defined exceptions)

  • Propagate exceptions

  Using PL/SQL Subprograms

  • Differentiate between anonymous blocks and subprograms

  • Create a simple procedure and invoke it from an anonymous block

  • Identify benefits of subprograms

  Creating Procedures and Using Parameters

  • Create a procedure with parameterrs

  • Use named notation

  • Work with procedures (create, invoke and remove procedures)

  • Handle exceptions in procedures and display a procedure's information

  Creating Functions

  • Differentiate between a procedure and a function

  • Describe the uses of functions

  • Work with functions (create, invoke and remove functions)

  Creating Packages

  • Identify the benefits and the components of packages

  • Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)

  • Overload package subprograms and use forward declarations

  Working with Packages

  • Use package types and variables

  • Use packaged constants and functions in SQL

  • Use ACCESSIBLE BY to restrict access to package subprograms

  Using Dynamic SQL

  • Describe the execution flow of SQL statements

  • Use Native Dynamic SQL (NDS)

  • Bind PL/SQL types in SQL statements

  Design Considerations for PL/SQL Code

  • Create standard constants and exceptions

  • Write and call local subprograms

  • Control the run-time privileges of a subprogram

  • Perform autonomous transactions

  • Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause

  • Use bulk binding and the RETURNING clause with DML

  Creating Compound, DDL, and Event Database Triggers

  • Create triggers on DDL statements

  • Create triggers on system events

  • Describe different types of triggers and their uses

  Using the PL/SQL Compiler

  • Describe the PL/SQL compiler and features

  • Use the PL/SQL compiler initialization parameters

  • Use the PL/SQL compile time warnings

  Managing PL/SQL Code

  • Describe and use conditional compilation

  • Code-based access control: granting roles to program units

  • Whitelist code access with the ACCESSIBLE BY clause

  • Mark code as deprecated

  Managing Dependencies

  • Track and manage procedural dependencies