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

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

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! Waaaay back in April 2010, I decided (without really consciously deciding) that I wasn't busy enough. What I needed was the responsibility of support a 24x7 website that offered quizzes on SQL, PL/SQL and more. Well, actually, I decided that a while before April 2010, but April 2010 was when we launched the PL/SQL Challenge . Over 900,000 answers to thousands of quizzes later, I thank my lucky stars that my original co-founder of the site, Finn Ellebaek Nielsen, suggested that perhaps my daily PL/SQL quiz should take a break on Saturday and Sunday (I was pushing for 7 quizzes a week, because I can be well, quite, insane). In any case, we have had great fun building the sit...

Recently ran across this procedure, defined in the Post Authentication field in an Appliation Expression application. I encourage you to look it over and come up with your list of things you'd want to change about it. I leave a whole lot of white space below the code block so you can view the end before seeing what I have to say about it. 1: procedure post_auth_7048783549465082709 is 2: begin 3: APEX_UTIL.RESET_AUTHORIZATIONS; 4: 5: for c1 in ( 6: select count(*) cnt 7: from app_users 8: where username = :APP_USER 9: and role_name = 'ADMINISTRATOR' ) 10: loop 11: if c1.cnt > 0 then 12: :IS_ADMIN := 'TRUE'; 13: else 14: :IS_ADMIN := 'FALSE'; 15: end if; 16: end loop; 17: 18: for c1 in ( 19: select count(*) cnt 20: from app_users 21: where username = :APP_USER 22: and role_name = 'CONTRIBUTOR' ) 23: loop 24: if c1.cnt > 0 then 25: :IS...