{"id":572,"date":"2012-10-26T06:27:57","date_gmt":"2012-10-26T06:27:57","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=572"},"modified":"2012-10-26T06:27:57","modified_gmt":"2012-10-26T05:27:57","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=572","title":{"rendered":"Long time no see"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

<\/p>\n

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’?<\/p>\n

This is why I wrote a simple script to try this:<\/p>\n

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