Long time no see



By Patch ~ October 26th, 2012. Filed under: APEX, Oracle, PL/SQL, SQL.

It’s been quite some time since my last post. It’s not that I haven’t been busy, just didn’t find the time or a subject to write a blog about. Until now.

I came up with something I didn’t know. I have a pipelined table function where I want to suppress a record from appearing when something is wrong (no data found or something like that). I know, suppressing errors is wrong, but in this case I will log the error. I just didn’t want it to appear in my data because the receiving application will generate errors and reject the entire set of data.

The easy way, of course, is not to pipe a row at all, but that would mean adding a lot of complexity to my code so I decided to try something different. Just set the record to NULL before it gets piped out. Adding this worked for my situation but it made me wonder: Is there an empty record added to the ‘table’?

This is why I wrote a simple script to try this:

   1:  CREATE OR REPLACE TYPE dummy_t AS OBJECT
   2:  (
   3:    dummy_id   NUMBER,
   4:    dummy_name VARCHAR2(30)
   5:  )
   6:  /
   7:  CREATE OR REPLACE TYPE dummy_ntt AS TABLE OF dummy_t
   8:  /
   9:  CREATE OR REPLACE FUNCTION dummy_f RETURN dummy_ntt
  10:    PIPELINED IS
  11:    l_returnvalue dummy_t;
  12:  BEGIN
  13:    l_returnvalue := dummy_t(1, 'Patrick');
  14:    PIPE ROW(l_returnvalue);
  15:    l_returnvalue := NULL;
  16:    PIPE ROW(l_returnvalue);
  17:    l_returnvalue := dummy_t(2, 'Mitchell');
  18:    PIPE ROW(l_returnvalue);
  19:    RETURN;
  20:  END dummy_f;
  21:  /
  22:  SELECT *
  23:    FROM TABLE(dummy_f)
  24:  /

As you can see, there are three PIPE ROW statements so you might expect three rows in the result. But luckily Oracle is smart enough to suppress the empty row:

DUMMY_ID DUMMY_NAME
———- ——————————
1 Patrick
2 Mitchell

This is what I hoped would happen and luckily it did so.

What else have I been up to?
I am currently in the process of rating abstracts for KScope13 being a Track Lead for the Developer’s Toolkit.
I have published a couple of articles on the AllThingsOracle website with the latest addition being an article on Edition Based Redefinition (part 1, part 2 hopefully next week). Other articles I wrote here can be found at this link.
Besides that
I’m still playing around with APEX trying to rebuild a program I wrote a long time ago in PHP using MySQL to build my own version of Delicious.

Reader's Comments

  1. John Campos | October 31st, 2012 at 2:59 pm

    I am using Oracle 11.2 and got the following results w/ your example:

    DUMMY_ID DUMMY_NAME
    ———- ——————————
    1 Patrick

    2 Mitchell

    3 rows selected.

    it appears to show the blank row in my case.

  2. Patch | November 2nd, 2012 at 9:00 am

    Thanks John,

    after your comment I noticed the same. Seem my IDE (PL/SQL Developer) hides the empty row. Maybe I should suppress the row myself anyway.

    best regards,
    Patrick

Leave a Comment