Skip to main content

Tips on Writing PL/SQL in APEX Apps

On December 3 for our AskTOM Office Hours session, three deeply experienced and highly respected APEX prosshared their wisdom on how best to write and manage PL/SQL code for Oracle Application Express projects.

You can watch the recording and follow links to related resources here.

Our experts were:

Karen Cannell

Karen promotes APEX topics and best practices through local, regional and national user group presentations and papers. A devoted user group volunteer since 2007, she is especially active in ODTUG, where she serves as the editor of the ODTUG Technical Journal. She is former Associate Editor of IOUG SELECT Magazine. She is co-author of Agile Oracle Application Express, Expert Oracle Application Express and Beginning Oracle Application Express 4. Her most recent presentation at Kscope15 focused on APEX Interactive Reports with a Deep Dive: APEX 5 New Features and Upgrade Cheat Sheet.

Karen has delivered Application Express solutions since its Web DB and HTMLDB beginnings, and continues to leverage the Oracle suite of tools to build quality web applications for clients in government, medical, and engineering industries.  She is known for thinking outside the box to implement maintainable APEX solutions – most are still in production today (upgraded, of course!). . A mechanical engineer by degree (one of them), she has analyzed, designed, developed, converted, upgraded, enhanced, and otherwise improved legacy and commercial database applications for over 25 years, concentrating on Oracle technologies since 1994 and APEX since it was born. Karen is President of TH Technology, a small consulting firm providing Oracle technology services, lately focused on APEX, where she continues to deliver quality solutions for her clients.

Scott Spendolini

Scott is Vice President of the APEX+ Practice of Viscosity, NA, where he manages a team of highly-skilled Oracle APEX developers. He is also responsible for developing and delivering Oracle APEX-related curriculum, as well as managing the development of several APEX-based products.

 Throughout his professional career, he has assisted various clients with their Oracle APEX development and training needs. Spendolini is a long-time and regular presenter at many Oracle-related conferences, including Oracle OpenWorld, Kscope, and RMOUG. He is a recipient of the Oracle Ace Director designation, author of Expert Oracle Application Express Security and co-author of Pro Oracle Application Express. Spendolini is also an Oracle Certified Oracle Application Express developer.

Prior to reigniting and running Sumner Technologies from 2015 to 2018, Spendolini was an APEX Practice director at Accenture and Enkitec from June 2012 through April 2015. Before joining Enkitec as part of an acquisition, he co-founded and ran Sumneva and Sumner Technologies from 2005 through 2012, which focused on Oracle APEX services, education & solutions. Spendolini started his professional career at Oracle Corporation, where he worked with Oracle eBusiness Suite for almost seven years and was a Senior Product Manager for Oracle APEX for just over three years.

Scott Wesley


Scott clearly has a passion for Oracle APEX. In the time since he's left Forms behind, he's recorded a video series; written a book; trained developers around Australia; answered forum questions from around the world; and written a variety of posts at http://grassroots-oracle.com.

Scott has been consulting with clients around Perth since 2000, enjoying his time with the Sage Computing crew for just over a decade. He presents regularly at Australian events, and was recognised as an Oracle ACE in 2014. and often weaves a side interest in science & astronomy into his work.


Comments

Popular posts from this blog

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...