Obsessed with Oracle PL/SQL

No language is perfect and while PL/SQL is an incredibly "tight fit" for building applications on top of Oracle SQL (and, of course, Oracle Database), it also is not yet quite perfect . Maybe in Oracle Database 13(?) - (?) being whichever letter Larry decides will best reflect the main theme of that version.... In any case, in the meantime, and to paraphrase a saying: You write code with the language you've got, not the language you want. So the key thing is to maximize your productivity any way you can, all along the way. And sometimes the steps you can and should take in this area can be very small, but they can still add up. Case in point: displaying a Boolean value with DBMS_OUTPUT.PUT_LINE. As anyone who's spent time with PL/SQL knows, DBMS_OUTPUT.PUT_LINE is the procedure provided by Oracle to display text from within a PL/SQL block on your screen. It accepts a string (and anything that can be implicitly converted to a string) and sends it to the ...

News You Can Use By all means check out this entire post, but if you are here for code generation, check out oddgen.org .  And Now For the Regularly Scheduled Post If you have spent much time in the PL/SQL world, you will have noticed some unusual aspects: Almost no third party libraries or utilities (prominent exceptions: utPLSQL , Logger , Alexandria Library ) Minimal code sharing Most applications built from scratch The cause(s) of this odd reality (odd in comparison to, say, the way JavaScript apps are built)? Likely several, including: Management concerns about putting "someone else's code" in their database PL/SQL community well established before the days of open source, so no tradition of sharing, of free code, etc. How much code can actually be shared for PL/SQL-based applications? That last point/question is what I'd like to explore a bit in this post. It's easy to shake our heads at the "sad state of affairs" when it...

According to the PL/SQL User Guide , A  cursor  is a pointer to a private SQL area that stores information about processing a specific  SELECT  or DML statement. So how many ways can you define a cursor in PL/SQL? Let's include in this ways in which you can get Oracle to define a cursor for you, as well. Please submit a comment with JUST ONE example of a cursor definition. You can submit multiple ideas, but with multiple comments.  If you include several in a single comment, I will take only the first one that has not already been suggested

Last week, I was emailing back and forth with Paul, who is a senior developer/architect at a major financial firm. He is widely respected within the company, a valued resource for many development teams. He has also garnered the respect of the PL/SQL development team. Yet Paul is not well known outside of his company. He does not use Twitter, does not have a blog. I encouraged him to share his knowledge publicly and he asked me: "How should I get started?" My first instinct was to say: Register on the  OTN Community ! Start a blog! Start a Twitter account! Set up a Facebook page! Join LinkedIn! etc. etc. But then I reflected back on the effort it's taken me, "Oracle celebrity" me, to build up my Twitter following, get people to read the posts on my blog, and I decided to hold off on the instinctive response and instead ask myself: How can someone who is technically strong but without name recognition contribute to the community in a way that ...

I posted this tweet yesterday: #314b #plsql Avoid gaps in sequence NEXTVALs: use NOCACHE option when creating or modifying sequence. #FrugalProgramming It was a great success social media-wise. Several Oracle technologists I admire greatly responded to it, and none favorably. But my twitter stats will improve, so hurray! :-) Seriously, though, it was a problematic tweet, partly because it was a piece of advice that only makes sense in a fairly narrow context, or as  @brynlite pointed out: I expect to repeat this often, Steven: don't advocate specific solutions until the requirements have been stated To switch I rather snarkily replied: Keep on saying it, I guess, Bryn, but I am not sure how the advice can apply in the world of Twitter. OK, fine, it wasn't my finest moment. As I was stretching this morning I realized: I should have written a post on my blog explaining the tweet, and then I could refer to that. Or as PL/SQL Challenge SQL Quizmaster @kibeha   ...

Got an email from an expert Oracle Database developer (let's call him Sheldon, in honor of my dad, who taught himself programming - RPG, no less!) yesterday: Is inlining (via PRAGMA INLINE) even something worth talking about?  I’m getting the feeling that it’s not. I’m trying to come up with an example to show folks the beauty of inlining function calls.  And I’m getting the idea that either oracle is doing something like inlining code all the time or is just really super-efficient on the calls these days. Well, sure, Oracle Database is really super-efficient. Always. Automagically. Don't have to worry about doing any tuning  yourself. Oh, wow, what a wonderful world! Pinch. Wake up! That was a dream you were having, Steven. Oracle Database does an awful  lot to ensure high performance for our applications, but it's not yet fully automatic or automagic. We can do things to drastically screw up performance. And we can also take actions, both in the wa...

I received this email from a person who has played the PL/SQL Challenge quizzes for years and now wanted to delete his account and player history. Here's part of the reason he gave: "I get the impression that the PL/SQL Challenge website is getting closer and closer to Oracle corporation (SSO with  oracle.com , Oracle advertisements on the start page, …) – I always valued the PL/SQL Challenge as an independent resource, and these changes are IMHO going in the wrong direction." This took me by surprise, but perhaps it should not have. S ince rejoining Oracle last year (after being away for 22 years), i t has become more and more clear to me   that  a noticeable percentage of my fellow Oracle Database developers and DBAs love the technology but don't love the company.  I suppose that explains why soon after I went back to Oracle, several people came up to me with a sad look on their face, as if they’d lost a good friend: “Why...

I recently spent a week in hot, muggy Florida, visiting my mom both before and after Kscope15 . Thanks, ODTUG, for organizing the event near my mom's home! Oh, and a  BIG  thanks for scheduling Kscope16 in Chicago. One less trip on an airplane! Hurray! Kscope15 followed a predictable (and very pleasant) pattern: an extremely well-organized event (kudos to Executive Director Crystal Walton, Conference Manager Lauren Prezby and the rest of the the amazing  Your Conference Connection  staff, as well the  ODTUG  conference organizing committee and board).... gathering together many of the hottest and most engaged Oracle Database development talent.... in a really nice location (resort hotel right on the beach). Many attendees brought their families, so I had lots of kids to play with, including and most rewardingly, little two year old Kingston, who now calls me "Uncle Steven." I love extending my virtual family. :-) Some highlights from Kscope15 (n...