Obsessed with Oracle PL/SQL

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 b...

So I signed up for my Always Free Autonomous Database (AFAD) and a boatload of other cloud services. OK, what shall I do with them? Hmmm....well....as some of you may know, I've gotten very concerned about climate change and human-caused extinctions. I started a project with Vincent Morneau called fabe - for all a beautiful earth - to help all of us reduce consumption, rescue species and reconnect to nature. Check it out at https://fab.earth . Yes, it is an APEX application and it is already running on an Oracle Database cloud service, so....what else? You know the saying "think globally, act locally"? Well, if fabe is global, then my work on invasive species is local, very  local. When I lived in Chicago, I went out to the nearest "wild" spaces I could find and cut back buckthorn trees that didn't belong in Chicago, out-competed native trees for sunlight, and killed off those native species. I rescued trees  - and the literally millions of living...

I ran into a problem yesterday on the  Oracle Dev Gym  (offering quizzes, workouts and classes on Oracle technologies). A number of rows of data were incorrectly deleted. I was able to use  Flashback Query  to restore them (thank you, thank you, Flashback Query!). Crisis averted.  But how did this come about? I recruited Chris Saxon to help me figure out how this could have happened. In relatively short order, we narrowed down the culprit to a process in the Dev Gym Application Express definition that was unconditionally "removing previews" - but was in fact removing all rows, "previews" or not. Ugh. So we fixed that. But it got me wondering and worrying: what other processes in my app are unconditional? And should they be? While some processes fire unconditionally on a page (for example, to get the data from tables and display them on the screen), many are (or should be!) restricted to a button press, the result of a conditional expression, or an ...

On the PL/SQL Challenge , all times are shown in the UTC timezone. Weekly quizzes end on Friday, midnight UTC. So I recently decided that when I display the time that the quiz starts and ends, I should add the string "UTC". Our quiz website is built in Oracle Application Express 5.0 , so I opened up the process that gets the date and found this: DECLARE l_play_date DATE := qdb_quiz_mgr.date_for_question_usage (:p46_question_id); BEGIN :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI'); "OK, then," says Steven the Fantastic Developer to himself. "I know exactly what to do." And I did it: DECLARE l_play_date DATE := qdb_quiz_mgr.date_for_question_usage (:p46_question_id); BEGIN :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI') || ' UTC'; Ah, PL/SQL and APEX - so easy to use! :-) Now, there are lots of things you could say about the ...