Logger alternative to dbms_output Oracle Log4j

Simple logger.write alternative to dbms_output.put_line. Call with: logger.write ( ‘hello world!’ ) ;or even just: logger.write ; It does the dbms_output.put_line but also writes asynchronously to a log_table, and gives info on calling line number and procedure name.

Read More Logger alternative to dbms_output Oracle Log4j
May 29, 2023

Logger Oracle PL/SQL

A logger.write procedure as an extension to dbms_output.put_line. It does a dbms_output.put_line, but also writes same message into a log table. Unlike dbms_output, the writes to the log table are visible while the program is running, via an asynchronous autonomous transaction. Output (both types) includes the line number and the name of calling package/procedure, for […]

Read More Logger Oracle PL/SQL
October 20, 2022

Analytics row_number to identify first parent row in oracle pl/sql cursor loop

Let’s say you have 10m parent rows and 11m child rows and need to process them row-at-a-time in pl/sql. Two methods of doing that are below. The second method will fire the combined select just the once, picking up all 11m rows in one go. The first method will fire the child select 10m times […]

Read More Analytics row_number to identify first parent row in oracle pl/sql cursor loop
October 22, 2014

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters

Result cache functions are a good new feature in Oracle 11g, but they only work with normal functions. A function that returns multiple outputs using OUT parameters cannot be made a result cache function, at least not in 11gR1. Attempting to do so fails with this error: “PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is […]

Read More PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters
May 5, 2010

Introduction to PL/SQL By Example

Here are online course notes, with code examples, for a simple introduction to Oracle’s PL/SQL language. Was originally aimed especially at Infrastructure DBAs, but would be of use to anyone learning PL/SQL.

Read More Introduction to PL/SQL By Example
March 19, 2009