Tuesday, April 28, 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 of thing, competing with others).

Yes, competing. Humans love to compete (some of them, mostly I suppose men). They love to be ranked. They love to win. And we give them that opportunity on the PL/SQL Challenge.

We offer lots of different kinds of rankings: by player, by country, by company, by affiliation (e.g., user group). You can choose the competition, the date range, etc. Because of this wide variety, we decided to utilize APEX's fantastic Interactive Reports feature.

One downside of Interactive Reports, however, is that they do not support a dynamic (constructed at runtime) query. Ranking reports run off of a series of materialized views, such as mv_qdb_rankings_q (quarterly rankings), so in our first pass we ended up with lots of different reports built on views that were built on top of the materialized views:


The report query itself was simple enough:


Still, you had to create a report, format the columns, etc. It was tedious work but somebody had to do it. Oh, not me. No, no. My son, Eli, who was the main APEX developer on the project. :-)

So "we" got the reports done, users used them, users were happy. But then....then it was time to enhance the reports. And then we both stared at those over-two-dozen reports and thought about having to go through each one of them apply the same changes to each one of them....

And suddenly we (with Eli definitely taking the lead here) were sure (desperate) that there had to be a better way, and we'd better figure out what that better way was.

Ideally, we would have just one report that would handle all of the variations we needed, in a way that the users would find easy to use.

Ideally, we would have a single view and just pass "parameters" to it, to alter its behavior and change the result set returned.

Sadly, views do not take parameters. But, wait....functions take parameters. If only we could call a function in the query.....well, we can! We can call a table function!

[Note to those who have never seen me present: those exclamation marks are real. I actually do get all excited about writing cool code in PL/SQL, even after multiple decades of doing so.]

Now, some of you might be thinking: why would you need a table function? Why not just use a series of "mutually exclusive" queries combined with a UNION ALL?

That is certainly a possibility, and in fact we did this for our player (as opposed to company, organizational , etc.) rankings, because the code required to construct the dataset via the table function turned out to be not all that complicated. The mutually exclusive UNION ALL query looks (in part) like this (all the MV-specific elements in red):

 SELECT yyyy,
       qdb_user_mgr.published_name_no_points (u.user_id),
       qdb_user_mgr.published_name (u.user_id),
       u.country,
       u.company_name,
       qdb_utilities.expertise_level (expertise_level_id),
       overall_rank,
       previous_rank,
       rank_improvement,
       overall_percentile,
       ROUND (pct_correct_answers, 1),
       user_score,
       user_wscore,
       qdb_utilities.seconds_to_duration (user_seconds, 0),
       best_possible_score,
       best_possible_wscore,
       total_quizzes,
       ROUND (total_quizzes / potential_quizzes * 100)
  FROM mv_qdb_rankings_y mv, qdb_users u
 WHERE     mv.user_id = u.user_id
       AND mv.competition_id = :p442_competition_id
       AND yyyy = :p442_period
       AND :p442_period_type = 'Y'
UNION ALL
SELECT yyyy_q,
       qdb_user_mgr.published_name_no_points (u.user_id),
       qdb_user_mgr.published_name (u.user_id),
       u.country,
       u.company_name,
       qdb_utilities.expertise_level (expertise_level_id),
       overall_rank,
       previous_rank,
       rank_improvement,
       overall_percentile,
       ROUND (pct_correct_answers, 1),
       user_score,
       user_wscore,
       qdb_utilities.seconds_to_duration (user_seconds, 0),
       best_possible_score,
       best_possible_wscore,
       total_quizzes,
       ROUND (total_quizzes / potential_quizzes * 100)
  FROM mv_qdb_rankings_q mv, qdb_users u
 WHERE     mv.user_id = u.user_id
       AND mv.competition_id = :p442_competition_id
       AND yyyy_q = :p442_period
       AND :p442_period_type = 'Q'

I expect you get the idea from there. It's a long query (well, 143 lines, not all that crazy long), but it's fairly straightforward. No need for a table function.

When it came to the other reports, however, the table function came in very, very handy - because the logic and variations between the reports was more substantial.

In this post, I will explore how we applied table functions in our application, and go as far as the specification of the table function. In my next post, I will dive into the implementation of that function. 

