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.
AI-Assisted
Posts tagged AI-Assisted are created with support from artificial intelligence. I use AI tools to research, draft, and refine ideas—combining human insight with machine precision to deliver clear, informed, and creative content.
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.
How to Compile PL/SQL Natively and Wrap Source Code
Speed up compute-heavy PL/SQL with native compilation, and hide the source of procedures and package bodies by wrapping them.
How to Use PL/SQL Compiler Warnings
Find code that compiles but is probably wrong, such as unreachable lines, with PLSQL_WARNINGS, and make the worst ones errors.
How to Use Conditional Compilation in PL/SQL
Keep debug code and version-specific code in one PL/SQL source, and choose what is compiled with PLSQL_CCFLAGS and DBMS_DB_VERSION.
How to Write Table SQL Macros in Oracle
Build parameterized views and reusable query shapes with functions marked SQL_MACRO(TABLE), which Oracle expands into the calling query.
How to Write Logon Triggers in Oracle
Record who connects with AFTER LOGON triggers and keep a history of schema changes with AFTER DDL triggers, and manage your triggers.
How to Use Lock-Free Reservations in Oracle
Let many transactions update seats, stock, or balances on the same row at once with reservable columns, while constraints still hold.
How to Find Deadlocks in Oracle
See how a deadlock happens and how Oracle breaks it with ORA-00060, where it is recorded, and how to keep deadlocks from recurring.
How to Lock Rows with SELECT FOR UPDATE in Oracle
Lock the rows you read until you update them with SELECT FOR UPDATE, and use NOWAIT, WAIT, and SKIP LOCKED to control waiting.