Obsessed with Oracle PL/SQL

YesSQL Celebration OOW15 October 26 - 4:00 - 5:30 PM Park Central Hotel - Metropolitan III Room Last year, we held our first-ever YesSQL Celebration at OOW14. It was precisely that: a celebration. It started with fascinating stories from Andy Mendelsohn, EVP of Server Technologies (aka, Oracle Database and more), about early days at Oracle, then moved on to presentations from various development teams ("Meet the folks who write this amazing software!"). Highlights include Andrew Witkowski, SQL Architect, in a NoSQL pirate disguise, Mike Hichwa talking about the origins of Application Express, and Mohamed Zait and the optimizer team in a highly amusing and self-produced video. This year, we will celebrate SQL (and PL/SQL, and related appdev technologies in Oracle Database) at OOW once again, but the event will be slightly different. The agenda this year is simpler, different and very exciting, because in addition to Andy Mendelsohn as our featured speaker (mo...

The Oracle Database Developer Choice Awards celebrate and recognize technical expertise and contributions in the Oracle Database community. As longtime and new users of Oracle Database move to the Cloud and take advantage of this exciting new architecture, community experts will play a critical role in helping them succeed. Panels of (mostly) Oracle ACE judges have now selected their finalists, and the time for public voting has begun! From 15 September to 15 October , we call on all members of the Oracle Database developer community to check out the finalists for each of these categories and vote for those you think deserve worldwide recognition for their work. You can vote for as many finalists as you like, but you can register only one vote per finalist (even those of us living in Chicago). And if you would like to help us generate buzz for the awards program via Twitter and other social media platforms, please use the #odevchoice hashtag. Congratulations to all our fin...

Finally, finally, FINALLY! I have overcome my resistance to learning Camtasia (or enough of it to not make an utter hash of things). To celebrate my newfound skills, I am releasing today a linked set of videos relating to the NOCOPY parameter hint. Enjoy! Passing by Reference and Value   In this video, Steven explains the difference between passing a parameter by reference (which happens by default with IN parameters) and passing by value (which happens by default with OUT and IN OUT parameters). Using NOCOPY to Improve Performance  The NOCOPY parameter hint tells the PL/SQL compiler to pass OUT and IN-OUT parameters "by reference" (meaning: do not copy in, nor copy out). The result is better performance and reduced PGA consumption.  Side-effect of NOCOPY In this video, Steven demonstrates how use of NOCOPY coupled with an unexpected termination of a subprogram with an unhanded exception can leave your IN OUT actuals partially modified -...

I received this question last week: Hi Steven, Here is a question that I have been pondering for sometime and haven't found a definitive answer to.  Perhaps you can shed some light. When writing a procedure in PL/SQL, I pass it all the variables that it needs to do it's job.  Of course, it has access to any variables that are defined at the global level - but I rarely if ever use these (perhaps a few constants here and there).   However, if I have a procedure that has a couple nested sub-procedures - should I follow the same pattern as, above?  It seems a bit silly to pass parameters to a proc and then re-pass those same parameters to a nested sub-proc when that nested sub-proc already has the variables in scope.   So should the rule of thumb be:   For procedures, pass all parameters in or use local variables as needed.  Use global variables only in extraordinary circumstances.   or   For nested sub-procedures, don't pass varia...