AI-Assisted

Find out which error a PL/SQL handler caught with SQLCODE and SQLERRM, and target common errors with predefined exception names.

Unite, intersect, and subtract nested tables like sets, and test membership and subsets, in PL/SQL and SQL without loops.

Build whole records and collections in a single expression, with values by position, by name, by index, or computed by an iterator.

Keep small ordered lists with a natural maximum, such as a top three, in varrays, and see how they compare with the other collections.

Work with unbounded lists in PL/SQL with nested tables, handle gaps after deletions, and pass lists of values to SQL with TABLE().

Store values under string or integer keys with associative arrays, use them as fast in-memory lookups, and handle missing keys safely.

Return one or more result sets from a PL/SQL block or procedure to the client with DBMS_SQL.RETURN_RESULT, without OUT cursor parameters.

Reuse one explicit cursor for many similar queries by giving it parameters with defaults, passed by position or by name.

Find out how many rows an UPDATE or DELETE changed, or whether it changed any, with the implicit cursor attributes of PL/SQL.

Step by any amount, cover several ranges, filter iterations, and loop over collection values, indexes, or pairs in one PL/SQL FOR loop.

Give PL/SQL types meaningful names with built-in limits, such as a 0 to 100 percentage, and let the language enforce them on every assignment.

Mark points in a transaction with SAVEPOINT and undo only the changes made after one with ROLLBACK TO SAVEPOINT, keeping earlier work.

Get the values before and after an update in the same statement with RETURNING OLD and NEW, for a single row or many with BULK COLLECT.

Keep a bulk INSERT or UPDATE going when some rows fail: log the bad rows to an error table with LOG ERRORS and load the rest.

Speed up bulk loads and archiving with direct-path INSERT and the APPEND hint, and confirm the load path with LOAD AS SELECT in the plan.

Route the rows of one query into several tables in a single statement with INSERT ALL and INSERT FIRST, with conditions or without.

See the live state of the database with V$ views: find your session, read parameter values, check the version, and list the PDBs.

Find every constraint of a table, what each foreign key references, and which tables point to a table, with USER_CONSTRAINTS.