Before looking at specification of the function, allow me to share with you the single report query that replaced sixteen  interactive reports:


Hopefully you can see why we talk about queries based on table functions as a kind of parameterized view. I am passing argument values to the parameters of the function. The function then returns the dataset as rows and columns. So from the standpoint of a developer building the report, we have kept (made) things very simple. All the complexity has been moved inside the function.

So let's look at the function. First, as explained in the 2nd post in this series, if I want to pass back more than a single scalar value from my table function, I need to create an object type and a nested table type based on that object type. Here goes:

CREATE OR REPLACE TYPE ir_other_ranking_ot IS OBJECT
(
   period VARCHAR2 (500),
   type_name VARCHAR2 (300),
   num_of_players INTEGER,
   top20 INTEGER,
   top100 INTEGER,
   score INTEGER,
   wscore INTEGER,
   pct_correct_answers NUMBER,
   answer_time VARCHAR2 (500),
   best_possible_score INTEGER,
   best_possible_wscore INTEGER,
   total_quizzes INTEGER,
   perc_taken NUMBER,
   score_per_quiz INTEGER,
   overall_rank_wscore INTEGER,
   overall_rank INTEGER
);
/

CREATE OR REPLACE TYPE ir_other_ranking_nt
   IS TABLE OF ir_other_ranking_ot
/

Then I define a function that accepts the necessary parameters and returns a collection of that type.

CREATE OR REPLACE PACKAGE BODY qdb_rankings
IS
   FUNCTION ir_other_ranking_tf (category_in         IN VARCHAR2,
                                 period_type_in      IN VARCHAR2,
                                 competition_id_in   IN INTEGER,
                                 period_in           IN VARCHAR2)
      RETURN ir_other_ranking_nt;
END;
/

So the public-facing side of my function is done. Time to dive inside! Unfortunately, I lead a busy, busy life at Oracle and right now I am on deadline to complete my next article for Oracle Magazine. Consequently, please accept my apologies for publishing this post without getting into the "good stuff." I promise that it is coming right along!

Links to Table Function Series

Wednesday, April 22, 2015

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?

Friday, April 17, 2015

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 love thee better after death.

And now that many developers have contributed their examples and stanzas, I offer a greatest hits compilation:

How Do I (or Oracle) Raise NO_DATA_FOUND? 

How do I raise NO_DATA_FOUND? Let me count they ways.

I queried a row, but I was not able,
Then I looked for the wrong entry in a PL/SQL table.


I had a file, and its content was Divine.
Read past the end, line by line.

Most times Oracle does the trick,
But I can also raise it, through thin and thick.

Four dreams in my heart I opened to thee,
Reached though for a fifth and were lost unto me.

Oracle always adds something new,
So, what else is left for a mere mortal like you?
Just learn, play the PL/SQL Challenge and try.
Then you will better understand everything, how and why.

Wednesday, April 15, 2015

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
   IS TABLE OF VARCHAR2 (4000)
/

CREATE OR REPLACE FUNCTION tabfunc_concat
   RETURN two_values_in_one_t
IS
   l_return   two_values_in_one_t := two_values_in_one_t ();
BEGIN
   l_return.EXTEND (2);
   l_return (1) := '143-Feuerstein';
   l_return (2) := '147-Silva';

   RETURN l_return;
END;
/

SELECT COLUMN_VALUE 
  FROM TABLE (tabfunc_concat ())
 ORDER BY COLUMN_VALUE
/

COLUMN_VALUE
-------------------------
143-Feuerstein
147-Silva

But wait! That's just a single value, not two values. So what I really need is something more like this:

  SELECT SUBSTR (COLUMN_VALUE, 1, INSTR (COLUMN_VALUE, '-') - 1)
            employee_id,
         SUBSTR (COLUMN_VALUE, INSTR (COLUMN_VALUE, '-') + 1)
            last_name
    FROM TABLE (tabfunc_concat ())
ORDER BY employee_id
/

I could even put it inside a view:

CREATE OR REPLACE VIEW two_values_v
AS
     SELECT SUBSTR (COLUMN_VALUE,
                    1,
                    INSTR (COLUMN_VALUE, '-') - 1)
               employee_id,
            SUBSTR (COLUMN_VALUE, INSTR (COLUMN_VALUE, '-') + 1)
               last_name
       FROM TABLE (tabfunc_concat ())
   ORDER BY employee_id;

