view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Java Generics in Oracle

Somebody posed the question about using a Comparator in the sorting examples provided in this earlier post on Updating Table View Columns (columns using a Varray or Nested Table of a single scalar data type). It seems the individual thought that you can’t use Java Generics inside an Oracle Database 11g’s Java libraries. It’s seems odd since they’ve been around since Java 5.

Exploring Mutating Table Errors and FORALL (9619)

The 5 January quiz tested players' knowledge of the fact that the mutating table error (ORA-04091) is raised differently for different ways of performing inserts and with a BEFORE row-level trigger.

Iudith Mentzel took the quiz as a starting point for some very interesting analysis, which I share here.

Hello Steven,

Ever wanted to debug lobs, object types, XML docs in your IDE?

"Exotic" datatypes, like large objects, object types, XML documents, etc. are being used by more and more Oracle development teams. And those teams, of course, are using one of many different IDEs for PL/SQL, including Toad, SQL Navigator, PL/SQL Developer, SQL Developer, etc. 

These IDEs come with source code debuggers, but unfortunately Oracle's implementation of the Java Debug Wire Protocol (JDWP) comes with a number of limitations, including:

Fact Mining and the Weekly Logic Quiz (10983)

In addition to the PL/SQL, SQL and APEX quizzes, the PL/SQL Challenge offers a weekly logic puzzle, modeled on the Mastermind game.

In the puzzle for the first week of 2012, the second choice (9084) stated:

"If 1 is in the solution, then 7 cannot be in solution."

This was scored as correct and offered a step-by-step logical "proof" of why this was so.

Hapy New Year and Participants in Q4 2011 Playoff

I hope you enjoyed some relaxing and joyful times with family and friends...ah, but it is all over so fast. Fortunately, at least in Chicago, it doesn't seem like winter has actually arrived. Today, 5 January, the temperature is in the 50s (Fahrenheit; 10 degrees Celsius). I tore myself away from my beloved laptop to spend time on my bicycle. Ah, that was very nice...

Well, regardless of the temperature, it's time to get back to work - and fun: back to answering PL/SQL, SQL and APEX quizzes...and it's also time for the Q4 2011 championship playoff.

Is it Onion or is it Science?

I hope that you are aware of the Onion, a hilariously satirical newspaper and website. It continually amazes me how funny and sharp they can be, week after week.

For example:

APEX: Friendlier exception message from AJAX call

While it is very easy to create a "Form and a Report" on a single table (or view), just follow the wizard, for end users it is not always intuitive that they should navigate to the form page to remove the record.

Understanding Java Enum

Somebody wanted an example of how to write an Enum class in Java 7 (a bit late since its introduced in Java 5) because they found the Enum tutorial unhelpful (not as helpful to their purpose at hand). They wanted to understand how to use an Enum type in another class. Here’s an example set of files to do that and here’s the link to the jazzed up Java 7 API online docs).

Generate multiple rows

For the first post of the year, one of my New Year's resolutions is to write more posts than last year which shouldn't be too hard, I wanted to collect some different ways of generating multiple records. Sometimes I need, for whatever reason, generate multiple records. These are a few different ways of doing so.This is not really "generating multiple rows", but just a simple way of having multiple lines of output. Just take a big table and limit the number of rows by using "where rownum <= ":


SQL> select 'Happy New Year' msg

What is a "Generic" Oracle Error Message? (9614)

The 29 December quiz tested your knowledge of the differences between various ways of raising errors and communicating error messages to users. It was a word-based quiz, as opposed to one that is mostly code so - no surprise - a number of players raised objections to some of the phrasing and scoring.

Let's go through these objections and see how much we can learn about PL/SQL through the process. Player comments are in blue. My response is in purple.

view counter