Showing posts from April, 2015

Table Functions, Part 3a: table functions as parameterized views in the PL/SQL Challenge website

Waaaay back in April 2010, I decided (without really consciously deciding) that I wasn't busy enough. What I needed was the responsibility of support a 24x7 website that offered quizzes on SQL, PL/SQL and more. Well, actually, I decided that a while before April 2010, but April 2010 was when we launched the PL/SQL Challenge.

Over 900,000 answers to thousands of quizzes later, I thank my lucky stars that my original co-founder of the site, Finn Ellebaek Nielsen, suggested that perhaps my daily PL/SQL quiz should take a break on Saturday and Sunday (I was pushing for 7 quizzes a week, because I can be well, quite, insane).

In any case, we have had great fun building the site on Oracle Application Express, packed full of PL/SQL APIs. And hundreds of developers have told us how much they have enjoyed and benefited from what I call "active learning" - not just reading a book or watching a video, but challenging oneself to test one's knowledge (and, if you like that sort o…

PL/SQL Brain Teaser: How can BULK COLLECT be used?

No sonnets, no songs, just a simple question that has mostly obvious answers, but maybe not completely:

What are the different ways (essentially different kinds of statements) that BULK COLLECT can appear in PL/SQL code?

PL/SQL Brain Teaser: Raising NO_DATA_FOUND - let me count the ways

How Do I (or Oracle) Raise NO_DATA_FOUND? 

"Let me count the ways."

The brain teaser is: what are any or all of the ways that the NO_DATA_FOUND exception can be raised in a PL/SQL block?

Extra bonus points if you can provide an example in two rhyming lines, as you will find in the sonnet that inspired this brain teaser:

How Do I Love Thee? (Sonnet 43)

by Elizabeth Barrett Browning, 1806 - 1861

How do I love thee? Let me count the ways.
I love thee to the depth and breadth and height
My soul can reach, when feeling out of sight
For the ends of being and ideal grace.
I love thee to the level of every day’s
Most quiet need, by sun and candle-light.
I love thee freely, as men strive for right.
I love thee purely, as they turn from praise.
I love thee with the passion put to use
In my old griefs, and with my childhood’s faith.
I love thee with a love I seemed to lose
With my lost saints. I love thee with the breath,
Smiles, tears, of all my life; and, if God choose,
I shall but lo…

Table Functions, Part 2: Returning complex (non-scalar) datasets

In my introduction to table functions, I showed how to build and "query" from a table function that returns a collection of scalars (number, date, string, etc.).

If that's all you need to do, well, lucky you!

Most of the time, however, you need to pass back a row of data consisting of more than one value, just as you would with "regular" tables when, say, you needed the ID and last name of all employees in department 10, as in:

SELECT employee_id, last_name
  FROM employees
 WHERE department_id = 10

This post explores how you can go about doing that with table functions.

So, first, let's explore how not to do it. That's usually a bit more entertaining. :-)

Let's take the very simple example above: return an ID and a name. Let's further assume that this data is not readily available from a table - we need a table function to put it all together.

Here's one approach I could take:

CREATE OR REPLACE TYPE two_values_in_one_t

PL/SQL Brain Teaser: compile procedure with two declarations with same name?

I posted my second PL/SQL brain teaser on Twitter today @sfonplsql:

I can compile a procedure successfully even tho it contains two declarations with the same name for the identifier. Can you?

But Twitter is probably not the best way to do this, since it is not easy to capture every nuance in a tweet.

For example, Jonathan Whitehead asked: "Complier directives?"

Which leads me to "tweak" the brain teaser to fully elaborate as follows:

The Brain Teaser

I can compile a procedure successfully even though it contains two declarations with the same name for the identifier. And after compilation, if I run DBMS_PREPROCESSOR.print_post_processed_source to display the actual source code that was compiled into the database, it will show BOTH of those declarations., clever PL/SQL developers, can you post an example of code that will solve this teaser?

Table Functions, Part 1: Introduction and Exploration

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this series.

Here's my plan for the series:

1. I will start (in this post) with some very simple examples and exploration of a few use cases.

2. Explore table functions that return more than one value per row (via object types).

3. Take a look at how the PL/SQL Challenge uses table functions…

Meet Blaine Carter, new Oracle Developer Advocate for Open Source

I am very pleased to announce the latest member to my team of Oracle Developer Advocates: Blaine Carter.

Blaine will focus on using open source application development technologies with Oracle Database. He will be working closely with Dan McGhan, our Oracle Developer Advocate for JavaScript.

Blaine kindly shared the following for your reading pleasure.

Hi, I'm Blaine Carter.
I started programming in 1995.  For most of my career I've used Oracle tools to build applications.  Beginningwith Oracle Forms and Reports, I created an application 100% in Designer 2k.  I helped write and maintain an HTML framework in PL/SQL similar to APEX. I've done a little Java both inside and outside the database, and of course a whole bunch of SQL and PL/SQL.
A few years ago, I started getting interested in other technologies.  I wrote a tiny bit of Perl; explored using Solr and Elastic Search as a database for some small projects; wrote a some more Java; built a project with Ruby for a small Autism…