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