Posts

Showing posts from July, 2015

Smoothing rough edges for productive programming

Image
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 DBMS_OUTPUT buff…

Thoughts on PL/SQL, code sharing, and code generation

[June 2016 update: we have now launched a community code generation project: oddgen - for Oracle Data Dictionary Generator. More information available here. Join the project!]

[While I am sure you will be BLOWN AWAY by what I write below, just in case you are already convinced of the need for a flexible code generator for PL/SQL, skip to "Needed: Flexible Code Generator for PL/SQL"]

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 sharingMost 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 databasePL/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…

PL/SQL Brain Teaser: how many ways can you define a cursor in PL/SQL?

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

Getting started: Sharing your Oracle Database Developer experience

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 doesn't feel like a wast…

Nuances of NOCACHE, a.k.a., Steven eating (partially) his words

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 @kibehatweeted:
Reason why …

Inline your subprograms - but only within same program unit

Image
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 way we write our code and in th…

Will Oracle ruin the PL/SQL Challenge?

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. Since rejoining Oracle last year (after being away for 22 years), it has become more and more clear to methat 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 did you join Oracle?” they asked, shaking their heads. 

Kscope15: Reflections on another great ODTUG conference

Image
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 (not meant to be a comprehensive revie…