SELECT * FROM two_values_v
 ORDER BY employee_id
/

EMPLOYEE_ID LAST_NAME
----------- -------------
143         Feuerstein
147         Silva

Is that slick or what?

Actually, well, "what". Not very slick at all. Ugly. Cumbersome. Error prone.

What if, for  example, an employee's name was hyphenated, as in "Silva-Feuerstein"?

So, yes, sure, you could take this approach when you need to pass back multiple values per row, but it's awfully kludgy and will not enhance your reputation with other developers on your team.

Which means it's time for....another way not to do it. :-)

Suppose I have a package defined as follows:

CREATE OR REPLACE PACKAGE two_values_pkg
AS
   TYPE two_values_rt IS RECORD
   (
      employee_id   INTEGER,
      last_name     VARCHAR2 (100)
   );
END;
/

Then it seems perfectly reasonable and "the way to go" to take the following steps:

1. Create a nested table type based on the record type.

/* Warning! This will not work. */
CREATE OR REPLACE TYPE two_values_nt
   IS TABLE OF two_values_pkg.two_values_rt
/

2. Build the function around that nested table, populating values of fields of each of two records:

CREATE OR REPLACE FUNCTION tabfunc_no_concat
   RETURN two_values_nt
IS
   l_return   two_values_in_one_t := two_values_in_one_t ();
BEGIN
   l_return.EXTEND (2);
   
   l_return (1).employee_id := '143';
   l_return (1).last_name := 'Feuerstein';
   
   l_return (2).employee_id := '147';
   l_return (3).last_name := 'Silva';

   RETURN l_return;
END;
/

Yes, it seems so reasonable - at first glance. And yet when I try to create that type, I see:

PLS-00201: identifier 'TWO_VALUES_PKG.TWO_VALUES_RT' must be declared

Must be declared? But it is declared. The following block, for example, executes without any kind of compilation error (when you see "PLS" you know that is a compilation error):

DECLARE
   r   two_values_pkg.two_values_rt;
BEGIN
   NULL;
END;
/

Yes, well. Let's face it: PL/SQL is very gracious about understanding and leveraging PL/SQL. SQL, on the other hand, is a bit arrogant. Sure, it will recognize and execute functions declared at the schema level (CREATE OR REPLACE FUNCTION) or in a package specification. But outside of that, SQL really doesn't want to have anything to do with PL/SQL.

OK, kidding aside. It is worth remembering that "PL/SQL" stands for Procedural Language extensions to SQL. So of course it must know about and support SQL - that is, in fact, one of the most wonderful things about PL/SQL: native support for SQL.

But SQL exists independently of PL/SQL and so it would make sense that it does not recognize a whole boatload of procedural constructs from The Language That Changed My Life.

Bottom line: you cannot reference a PL/SQL record type (even one defined with %ROWTYPE against a database table) as the datatype of a schema-level nested table.

Bummer, 'cause that's a fast and easy way to do things.

Instead, I must dip my toe into the world of object-oriented programming in PL/SQL and create an object type to serve as the base datatype for the nested table.

[Note: there is an exception to this rule, as noted by Patrick Barel in comments below. If you are defining a pipelined table function, then you can define your nested table type in a package specification, based on a record type.]

Now, if you are one of the 99% of PL/SQL developers who are not comfy with object orientation and have never had the inclination or reason to learn about PL/SQL's OO features, don't worry! You will not have to do so in order to use object types with your table functions.

