Obsessed with Oracle PL/SQL

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

A month ago, I received this email: Hi Steven, just to inform you that your "PL/SQL Obsession" page is out of work, http://www.toadworld.com/sf answers "Page Not Found"  Maybe Dell has blocked you? So I thought I would post a note on my blog to clarify matters. For many years, Quest and then Dell "hosted" my online PL/SQL resources at  http://www.toadworld.com/sf : I had lots of fun with that page, and am especially grateful to Steve Hilker for helping me keep it current and useful for thousands of PL/SQL developers. We came up with what I still feel is the best way to describe my relationship to PL/SQL: PL/SQL Obsession And for more than a year after I joined Oracle (in March 2014), Dell and I agreed that we should keep the PL/SQL Obsession site intact, with all of my content, even that which they did not own. But all things - good or otherwise - must come to an end. It's confusing to have me working at Oracle but still seemin...

Yes, yes, I know: another post with little or nothing to do with PL/SQL. My apologies. But remember, I do offer a daily tip on Twitter:  @sfonplsql .  videos on Practically Perfect PL/SQL and PL/SQL Channel resources at the Oracle PL/SQL page [well not me but....] Bryn Llewellyn's PL/SQL and EBR Blog weekly quizzes on the PL/SQL Challenge .  So please don't say "But what have you done for me lately?" :-) 'Cause then I would feel bad that this post is about me asking you  for something. I am involved here at Oracle with both content generation (see above) and working on community-oriented apps that make it easier for you to access expertise and resources on Oracle Database developer-related topics, including: Ask Tom LiveSQL PL/SQL Challenge Oracle Learning Library OTN SQL-PL/SQL forum As many of you are probably aware, content is great stuff ("Content is King"), but and especially for technology-related content, it can get "sta...

Oracle Corporation is a massive enterprise - over 130,000 employees, big shiny headquarters, hundreds of products (software and  hardware), etc. - and Oracle Open World reflects that massiveness. I suppose there may be some people who can ingest all of that and make sense of it (besides our top executives, for whom that is in part their job), but I tend to focus lots more narrowly in the Oracle Database world and the developer community that thrives around that flagship product. My Personal Highlight: Announcing Winners of the 2015 Devvys So first and foremost the highlight of OOW15 was our YesSQL event, in which we announced the winners of the 2015 Devvys - the Oracle Database Developer Choice Awards . Andy Mendelsohn was kind enough to make room in his busy schedule to stop by, share some stories (this year, he focused on Big Data SQL, and how that evolved so smoothly out of existing technologies) and let everyone know how important application developers are to the continuin...

Received this request today via email: I was at the MOUG Fall Conference in Chicago a few weeks ago and enjoyed your presentation on the result cache. It’s already paying dividends for us. Thanks for coming and sharing.   I have a question for you, and maybe you’ve already written about this and can point me toward an article or blog post. We will be revising our coding standards, which are rather loose and largely ignored, and I want to try to promote those that will give us the most benefit. What is your top ten list of the most important coding standards to implement?   Thanks for your time, and I hope to see you at OOW. It will be my first trip there. And I replied: I love these kinds of requests, because it gives me an opportunity to take a fresh look and publish something on my blog.  :-)  I don’t think I will be able to get back to you until after OOW, hope that works OK. Please do come up and say hi if you see me! And then I thought: wait a minute, le...

Received this question today: We are planning to develop    a product with APEX and is it possible to    execute free sql inside an apex application? I mean is it possible to have a SQL execution window inside the APEX application like we execute inside an Oracle SQL developer? Sure, why not?  Well, actually, there are all sorts of reasons "why not" , right? But, yes, it is certainly  technically possible to do this - and not very difficult.  Create a page in Application Express. Add a Text Area item and give your users lots of room to write lots of SQL.  Add an Execute button. Create a process that fires on that button, and contains code like this:       BEGIN          EXECUTE IMMEDIATE :P1000_your_sql;       END; Then your users will then be able to do all sorts of things: Create a new table (!) Truncate an existing table (!!) Set values of columns to NULL (!!!) et...