Oracle PL/SQL is My Game: EXAM 1Z0-149

Understand Oracle's procedural extension to sql, with block structures, variables, data types, control structures, and reusable units like procedures, functions, and packages.

Explore the pl/sql block structure, including declare, begin, exceptions, and end, and compare anonymous blocks with named sub programs and their parameters and return values.

Learn to write anonymous plsql blocks, distinguish them from subprograms, and organize related functions in a package; use begin and end blocks and dbms_output to display results.

Learn how to run an anonymous PL/SQL block using SQL*Plus, enable DBMS_OUTPUT, and view the block's output similar to SQL Developer.

Write a PL/SQL block that outputs hello with DBMS_OUTPUT.PUT_LINE, save it as test.sql, and run it via SQL Developer or SQL Plus to see the hello result.

Declare PL/SQL variables for temporary storage, manipulate and reuse values in declarative and executable sections. Learn naming rules, constants, and how to pass variables to subprograms.

Learn how to declare and initialize PL/SQL variables in date, number, and varchar2 types with not null constraints and constants, using two examples showing arithmetic and date addition via dbms_output.

Master guidelines for declaring and initializing pl-sql variables, including naming conventions and meaningful identifiers, one identifier per line for readability, and not null initial values with assign operator or default.

Master how to use delimiters in string literals and the q' notation in Oracle PL/SQL to handle apostrophes, with practical examples using dbms_output and various delimiters.

Explore how PL SQL variables differ from non PL SQL variables and describe scalar and composite data types, including char, varchar2, number, boolean, binary float, and binary double.

Explore date and time data types, including date, timestamp and time zone variants, and interval types, then cover large object types (clob, blob, nclob) and composite types with segments.

Explore PL/SQL data types through variable declarations and assignments, including date, timestamp, current_timestamp, and interval year to month, and format outputs with to_char and dbms_output.

Define variables using the percentage type attribute to mirror a database column definition. Leverage the benefits to avoid data type mismatches and hardcoding when column definitions change.

Learn to write a PL/SQL block that retrieves an employee's first and last name into percentage type variables with select into, and handle the one-row rule and exceptions.

Learn how bind variables, or host variables, persist for a session and are used in SQL and PL/SQL. Define, assign, and print them with the variable keyword and colon references.

Explore what sits inside a plsql block: identifiers, variables (including double-quoted case-sensitive names), literals, delimiters, comments, assign vs equal, sequence nextval, and conversion functions.

Demonstrate pl/sql block basics by declaring variables from employees, selecting into them, and displaying first name, last name, salary, and hire date using length, substring, and date operations.

Explore nested blocks in PL/SQL, including the outer and inner blocks, global and local variable scope, and how to reference outer variables with outer dot notation.

Document code with comments to describe procedures and readability through indentation, and apply Oracle's guidelines by using upper case keywords and lower case identifiers like employees, employees_info, and v_salary.

Learn how to interact with the Oracle database server using select, insert, update, delete, and merge in PL/SQL, manage commit, rollback, and savepoint, and apply one-row retrieval rules.

Practice PL/SQL with select, update, and insert within blocks, handling variables, dbms_output, and standard functions, while avoiding reserved words and understanding exceptions for non-matching updates.

Explain how Oracle uses an implicit SQL cursor to process statements, reporting SQL percentage not found, SQL percentage found, and SQL row count.

Explore implicit cursors in Oracle PL/SQL with a salary update example, using sql%rowcount, sql%found, dbms_output, and an if statement to handle zero and nonzero row cases.

Master control flow in Oracle PL/SQL by applying simple if, if else, and else if formats, using substitution variables and dbms_output to display results.

Master if-else if chains to map grid values to letter grades, validate ranges, handle edge cases, and apply a salary comparison example using substitution variables in PL/SQL.

Master handling nulls in Oracle PL/SQL if statements by using nvl and is null, avoiding null comparisons and ensuring correct outcomes for exam 1z0-149.

Discover the difference between case expression and case statement in Oracle PL/SQL, including value-returning salary increments by department and the case statement that performs actions with end case.

Master the while loop in Oracle PL/SQL by repeating statements while a condition holds, printing outputs with dbms_output.put_line, incrementing counters, and tracing examples with employee names.

Master the plsql for loop, including forward and reverse iterations, implicit declaration of the loop variable, and examples like printing greetings and fetching employee data by id 100 to 102.

This Oracle PL/SQL lecture demonstrates nested loops to print five lines of stars, using an outer loop 1–5 and an inner loop 1–I, building and printing v_star.

