In macOS Catalina (10.15) there’s a new (maybe this was released in an earlier version) version of the built in Screenshot app has some really cool features that addresses most of my screenshot needs. Before continuing you should read the following Apple support articles about the built in Screenshot tool:
One thing that is a bit of a hidden gem is the ability to quickly markup screen shots both using the Markup tool. To activate the markup tool click the thumbnail icon of the screenshot (right after it’s taken). The following video highlights this:
The Markup tool has a very cool feature called Continuity Markup that allows you to markup the screenshot either on your iPad or iPhone. The following video shows this. Note: once I clicked on the thumbnail of my screenshot, in the bottom right corner on my screen, it automatically turned on my iPad for me!
You can also use Continuity Markup when using Quick Look to preview a file (ex: hitting spacebar on a file in Finder).
Aside from the obvious, it’s been disappointing for a lot of people in our community seeing all the canceled conferences. Conferences are a great time to learn and meet new people. I was recently talking about this to Adrian Png and Trent Schafer and we decided to create a one-day mini conference called [email protected]. Since our initial discussion others have gotten involved (Joel Kallman, Shakeeb Rahman, Peter Raganitsch, and Niels de Bruijn) and it’s blown up into a full one day 24-hour conference - for FREE!
[email protected] will be a bit different from normal webinars in the sense that everyone will be presenting from the comfort and safety of their homes (thus the name, [email protected]). We can expect some interruptions from kids, pets, etc. and we’ll make the most of it.
Everything you need to know:
When: Thursday April 16th starting at 00:01am EDT going until 23:59pm EDT
Starting with APEX 19.1 a new form type was introduced that replaces the old “Auto DML” model with much more robust functionality. Carsten Czarski wrote an in depth article about the new form regions and I suggest you read it before continuing with this post.
One really nice feature with the new form regions is the ability to have a PL/SQL process instead of automatic DML to save changes. This allows for all the great functionality to load data and manage lost updates, while having the customization of PL/SQL to process your data. An example of a Form Region with PL/SQL Code:
Note the PL/SQL code sample was taken from the help section for demo purposes.
Processes associated to a Form Region will only run if a value directly associated to that Form Region has changed. For example in the screen shot below, P4_SAL is associated with the Emp Form Region. If this value is changed than the process, Process form Emp (from the previous screen shot), will be executed.
If a field is not associated with the Form Region and it’s changed then the process associated to that Form Region will not run. For example, suppose a new page item is added: P4_SAL_INCREASE. It is not associated to any Form Region:
The Form Region PL/SQL Processing code is then changed to:
1 2 3 4 5 6 7 8
... -- Updating when 'U' then update emp set sal = :SAL + nvl(:P4_SAL_INCREASE, 0), ... whererowid = :ROWID; ...
If a user only updates P4_SAL_INCREASE then the page process Process form Emp (i.e. the process associated to the Form Region) will not run since none of the associated values have changed.
They’re several work arounds to this, each have their pros and cons:
Create a computation or PL/SQL process (that runs before the current process) to set :P4_SAL := :P4_SAL + nvl(:P4_SAL_INCREASE, 0). This will change the associate P4_SAL if P4_SAL_INCREASE has a value and therefore trigger the Form Region process to run.
Change the process type to PL/SQL Code (no associated Form Region). This will make things very straight forward but the built-in row locking and lost update prevention functionalities will be lost.
If you’ve created a modal page in APEX you’ve inevitably run into the issue of needing to refresh a modal page after changing it. You can’t use the normal browser’s refresh button or use the keyboard shortcut ctrl/cmd + r as it refreshes the underlying page, not just the modal page. Instead you either need to close and reopen the modal page or right click and select the reload frame option as shown below:
Instead of struggling with the reload situation I usually create a “Modal Reload” button that will show up in all modal pages and only work in development environments. Here’s how to add one:
On Page 0, add a region called DEV_ONLY Modal. The region will be automatically included on modal pages.
Appearance > Template: Blank with Attributes
Configuration > Build Option: DEV_ONLY
This assumes you have a Build Option called DEV_ONLY. If not it’s fairly easy to create.
SQLcl supports aliases which can save a lot of time when running the same queries or scripts by not having to type them each time. A simple example of an aliases is to show the current date. Instead of having to type select sysdate from dual; each time you could do this:
1 2 3 4 5 6 7 8 9 10 11 12
-- Only do this once alias today= selectsysdate from dual ;
-- To run it just type: today
SQL> today SYSDATE ____________ 11-MAR-20
If you are new to SQLcl aliases or the concept of login.sql (which runs each time SQLcl is launched) I suggest reading the following articles before continuing with this article.
If you use multiple machines or want to share aliases you may think you’re limited to sending code snippets to people or blogging about them. Luckily SQLcl can reference files stored on the web! I’ve posted some of my SQLcl aliases on Github here. To load them just run @https://raw.githubusercontent.com/martindsouza/oracle-sqlcl/master/aliases.sql in SQLcl. If you want to constantly get an updated version of these aliases just add the line above to your login.sql file. I’m currently working on fine tuning my SQLcl aliases and will keep Github file up to date.
One example of a alias I have is called invalid which lists all invalid objects in your schema. Example:
1 2 3 4 5 6 7 8 9 10
SQL> invalid OBJECT_NAME OBJECT_TYPE ___________________________ _______________ BIU_OOW_DEMO_EVENT_LOG TRIGGER BI_OOW_DEMO_HIST_GEN_LOG TRIGGER EBA_DEMO_IG_TEXT_PKG PACKAGE BODY OOW_DEMO_GEN_DATA_PKG PACKAGE BODY OOW_DEMO_PREFERENCES_BIU TRIGGER PKG_ORDS PACKAGE BODY PKG_ORDS PACKAGE
Some closing thoughts:
If referencing other people’s aliases (via the web / .sql file) be careful to either review the file or ensure it comes from a trusted source as malicious code could be easily added.
Instead of having a lot of content in your login.sql file you can keep it to one line (referencing a file on Github for example) and keep the Github file updated with everything. This way if you change machines or use multiple machines they’ll all have the same experience.
-- Pipelined Function (with error handling) createorreplacefunction f_pipeline_demo return emp_info_arr pipelined as l_scope logger_logs.scope%type := lower($$plsql_unit); begin for x in ( select ename, job from emp ) loop piperow (emp_info(x.ename, x.job)); endloop;
exception when others then logger.log_error('Unhandled Exception', l_scope); end; /
-- Query First 5 rows of Pipelined Function (they're 14 ros in emp table) select * fromtable(f_pipeline_demo) whererownum <= 5 ;
ENAME JOB ________ ____________ KING PRESIDENT BLAKE MANAGER CLARK MANAGER JONES MANAGER SCOTT ANALYST
The above demo serves two purposes. First to provide a basic example of a pipelined function and second to show a “hidden error” in its implementation. No errors were raised when querying the pipelined function, however looking at the logger output shows that an error was raised:
1 2 3 4 5 6 7 8 9
select logger_level, text from logger_logs_5_min where1=1 andscopelike'f_pipeline_demo' ;
LOGGER_LEVEL TEXT ____________ ____ 2 Unhandled Exception ORA-06548: no more rows needed ...
Taking a step back, the emp table has 14 rows in it. Querying f_pipeline_demo was limited to the first 5 rows by adding where rownum <= 5. When a pipelined function is called and it realizes that it no longer needs to process additional rows it stops by raising the no_data_needed.
At first glance this may seem like a weird/bad outcome but it makes sense. Pipelined functions sometimes contain some costly computations in each loop. If only 5 rows are returned, doing the additional computations isn’t required.
Since I log all exceptions in my code, I don’t want to log no_data_needed exceptions as it’s not a “bad” exception. To get around this I can simply change the exception block in the pipelined function to:
1 2 3 4 5 6 7 8 9 10
... exception -- Make sure this is before the "when others then" block when no_data_needed then null; when others then logger.log_error('Unhandled Exception', l_scope); end; /
Now when the pipelined function is queried with a restricted set of rows the no_data_needed exception won’t be logged.
apex_string.split is a great utility that allows you to take a delimited list and make it queryable. Here’s a small example:
1 2 3 4 5 6 7 8 9
selectcolumn_value from apex_string.split('John,Sally,Bob', ',') ;
COLUMN_VALUE _______________ John Sally Bob
You can read more about using apex_string.split and how to parse CSVs here.
When I use the split function to filter a table I used to do an explicit join as shown below. There’s nothing wrong with this concept but it can be a bit clunky to read and write.
1 2 3 4 5 6
select e.* from dual join emp e on1=1 join (selectcolumn_value job from apex_string.split('CLERK,MANAGER', ',')) x on1=1 and x.job = e.job ;
Recently Stefan Dobretweeted about how you can use value member of apex_string.split(...) to accomplish the same thing. Here’s an example:
1 2 3 4 5
select * from emp e where1=1 and e.job memberof apex_string.split('CLERK,MANAGER', ',') ;
Using member of is a nice clean solution however since apex_string.split is a PL/SQL function there’s the potential of a lot of context switching (between SQL and PL/SQL) that can slow your code down. To verify this I made a simple wrapper function which logs all the calls to the function:
1 2 3 4 5 6 7 8 9 10 11 12 13
createorreplacefunction wrapper_split( p_str invarchar2, p_sep invarchar2) return wwv_flow_t_varchar2 as begin logger.log('START', 'wrapper_split'); return apex_string.split( p_str => p_str, p_sep => p_sep ); end wrapper_split; /
Using the previous example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
select * from emp e where1=1 and e.job memberof wrapper_split('CLERK,MANAGER', ',') ;
They’re 14 rows in the emp table and apex_string.split was called 14 times. When using this on larger tables it could have significant performance impacts. Thankfully there’s a simple solution: scalar subqueries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
select * from emp e where1=1 and e.job memberof (select wrapper_split('CLERK,MANAGER', ',') from dual) ;
-- Check Logger: selectcount(1) cnt from logger_logs_5_min wherescope = 'wrapper_split' -- Note extra predicate was added to only show logged records after the previous example ;
CNT ______ 1
This subtle difference limits it to one PL/SQL call regardless of the size of the table it’s checking. If using the member of syntax don’t forget to also reference apex_string.split in a scalar subquery.
APEX has a set of built-in substitution strings which allow developers to reference things such as the current application ID, page number, date format, etc. For more information about how to reference substitution strings read Variables in APEX.
Finding the list of built-in substitution strings can be a bit difficult for new users to APEX (thus this article). To get the complete list of built-in substitution strings:
View the Oracle Application Express App Builder User's Guide
Search Table of Contents for Using Built-in Substitution Strings
You’ll find a complete list along with description and usage examples. If you’re new to APEX or didn’t know about all the substitution strings I suggest scanning through it so you know which ones are available and what they contain.