Lesson 1

This course introduces you to the object-oriented paradigm within the context of Oracle, then focuses on the SQL and PL/SQL commands for creating, modifying, and manipulating data within objects such as object tables and related object tables. This course also covers nested tables, varrays, LOBs, object types, and objects embedded within relational tables. You will learn the steps necessary to create and use methods written in PL/SQL to manipulate data within objects.
This course is 1 of 3 courses that discusses PL/SQL Programming.

1. Oracle PL/SQL Programming
2. PL/SQL Stored Objects/
3. Programming PL/SQL

Along the way, you will work on a series of course projects and exercises that will give you a chance to put your new skills to use in the context of actual business scenarios.

Millions of application developers and database administrators around the world use software provided by Oracle Corporation to build complex systems that manage vast quantities of data. At the heart of much of Oracle's software is PL/SQL, a programming language that provides procedural extensions to Oracle's version of SQL (Structured Query Language) and serves as the programming language within the Oracle Developer toolset (most notably Forms Developer and Reports Developer). PL/SQL figures prominently as an enabling technology in almost every new product released by Oracle Corporation. Software professionals use PL/SQL to perform many kinds of programming functions, including:

1. Implementing crucial business rules in the Oracle Server with PL/SQL-based stored procedures and database triggers
2. Generating and managing XML documents entirely within the database
3. Linking web pages to an Oracle database
4. Implementing and automating database administration tasks, from establishing row-level security to managing rollback segments within PL/SQL programs

Key Details: PL/SQL (Procedural Language/SQL) remains fully supported and is a core component of the Oracle Database in Oracle 23ai**, just like in previous versions (e.g., 11g, 12c, 19c, 21c, 23c). Despite the "AI" branding in Oracle 23ai, which highlights enhancements in AI integration, JSON processing, vector search, and in-database machine learning, Oracle continues to support its traditional SQL and PL/SQL capabilities.
Usage:
You can run PL/SQL in Oracle 23ai:

• Inside anonymous blocks
• In stored procedures, functions, packages, and triggers
• Through SQL*Plus, SQL Developer, SQLcl, or other Oracle interfaces
• In conjunction with new AI-related features (e.g., calling a PL/SQL wrapper that uses in-database machine learning functions or JSON operations)

Example (still works in Oracle 23ai):

BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL in Oracle 23ai');
END;

Additional Enhancements:
Oracle 23ai introduces:

• AI Vector Datatypes (e.g., VECTOR)
• SQL Macros and JavaScript stored procedures
• But these extend the environment—they do not replace PL/SQL.

Completing the course, you will be able to:

1. Describe the SQL enhancements that allow you to query object and related object tables
2. Flatten nested tables and query them
3. Update object tables
4. Insert and delete rows in object tables
5. Analyze dangling references
6. Update nested tables and varrays
7. Insert and delete rows in nested tables and varrays
8. Create and use methods for object types

New Features for PL/SQL is the first of five courses in the oracle New Features
Certification Series.
In the next lesson, the prerequisites for this course will be discussed.