Define labels for outer and inner loops in PL/SQL, then exit the outer loop from the inner loop to control execution and simplify loop management.

Learn to use the continue statement to control a for loop from 1 to 10, printing a happy face for 1–5 and skipping 6–10.

Master PL/SQL records, a composite data type that groups fields; define programming defined records with a type and variables, then select into a record and output its fields.

Define a programmer defined PL/SQL record as a composite type with employee id, first name, and last name; declare t_emp, fetch into it, and print its fields via for loop.

Learn to define PL/SQL records using percentage row type to mirror the departments table, then populate a copy of the table and update rows using a composite record.

Create a nested pl sql record by defining type t_tel for mobile one, mobile two, and landline, then type t_emp_tel with v_id, v_full_name, and v_tel, and use dbms_output to display.

Explore PL/SQL associative arrays (index by tables) that map (key, value) pairs, with keys integer or string, declare and populate them, and print with dbms_output.

Master index by table methods for associative arrays in pl/sql by applying exist, count, first, last, prior, next, and delete to manage and access elements.

Learn how to use index-by tables of records in PL/SQL by declaring an associative array of employee records, filling it with full table data, and printing selected fields with loops.

Define a nested table as a table of varchar2 to store values in memory, note it has no visible index unlike index pi tables, and learn initialization, extension, and manipulation.

Varray is a fixed-size collection, like a nested table, with a three-element varchar array; it enforces size limits and is mainly used in PL/SQL rather than SQL.

Declare the explicit cursor, open it to execute the select, fetch rows row by row, then close. Apply attributes such as percentage not found and percentage found to control processing.

Master explicit cursors in Oracle PL/SQL: declare, open, fetch, and loop through department 30, print employee ids and first names, and update salaries with three methods.

Explore explicit cursor attributes like percentage not found, percentage open, and percentage row count to control cursor flow and track fetched rows in Oracle PL/SQL.

Explore how to use a for loop cursor in Oracle PL/SQL to iterate over a select statement without manual open, fetch, or close, outputting employee id and first name.

Learn to define a parameterized cursor in Oracle PL/SQL, open with department ids, fetch results, and reuse it; use a for loop cursor to automate open, fetch, and close.

Use the for update clause to lock rows and the current of clause to update the same cursor row. Enable safe, incremental salary updates with this technique.

Handle errors in PL/SQL by using the exception section to trap and respond. Explore predefined exceptions such as no data found and too many rows.

Learn to handle predefined Oracle server error exceptions in PL/SQL, focusing on no data found, too many rows, and others, with practical block and exception handling patterns.

Highlight a common PL/SQL mistake with no data found aborting a loop; learn to wrap the select in its own block to continue printing existing employee names.

Define a custom exception for non predefined Oracle server errors and map it with pragma exception_init. Handle the exception and place each statement in its own begin and end block.

Explore user defined errors in pl/sql by defining a programmer exception, raising it with sql percentage not found, and using raise_application_error for custom error codes.

Explore group functions and exceptions, including sum, count, and average, and learn how Oracle handles null results versus no data with custom exception handling.

Explore how PL/SQL uses nested inner and outer blocks to handle no data found and other exceptions within each block.

Understand how to create and call procedures, and distinguish them from anonymous blocks and functions. Adopt modular sub programs with parameters to improve reuse, maintenance, and data security and integrity.

Learn the syntax for Oracle PL/SQL procedures, including create or replace, parameter lists, and parameter modes. See examples with updating salaries and calling procedures via execute or begin-end blocks.

Create and call a PL/SQL procedure with two IN parameters to update employee salaries, handling commits and errors, and learn to compile, test, view errors, and drop the procedure.

Explore out parameter mode in a PL/SQL procedure, define bind variables for the outputs, and print results with dbms_output.

Explore the in out parameter in Oracle PL/SQL by formatting a 12-digit telephone number with a procedure, noting that initial values are required and the result returns to the caller.

Compare the n, out, and n out parameter modes in Oracle PL/SQL, detailing defaults, initialization rules, and which ones support default values and require variables.

Explore the available notations for passing parameters in PL/SQL procedures—positional, named, and mixed—through practical examples using a products table and an add_products procedure.

Learn how to set default values for parameters in a PL/SQL procedure, using default and colon-equal syntax, by recreating a products procedure with optional name and type.

Explore how to handle exceptions across multiple PL/SQL blocks using a add underscore products procedure, including committed inserts, exception handling with when others, and rollback behavior.