How to Describe a Query at Run Time with DBMS_SQL.DESCRIBE_COLUMNS
Learn the names, types, and nullability of a query's columns at run time, and write PL/SQL that prints any query.
PLSQL
PL/SQL (Procedural Language for SQL) is Oracle's proprietary extension to standard SQL. It combines the data manipulation power of SQL with the procedural capabilities of a programming language. PL/SQL allows developers to write complex database-centric applications by incorporating variables, conditions, loops, error handling, and modular programming constructs like procedures and functions. This language is tightly integrated with Oracle databases, enabling efficient data processing and transaction management.
How to Get Object Metadata with DBMS_DEVELOPER.GET_METADATA
Get the columns, keys, indexes, and statistics of a table, index, or view as one JSON document in Oracle AI Database 26ai.
How to Work with ROWIDs Using DBMS_ROWID
Split a ROWID into its object, file, block, and row number to see where rows are stored, including in bigfile tablespaces.
How to Manage Compiler Warnings with DBMS_WARNING
Turn PL/SQL compiler warnings on, off, or into errors from code, read the current setting, and restore it afterward.
How to Use DBMS_PREPROCESSOR
Print the source the PL/SQL compiler actually sees after conditional compilation, for stored units or source text.
How to Describe Procedures with DBMS_DESCRIBE
Find out the parameters, types, modes, defaults, and overloads of any procedure or function at run time.
How to Check the Database Version with DBMS_DB_VERSION
Read the database version and release as PL/SQL constants, and use them to compile the right code for each release.
How to Generate Random Data with DBMS_RANDOM
Create random numbers, strings, and test rows, shuffle query results, and get repeatable sequences with a seed.
How to Use User Locks with DBMS_LOCK
Make sure only one session runs a process at a time with named user locks, and read the result codes REQUEST and RELEASE return.
How to Reset Package State with DBMS_SESSION.RESET_PACKAGE
Throw away package variables and caches in the current session with RESET_PACKAGE or MODIFY_PACKAGE_STATE, and start clean.
How to Use DBMS_SESSION in Oracle
Identify end users, change NLS settings, check roles, pause, and set application context values for the current session.
How to Report Progress with SET_SESSION_LONGOPS
Publish how far a long PL/SQL batch job has got, with a message and a time estimate, in V$SESSION_LONGOPS.
How to Track Sessions with DBMS_APPLICATION_INFO
Tell DBAs and monitoring tools what each session is doing by setting module, action, and client information from your code.
How to Expand Views with DBMS_UTILITY.EXPAND_SQL_TEXT
Replace every view and table SQL macro in a query with the SQL it stands for, to see what the database really runs.
How to Resolve Object Names with DBMS_UTILITY.NAME_RESOLVE
Find out what an object name really refers to, through schemas and synonyms, and split or canonicalize names in PL/SQL.
How to Split CSV Lists with DBMS_UTILITY.COMMA_TO_TABLE
Turn a comma-separated list of names into a PL/SQL table and back, and see why numbers and other values are rejected.
How to Time Code with DBMS_UTILITY.GET_TIME
Measure how long PL/SQL takes with GET_TIME and GET_CPU_TIME, and compare the two to tell computing from waiting.
How to Read the Call Stack with UTL_CALL_STACK
Find where your PL/SQL code is running and where an error came from, as separate values with subprogram names inside packages.