Advanced PL/SQL

29.1 Pipelined Table Functions

Parallel-enabled pipelined functions (PARALLEL_ENABLE (PARTITION c BY ANY)) for ETL.

CREATE OR REPLACE TYPE emp_row AS OBJECT (id NUMBER, name VARCHAR2(100), annual NUMBER);
/
CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_row;
/
CREATE OR REPLACE FUNCTION annual_salaries RETURN emp_tab PIPELINED IS
BEGIN
    FOR r IN (SELECT employee_id, last_name, salary FROM employees) LOOP
        PIPE ROW (emp_row(r.employee_id, r.last_name, r.salary * 12));
    END LOOP;
    RETURN;
END;
/
SELECT * FROM annual_salaries() WHERE annual > 100000;

29.2 Object Types (Object-Relational)

Constructors, MAP and ORDER methods, inheritance (UNDER), TREAT, IS OF, REFs, object tables, and type evolution.

CREATE OR REPLACE TYPE address_t AS OBJECT (
    street VARCHAR2(100), city VARCHAR2(50), pin VARCHAR2(10),
    MEMBER FUNCTION full_address RETURN VARCHAR2
) NOT FINAL;
/
CREATE OR REPLACE TYPE BODY address_t AS
    MEMBER FUNCTION full_address RETURN VARCHAR2 IS BEGIN RETURN street || ', ' || city || ' ' || pin; END;
END;
/
CREATE TABLE people (id NUMBER, addr address_t);
SELECT p.addr.full_address() FROM people p;

29.3 PL/SQL Compiler and Optimization

PLSQL_OPTIMIZE_LEVEL (0–3; 2 is the default; 3 adds automatic inlining).

Native compilation: PLSQL_CODE_TYPE = NATIVE.

Compiler warnings: ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';.

Conditional compilation: PRAGMA INLINE, PRAGMA DEPRECATE (12.2), PRAGMA UDF, PRAGMA SERIALLY_REUSABLE, PRAGMA RESTRICT_REFERENCES (legacy).

PL/Scope (identifier and SQL usage analysis): PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL, STATEMENTS:ALL'.

$IF DBMS_DB_VERSION.VER_LE_19 $THEN
    -- legacy code
$ELSE
    -- 23ai code
$END
$IF $$debug $THEN DBMS_OUTPUT.PUT_LINE('debug'); $END
ALTER PACKAGE p COMPILE PLSQL_CCFLAGS = 'debug:TRUE';

29.4 Profiling, Tracing and Debugging

DBMS_HPROF (hierarchical profiler), DBMS_PROFILER (legacy), DBMS_TRACE.

SQL Developer debugger (DEBUG CONNECT SESSION privilege, ACL for JDWP).

Instrumentation: DBMS_APPLICATION_INFO.SET_MODULE/SET_ACTION, DBMS_SESSION.SET_IDENTIFIER, the open-source Logger framework.

29.5 Caching

Function result cache (RESULT_CACHE), /*+ RESULT_CACHE */ SQL query result cache, package-level collections as caches, deterministic functions, and scalar subquery caching.

29.6 Other Advanced Topics

Edition-Based Redefinition (EBR): editions, editioning views, cross-edition triggers. Online application upgrades (Module 60).

Java stored procedures (OJVM), external procedures (C), JavaScript stored procedures with Multilingual Engine (MLE, 23ai):

PL/SQL coding standards: naming prefixes (l_, g_, p_, c_), avoiding hard-coded literals, small modules, instrumentation, and unit testing with utPLSQL.

CREATE OR REPLACE FUNCTION js_greet(name VARCHAR2) RETURN VARCHAR2
AS MLE LANGUAGE JAVASCRIPT {{ return `Hello, ${NAME}!`; }};
/