Learn Oracle PL/SQL - A Comprehensive Training Course

Learn where to access Oracle software and documentation, access forums, and download free Oracle XE for practice, including registering an Oracle ID to install X-C.

Install Oracle 11g Express Edition, accept the license, use default install settings, set a password, start the database, and connect as HR to explore the employees table.

Explore PL/SQL block structure, including declaration, executable, and exception sections, with nested and anonymous blocks and procedures, to understand how to declare variables, run logic, and handle errors.

Learn how Oracle PL/SQL functions return values and integrate into expressions, how procedures perform actions without returns, and how packages group these units with spec and body, plus event-driven triggers.

Learn Oracle PL/SQL data types, including varchar2 and char strings, date and timestamp values (with time zone), numeric types like number and integer, and boolean expressions.

Explore complex data structures in Oracle PL/SQL, including record types, arrays, and objects; declare types, instantiate variables, and use dot notation and indexing to manage nested collections.

Learn about variables, constants, and parameters in Oracle PL/SQL, including memory locations, initialization, and basic data types, with counters, dates, strings, and output examples.

Learn how variables, constants, and parameters work in Oracle PL/SQL, including in, out, and in out modes, and how procedures and anonymous blocks pass and modify values.

Explore flow control in PL/SQL by using if and case statements, including simple and searched cases, plus looping constructs like for, while, and unconditional loops with exit conditions.

Master how to handle errors and exceptions in PL/SQL using exception blocks, predefined exceptions like no data found, and when others, with raising and handling custom exceptions.

Comment Oracle PL/SQL code with single-line dash-dash and multi-line slash-star comments. Place comments anywhere, including headers, and write meaningful notes that explain intent.

Explore oracle pl/sql string data types, including char and varchar2; learn about fixed vs variable length, padding, string literals with single quotes, and concatenation.

Explore how Oracle's number data type uses precision and scale, including 38 bytes of precision, floating point numbers, rounding rules, binary integer options, and explicit conversions with to_number and to_char.

Learn how to convert strings to dates and time stamps in Oracle PL/SQL, using format masks, implicit and explicit conversions, and to_char with fm for clean output.

Master booleans in Oracle SQL, using true, false, and null, with inline expressions or assignments for reuse; and learn about clob, blob, bfile, and dbms_lob with chunked processing.

Learn how record types combine scalar data types into a composite structure, declare and use row types from the employees table, and drive cursor-based select, update, and insert operations.

Learn how associative arrays (index-by tables) work in Oracle PL/SQL, with numeric indexes, assignment and retrieval, looping techniques, and bulk collection concepts.

Compare nested tables with associative arrays in Oracle PL/SQL; declare and initialize via the constructor, extend before adding values, and loop with first, last, and next to skip deleted elements.

Examine varray in Oracle PL/SQL as a fixed-size array that cannot grow, with memory allocated via constructor and extend, and learn to manage elements using limit and NULL.

Create an array of records to simplify bulk collect into a table of records with matching columns, and apply bulk operations that process rows, using cursor records and nested tables.

Learn sqlplus, a console-based, line-oriented oracle tool for connecting to an 11g express edition database and running commands, noting its user-unfriendly nature yet reliability as a backup and learning aid.

Learn Oracle SQL Developer navigation and how to create and test new connections. Use the navigator and editor to work with packages, procedures, and functions in the SQL worksheet.

Explore creating and editing code in SQL Developer, including worksheets, procedures, functions, and packages, and learn to connect, compile, run, and refresh.

Explore saving code to the file system, run anonymous blocks, and convert them into procedures, functions, or packages, while reviewing block structure and executable sections in Oracle PL/SQL.

Learn to create and use variables and constants in PL/SQL, assign values, and format output with to_char, then convert an anonymous block into a procedure.

Convert an anonymous block into a create or replace procedure named larget with an in parameter and a default hello world for logging output; compile, run, and observe debug messages.

Implement autonomous transactions to log messages in a separate transaction using a log table; commit or rollback independently from the main program flow.

Enhance a prior procedure by adding an output target parameter (default t) to route messages to a table or dbms output, using named and positional notation for flexible results.

Explore loop constructs in Oracle PL/SQL with the print_business_days procedure, counting working days (Monday through Friday) between dates using inclusive exit logic.

Learn how to compute business days in PL/SQL by using an associative table of holidays to exclude weekends and holidays, and explore converting between while, loop, and for loop constructs.

Convert the print business days routine into a function that returns the number of non weekend days between two dates, with optional debug and named notation.

Learn how to modularize PL/SQL code with a nested function that checks if a modulo operation equals zero, improving readability and maintenance.

Explore built-in SQL functions, including decode, case, and string handling with substr and position, and learn when to use native SQL versus PL/SQL for efficient data manipulation.

Explore Oracle PL/SQL exception handling, including runtime errors, no data found, and named exceptions, and learn how to propagate or handle errors across executable and anonymous blocks.

Explore how to handle Oracle's built-in and predefined exceptions and map names to error codes. Examine how the standard package and pragma exception support assigning names to numbers.

Learn how to raise exceptions in Oracle PL/SQL using the raise keyword and raise application error, handle codes with when others, and re-raise for outer handlers.

Master robust Oracle PL/SQL exception handling by logging and selectively raising errors, avoiding suppressed exceptions, and standardizing codes through an exceptions package for consistent, maintainable code.

Explore how PL/SQL handles data manipulation with insert, update, and delete operations, building an API to manage the employees table using record types and a sequence.

Explore updates and deletes in PL/SQL by building update APIs with defaults and named parameter notation, and handling nulls. Implement delete by primary key and practice controlled commits.

Learn to select data in PL/SQL with the into keyword, handle no data found and too many rows, and use local records, cursors, and sequences for inserts.

Learn how to use cursors in Oracle PL/SQL by declaring parameterized cursors, opening and fetching rows, and looping with a cursor for loop, including handling not found and row count.

Discover how the cursor for loop shortens code by auto-creating a local record during iteration. See how it compares to open the fetch and how bulk operations supersede it.

Master bulk operations in Oracle PL/SQL, starting with bulk collect into and the select statement, then use cursor, limit, and for all to perform faster inserts, updates, and deletes.