Obsessed with Oracle PL/SQL

PL/SQL offers a number of options when it comes to writing conditional logic, as in variations on "If this then that". This post reviews them, and provides examples. You can choose from the following: IF statement - IF, IF-THEN, IF-ELSE, IF-ELSIF-ELSE CASE statement - simple and searched CASE CASE expression - the expression "version" of the statement (and one of my favorite elements of PL/SQL) IF Statement It's hard to imagine a programming language without a version of IF, and PL/SQL is no exception.  The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement can take one of three forms, but they all have this in common: they start with "IF" and then end with "END IF;". IF THEN The simplest IF statement: if the boolean expression evaluates to TRUE then execute the statements between THEN and END IF. Example: BEGIN IF l_hire_date < SYSDATE THEN

This question just came in via Twitter DM, so I thought it could do with a blog answer. Ref cursor types are the datatypes from which we declare cursor variables. A cursor variable is, well, just that: a variable pointing back to a cursor/result set. Some really nice aspects of cursor variables: you can associate a query with a cursor variable at runtime (useful with both static and dynamic SQL);  you can pass the cursor variable as a parameter or function RETURN value. Specifically: you can pass a cursor variable back to a host language like Java for consumption. Check out the cursor variable documentation .  Explore this LiveSQL script on cursors, including multiple examples of cursor variables. OK, to get to it, then: Before you can declare a cursor variable, you need to have a ref cursor type defined. There are two, ahem, types of types: strong and weak.  With a strong type, you include a RETURN clause that specifies the number and datatypes of expressi

We upgraded the Oracle Dev Gym site this past weekend to 2.2. Why should you care? Because the Dev Gym offers a great active learning complement to reading doc and blogs, and watching videos. We help you deepen and broaden your expertise through exercise: taking quizzes that reinforce newly gained knowledge. Quizzes At the heart of the Dev Gym is a library of over 2,400 quizzes on SQL, PL/SQL, Oracle Application Express, Database Design and Logic. Almost all these quizzes were first played as part of our daily and then weekly tournaments - which means they've been reviewed by experts and taken by hundreds and in some cases thousands of developers. They are usually code-based quizzes that strengthen you ability to read and understand code (rather than "try it and see" by running the code). The Dev Gym home page offers a selection of favorite quizzes hand-picked each week by our quizmasters from this library. But that's just the tip of the iceberg. Looking to st

Thanks, Tim Hall , for launching the annual OTN Appreciation Day , now renamed to ODC Appreciation Day , since the Oracle Technology Network has been recently re-shaped into Oracle Developer Community ! Many "outside" Oracle technologists (not employed by Oracle) are publishing posts today about their favorite Oracle technologies. I have seen posts about index-organized tables, PL/SQL, SQL, pipelined table functions, SQL Developer, PL/SQL collections in SQL, and much, much more. I could write a similar blog about my all-time favorite technology, PL/SQL, the best database programming language the world has ever seen. But you all know that about me, and hopefully about PL/SQL, too. And it seems a little, I don't know, self-serving for an Oracle employee to toot a horn about Oracle technology (OK, not self-serving: it is, after all, my job ). But since OTN has been renamed into Oracle Developer Community, I will take advantage of Tim's initiative to c