Obsessed with Oracle PL/SQL

On the PL/SQL Challenge , when you click on a link to play a quiz, you are taken to the "launch" page. We give you an opportunity to review assumptions and instructions, and then press the Start button when you are ready (your score is based in part on the time it takes you to answer). However, if you've taken that particular quiz before, and there have been no changes to assumptions or instructions, the launch page just gets in the way. So I decided to streamline the flow on our site as follows: 1. If a person has never taken this quiz before, go to the launch page. 2. Otherwise, if assumptions or instructions have changed since the last playing of the quiz, go to the launch page. 3. Otherwise, go straight to the Play Quiz page. I figured the way to do this is build a function that will be invoked from Oracle Application Express. Here is a first pass, using the top-down design technique, at implementing the function. CREATE OR REPLACE PACKAGE q...

Process Global Area (PGA) memory is utilized to store and manage session-specific data (compared to the System Global Area or SGA, which manages cross-session memory; lots more details here ). If a session uses too much PGA, it can fail with the "ORA-04030: out of process memory when trying to allocate..." error. PL/SQL offers several techniques for reducing PGA consumption, including pipelined table functions, the LIMIT clause of BULK COLLECT, and the NOCOPY hint. The 2015 Oracle PL/SQL Championship at the PL/SQL Challenge offered a quiz on this topic. You can take the entire championship to test your own knowledge by registering at this quiz website and then clicking on the Tests tab. In this post, I point how techniques for PGA reduction using the code from that quiz. I have a table with lots of data: CREATE TABLE plch_data ( data_id INTEGER PRIMARY KEY, data_name VARCHAR2 (1000) UNIQUE) / BEGIN INSERT INTO plch_data (data_id, data_name) S...

[That title is, by the way, a fairly lame play on the American Express "Don't leave home without it." tagline. Sorry, can't help myself. SF] The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed to obtain the values of the changed columns. The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form. You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement. I explore the feature below. You can execute the same code on LiveSQL . Set Up Some Data First, let's create some data with which to play. CREATE TABLE parts ( part_number INTEGER , part_name VARCHAR2 (100) ) / BEGIN INSERT INTO parts VALUES (1, 'M...

[April Fools Joke] Today, the Oracle Database Developer Advocates team announces the release of PLSQL.js , a PL/SQL framework for JavaScript developers, delivering all the power and flexibility of JavaScript though simple, declarative APIs written in the best database programming language in the world. “The first key advantage to PLSQL.js is that you don’t have to write those little squiggle thingies,” notes Steven Feuerstein, well-known author and trainer on Oracle PL/SQL, who designed the bulk of PLSQL.js. “We really didn’t see the point. Why not use regular English words and the kind of punctuation everybody was already used to, like underscores and dots? Why do we always have to change things?” Oracle’s Chief Healthification Officer, Jean Frutesandveggies, adds that PLSQL.js is also an attempt to help young application developers deal with a growing epidemic of  Javascript Fatigue . "While PLSQL.js appears to be yet-another-framework that just compounds the problem,...