You will, in essence, create an object type, just like you create a relational table or a record type, defining an attribute (analogous to a record's field) for each distinct value you want returned in the row from the table function.

To do this, you do not need to understand object-oriented principles, nor pretend to get all excited about dynamic polymorphism.

Instead, create the object type:

CREATE OR REPLACE TYPE two_values_ot AS OBJECT
   (
      employee_id   INTEGER,
      last_name     VARCHAR2 (100)
   );
/

Then create a nested table based on that type:

CREATE OR REPLACE TYPE two_values_nt
   IS TABLE OF two_values_ot
/

Then return that nested table type in the function and populate the nested table with two instances of that type:

CREATE OR REPLACE FUNCTION tabfunc_no_concat
   RETURN two_values_nt
IS
   l_return two_values_nt := 
      two_values_nt (two_values_ot (143, 'Feuerstein'),
                     two_values_ot (147, 'Silva'));
BEGIN
   RETURN l_return;
END;
/

and then....voila!

SELECT employee_id, last_name 
  FROM TABLE (tabfunc_no_concat ())
 ORDER BY employee_id
/

EMPLOYEE_ID LAST_NAME
----------- -------------
143         Feuerstein
147         Silva

Notice:

1.  That hard-coded COLUMN_VALUE is no longer a part of the equation. The names of the object type attributes become the names of the "columns" in the SELECT list. You can reference those names in the WHERE clause, ORDER BY clause, etc.

2. In this latest table function, I took advantage of the constructor functions that come with both nested tables and object types to set all the desired values to be returned by the table function right in the declaration section. This is not something you can do with records and associative arrays. That's very handy, but practically speaking will not likely be the way you build your datasets within a table function.

So now you know the secret handshake: when your table function needs to return rows with more than one column, create an object type that defines the desired structure, build the nested table on that object type, and then have the table function return a collection that that nested table type.

Links to Table Function Series

Thursday, April 9, 2015

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.

OK....now, clever PL/SQL developers, can you post an example of code that will solve this teaser?

Wednesday, April 8, 2015

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 to avoid code redundancy and minimize the number of interactive reports needed to provide rankings.

4. Streaming table functions: these kinds of table functions are used typically in data warehousing environments, in which one or more transformations of data are needed all "streamed" within a single SQL statement.

5. Pipelined table functions: how they differ from non-pipelined, what they look like, why you would use them.

6. Optimizing execution of table functions: it helps to provide the cost-based optimizer with some additional information so that it can come up with the best plan.

The Basic Idea

I assume you are familiar with the SQL SELECT statement:

SELECT column_list FROM table_or_view1 [, table_or_view2...]

Several versions back of Oracle Database, Oracle added the table operator, which transforms a collection's contents into a relational dataset that can be consumed by a SELECT. The syntax looks like:

SELECT column_list FROM TABLE (my_collection)

and you can also call a function from within the TABLE operator that returns a collection, as in:

SELECT column_list FROM TABLE (my_function (...))

Which means you can even create a view (sometimes called a "parameterized view", since you can pass values to the function's parameter list) based on a TABLE operator:

CREATE VIEW my_view AS 
   SELECT column_list FROM TABLE (my_function (...))

and you can "blend" together in the FROM clause table function calls, tables and views, as in:

SELECT column_list 
  FROM TABLE (my_collection) t1,
       employees e,
       TABLE (another_collection) t2

'cause at that point, it (the dataset returned by a table function) is just the same as the dataset from a table or view. You can use ORDER BY, GROUP BY, etc. on the data returned from the function.

Prior to Oracle Database 12c Release 1, only nested tables and varrays could be consumed by the TABLE operator, and their types had to be defined at the schema level (CREATE TYPE) or in a package specification.

In 12.1 and higher, you can also use TABLE with integer-indexed associative arrays (also known as "IBIs" because of their INDEX BY clauses), one of my favorite 12.1 enhancements for PL/SQL.

By the way, I expect it is rather clear by now that you need to know about PL/SQL collections in order to use table functions. You don't need to necessarily know all the details, but here are some links to help you along:

Collections documentation

PL/SQL Channel videos on collections (5+ hours' worth!)

Use Cases

I am hoping that your eyes lit up at even the most basic presentation of this feature above, and your mind is spinning with ideas of how to  use table functions. To help you along, though, I offer the following list, all of which will be explored as we move through this series.

Merge session-specific data with data from tables

You've got data, and lots of it sitting in tables. But in your session (and not in any tables), you have some data - and you need to "merge" these two sources together in an SQL statement. In other words, you need the set-oriented power of SQL to get some answers.

With the TABLE operator, you can accomplish precisely that.

Programmatically construct a dataset to be passed as rows and columns to the host environment

Your webpage needs to display some data in a nice neat report. That data is, however, far from neat. In fact, you need to execute procedural code to construct the dataset. Sure, you could construct the data, insert into a table, and then SELECT from the table.

But with a table function, you can deliver that data immediately to the webpage, without any need for non-query DML.

Create (what is in effect) a parameterized view

One of my favorites, and arises (for me) directly from my work on the PL/SQL Challenge. We have lots of different ranking reports, based on different materialized views (but all very similar in their columns and the way the data is computed). Used to be, we created something like 25 different interactive reports in Application Express.

Then, when facing the need to enhance each and every one of those, we stepped back and looked for ways to avoid this repetitive mess. The answer lay in a table function. Since you call a function in the FROM clause, you can pass parameters to the function and therefore to the query itself. That flexibility made it possible to replace those 25 different reports with just 1 report, built on that "parameterized view."

Improve performance of parallelized queries (pipelined table functions)

Many data warehouse applications rely on Parallel Query to greatly improve performance of massive ETL operations. But if you execute a table function in the FROM clause, that query will serialize (blocked by the call to the function). Unless, unless....you define that function a a pipelined function and enable it for parallel execution.

Reduce consumption of Process Global Area (pipelined table functions)

Collections (which are constructed and returned by "normal" table functions) can consume an  awful lot of PGA (Process Global Area). But if you define that table function as pipelined, PGA consumption becomes a non-issue.

Do you know of others? Tell me in a comment on the post and I will add them to this post and give you credit (of course)!

Simple Examples 

I will close out this initial post of the series with some examples of the simplest sort of table functions: those that return a collection of scalar values - a list of strings, dates, numbers, etc.

Since a table function returns a collection, the type of collection must first be defined. Prior to 12.1, that type must be defined as a schema-level type, such as:

CREATE OR REPLACE TYPE list_of_names_t
   IS TABLE OF VARCHAR2 (100);
/

And that's all I need to have some fun. I will now define a function that returns the collection:

CREATE OR REPLACE FUNCTION my_family
   RETURN list_of_names_t
IS
   happyfamily   list_of_names_t
                    := list_of_names_t ('Veva',
                                        'Chris',
                                        'Lauren',
                                        'Loey',
                                        'Eli',
                                        'Steven');
BEGIN
   RETURN happyfamily;
END;
/

And then....I can SELECT FROM that function!

SELECT COLUMN_VALUE family_member
  FROM TABLE (my_family () )
/

FAMILY_MEMBER
-------------------
Veva
Chris
Lauren
Loey
Eli
Steven
Note that when you return a collection of scalars, Oracle automatically uses "COLUMN_VALUE" for the name of the column. You can change it to whatever you'd like with a column alias.
Starting with 12.1, you can also define a collection type returned by a table function in a package specification, and then reference that collection inside a TABLE operator (note one exception to this rule: use of the collection with pipelined table functions):

CREATE OR REPLACE PACKAGE plch_pkg
IS
   TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
END;
/

CREATE OR REPLACE FUNCTION my_family
   RETURN plch_pkg.list_of_names_t
IS
   /* Only works on 12.1 and higher */
   happyfamily plch_pkg.list_of_names_t
                    := plch_pkg.list_of_names_t ('Veva',
                                        'Chris',
                                        'Lauren',
                                        'Loey',
                                        'Eli',
                                        'Steven');
BEGIN
   RETURN happyfamily;
END;
/

DECLARE
   l   INTEGER;
   t   plch_pkg.list_of_names_t := my_family ();
BEGIN
   SELECT COUNT (*) INTO l FROM TABLE (t);

   DBMS_OUTPUT.put_line (l);
END;
/

Note, however, that as of 12.1, you cannot call the table function directly inside the TABLE operator. You must invoke it in PL/SQL, assign result to a variable, and then reference the variable inside TABLE. [thanks to Iudith Mentzel for reminding me of this point - see Comments]

And if you try this prior to 12.1, you will see this error:

ORA-21700: object does not exist or is marked for delete

So upgrade to Oracle Database 12c, already, willya? :-)

As I'd mentioned earlier, once you've stuck that table function inside TABLE inside FROM, well, it's a query like any other. You can create a view over it, you can join with other tables, use it with set operators, etc.:

CREATE VIEW family_members_v
IS
   SELECT COLUMN_VALUE family_member
     FROM TABLE (my_family () )
/

View created

DECLARE
   happyfamily list_of_names_t := 
      list_of_names_t ('Larry', 'Mark', 'Safra');
BEGIN
   FOR rec IN (  SELECT COLUMN_VALUE a_name
                   FROM TABLE (happyfamily)
                 UNION
                 SELECT last_name FROM employees
                  WHERE)
   LOOP
      DBMS_OUTPUT.put_line (rec.a_name);
   END LOOP;
END;
/

De Haan
King
Kochhar
Larry
Mark
Safra

DECLARE
   happyfamily list_of_names_t := 
      list_of_names_t ('Kingston', 'Bellman');
BEGIN
   FOR rec IN (  SELECT DISTINCT e.last_name
                   FROM TABLE (happyfamily) hf, employees e
                  WHERE hf.COLUMN_VALUE like e.last_name || '%')
   LOOP
      DBMS_OUTPUT.put_line (rec.last_name);
   END LOOP;
END;
/

King
Bell

I am not, by the way, claiming that the last example makes any sense. Just showing that you can do it.

And there you have it: a quick introduction, description of (some) use case, and a set of simple examples.

Next in series: Explore table functions that return more than one value per row (via object types).

Questions? Ask 'em below!
Complaints? State 'em below!
Suggestions for improvement? Let 'em rip!

And if you'd like to dive right in to more interesting examples, download my demo.zip and check out the files starting with "tabfunc".

Links to Table Function Series

Monday, April 6, 2015

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.  Beginning with 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 center; and wrote lots of JavaScript.

While using these technologies, I started to drift away from Oracle.  The prevailing viewpoint in these communities seemed to be that Oracle is wrong, in one way or another, for open source technologies.  Most of them prefer a data storage solution that is "free" -- if you just put in enough effort.  I spent quite a lot of time bumping up against one "small" problem or another, getting increasingly grumpy along the way, because I knew how to solve the problem in an Oracle database, sometimes with something as simple as a synonym.

Last month, I saw the posting for the Oracle Developer Advocate for Open Source position and my first thought was "It would be awesome if that were real – if Oracle was really serious about reaching out to open source communities." I decided to give it a shot and see what it's all about.  I spoke with a lot of people both in and out of the team. Everyone was so excited about the future that I became convinced it is for real....and that I really wanted to be a part of the effort.

Fortunately, Steven Feuerstein and Mike Hichwa felt the same away about me, and I started my new position on April 1st – and that's no joke!

I think this is going to be one of the most challenging jobs I've ever taken on – and that's exciting!  The way I see it, we have a couple things to accomplish:

1. We need to show open source application developers that Oracle is not the enemy.  We need to help them see that they can explore and build with these languages, but still use proven, powerful Oracle Database technology.  

It's always seemed odd to me that lots of developers shift to open source to expand their knowledge, tool set and options, but then get locked into a specific stack and a kind of "default" database.  With JavaScript, that might be MEAN, with Ruby it's PostgreSQL, with PHP it's LAMP.  We need to help people avoid the mistake I made thinking that learning something new means learning everything new.

2. On the other hand, even though I believe Oracle Corporation is committed to open source, some inside the company might still see open source as the enemy.  We need to help Oracle get more involved in the open source communities, get some pull requests submitted, attend and present at meetups, and show the world we're serious – about having fun with open source. J

I see the work I will be doing is kind of bridge building, between Oracle and open source communities.

I intend to help the open source communities see that when they chose the best language for their project they don't have to be locked into that language's default data store.  They can still choose the very best database for their project.  Sure, that might not always be Oracle Database, but when it is, it should be easy for them to integrate Oracle Database into their technology stack.  

And I intend to help my fellow Oracle technologists understand why we are sometimes seen as a bad guy, and work with them to undo that image by making Oracle Database and its many fantastic appdev features more accessible and easier to use. Our Cloud Database will be huge in making this shift happen.


Another part of my bridge building is reaching out to other bridge builders: Oracle technologists also working in the open source space. So I'd love to hear about your experiences, your ideas, and how I –and the entire Oracle Developer Advocate team – can help.

You can reach Blaine at blaine.carter@oracle.com.