Obsessed with Oracle PL/SQL

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! 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 sit...

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?

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 Go...

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! 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...

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: "C omplier 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?

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! 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...

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 so...