Obsessed with Oracle PL/SQL

Got this email Monday from a fellow new to PL/SQL and Oracle: I have been in I.T. for a couple of years but always had this feeling that I could do better. I lost that hunger when the company I had worked for 10 years plucked me from the warehouse as a kind of internal promotion program. I guess you could say I slowly settled into a rut.    I discovered your site recently and I am in the process of reviewing your tutorial videos (I have also started to read newer technical books). I am sure you have been told before but your tutorial videos are great!    My problem is that I think the lessons would "stick" with me more if I had the chance to work with them and "play" around.   I have very low self esteem (which I am also working on) so please don't laugh but is there a place that I can download a condensed or tutorial version of Oracle 11g? First, I am always excited to hear from people who are just getting into Oracle Database, SQL and PL/SQL. Our part...

Last week, I posted this on Twitter : What code can be removed w/o changing text shown after execution? Who Said What? evrocs_nl putting data in collection before the select, because the bulk collect will clear the collection first (unless really old oracle) Yes! A BULK COLLECT always empties the target collection. If the query returns no data, the collection remains empty. Otherwise its contents are replaced by the result set of the query. MDWidlake   All of it as you forgot to turn serveroutput on anyway. Where do I collect my Kewpie doll? Oh, Martin, you clever fellow. There's always one in a crowd. But sort of good point. This exercise was taken from the PL/SQL Challenge , whose PL/SQL quiz assumptions include that SERVEROUTPUT is always on. ddfdba   indx pls_integer:=100; l_empty objects_t; := l_empty Yes! There is no need to declare a variable for the iterator used in a FOR loop; it is declared implicitly by PL/SQL. T...

Oracle Database Insider newsletter asked me to do another round of resolutions for the coming year. Since I love it when other people act as though they are interested in what I have to say, I told my manager I would have to clear my calendar for a couple of days and get this done. He agreed - so long as I did it during my week off between Christmas and New Years. Oh, OK. Before getting to the resolutions, I encourage you to subscribe to this very interesting and useful newsletter. Here goes.... 1. Finish what I and we started.  I have a long-standing and bad habit of getting all excited about new stuff: new programs, new applications, new websites, new programs…go, go go! But I am not so good at making sure that all the existing and really great stuff is “finished” (as useful and usable as it could be). So for 2016, I resolve to consolidate what is already in place and make sure our users can get the most out of it. I am thinking about websites like Ask...

We all know that hard-coding is a bad thing (well, maybe not all of us . At one training several years past, I asked the audience "Does anyone think hard-coding is a good idea?" and one person raised his hand. Um, OK). You know hard-coding: when you say to yourself "This is never going to change." and so you put the "value" directly in your code, over and over again. I put "value" in quotes, because many developers think simply of hard-coded literal values when they think of hard-coding. But I think there are many more ways that hard-coding can seep into our programs. So I invite you to help find all the hard-codings in the procedure below. Here's the rule: you can only identify ONE HARD-CODING in each comment. I will delete a submission with > 1. It'll be more fun that way. Promise! I will give everyone a couple of days to submit your ideas, then offer my own views on the subject.

Use LOG ERRORS to suppress row-level errors from within the SQL engine when executing non-query DML statements (like inserts, updates and deletes). Instead of raising an error, the SQL engine will insert a row into your error log table - which you then must, really must check after statement execution to see if there were any problems. Or you will be hiding under the pillows, and your users will really not appreciate that. In contrast, if you use SAVE EXCEPTIONS with FORALL, you will suppress statement-level errors, but all changes made to rows identified by that statement are rolled back. Suppose I execute the following statements: CREATE TABLE plch_employees ( employee_id INTEGER PRIMARY KEY, last_name VARCHAR2 (100), salary NUMBER (3) ) / BEGIN INSERT INTO plch_employees VALUES (100, 'Sumac', 100); INSERT INTO plch_employees VALUES (200, 'Birch', 50); INSERT INTO plch_employees VALUES (300, 'Alde...

This post is courtesy of the PL/SQL Challenge quiz ending 27 November 2015 : If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query. Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction. Here's the code for the quiz - see how you do! And of course sign up to take each weekly quiz as it is released (you can even compete for international rankings). I execute the following statements (which you can easily run yourself on LiveSQL ): CREATE TABLE plch_flowers ( id INTEGER PRIMARY KEY, nm VARCHAR2 (100) UNIQUE ) /...

What? You didn't know that? :-) On a recent blog post, I received this comment: Thanks for the video. You've answered my question and given us lots to talk over. Now, do you have any advice on getting those resistant to change, crusty, old, developers to buy in to change? My immediate response was: incentives and fun. So now it is time to elaborate a bit. First of all, the hardest part of programming is not learning new features or absorbing the syntax of a programming language. After all, learning such a language is waaaaay easier than learning a human language - primarily because when we write code we are communicating with something that "thinks" quickly but is not particularly "smart." It, the computer however you want to define that these days, does what we tell it to do. Really, it does - no matter how many sci-fi movies you've watched that indicate otherwise. Since a computer isn't very smart, we have to communicate with it using...