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.


Labels:

Comments

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

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, ...
4 comments
Read more

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...
5 comments
Read more