If you ever need to generate PDF reports, Excel files, or Word documents based on data from your APEX application (or from within your Oracle database) APEX Office Print (AOP) is the tool for you. If you’ve never used or heard of AOP before you should review it before continuing with this article.

This is the desired outcome of the article below. To summarize the solution will allow me to quickly edit a Word template using Office 365. Then view the generated PDF in APEX with a minimal amount of effort.

Background

AOP has two common uses cases (personal opinion). The first is providing users the ability to download their reports exactly as they’re shown in any format they’d like. For example if they build an Interactive Report (IR) with row highlighting etc and click “Download”, AOP will make the report look exactly as the user defined it. The second, which this article focuses on, is to create customizable documents based on data in Oracle. A good example of this is generating a invoice for a customer.

Problem

Using the later case, to generate a PDF invoice you need to create a Word or Excel file and use substitution string for AOP to replace data with. For example, to show the Invoice Number based on the column invoice_num you would reference {invoice_num} in your Word document. AOP with then “fill in” the substitution strings with your data. In this case the process is as follows:

1. Define your query (with all the data)
   • One time task unless more data is required
2. Upload Word template file
   • They’re various options as to where to store a file (more on this later)
3. Generate the PDF
   • This will involved downloading and then opening the PDF on your laptop

When initially building a template file the last two steps can happen many times. I.e. I want to see what the invoice would look like if I move some text over a bit. I’d need to re-upload the file then re-generate and download the PDF. This process has a few problems:

• It’s not good for multiple people working on the same template at the same time
• There’s lots of steps involved once making a change to the template to see the resulting PDF

Solution

To resolve the issues described above I created the following process to allow me to quickly build and view invoices using AOP and APEX. These steps require that you have an Office 365 account. (Note: if using a coporate Office 365 you’ll need the ability to share files with people outside of your organization. If you’re not allowed to use a personal account to host the template file)

Host Template on OneDrive

• Go to One Drive and upload a new Word document
  • Note: For some reason when creating the Word document directly in One Drive AOP can’t process the file. To get around this create a Word document on your desktop and upload
  • Note: the same steps can be used for any type of Office document (ex: Excel)
• Generate Share URL
  • This will be a link to give others to work on modifying the Word template file
  • We’ll refer to this URL as the “Share URL”
  • Right click on the file and click Share

• A new modal box pops up. Make sure to select the appropriate permissions (not critical that everyone can edit but does make it easy to share the link)
• Select Copy link and save this link somewhere
  • In my example the link is: https://1drv.ms/w/s!AmD6WK_LJIvHq8xyy0JR-JT6ihJdPQ?e=xC88lZ

• Generate Embed URL
  • This will be used by your database to download the template file and send to AOP
  • We’ll refer to this URL as the “Embed URL”
  • Right click on the file and click Embed

• A new slider pops up on the right hand side. Click Generate

• Copy the iframe HTML tag and save this link
  • In my example the HTML is: <iframe src="https://onedrive.live.com/embed?cid=C78B24CBAF58FA60&resid=C78B24CBAF58FA60%21714354&authkey=AKLKkXB0W5OfUZ4&em=2" width="476" height="288" frameborder="0" scrolling="no"></iframe>

APEX Setup

Make sure that your APEX application has the latest AOP plugins installed.

Create a new page with a new region. Add the following text page items (Note: you can change to hidden later on / load from a table)

• P3120_ONEDRIVE_EMBED_IFRAME

In the region add the following in the Source > Text box:

<div id="pdf" data-aop-inline-pdf="onClick: Generate PDF" style="width:100%;height:600px;"></div>

Create a new region button called GENERATE_PDF

• Set the Button Position to Edit
• Right click on the button and Create Dynamic Action
  • Name: onClick: Generate PDF
    • Note: Do Not this name must match the data-aop-inline-pdf from the previous div tag
  • Add the following actions:
    • Execute Server Side Code
      • Items to Submit: P3120_ONEDRIVE_EMBED_IFRAME
      • PL/SQL Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

declare
  l_download_url varchar2(4000);
  l_blob blob;
  l_file_name varchar2(4000);
begin
  -- Need to replace ampersand with amp; for XML parsing
  -- Need to do in SQL as extractvalue not PL/SQL
  select replace(extractvalue(xmltype(replace(:p3120_onedrive_embed_iframe, chr(38), chr(38) || 'amp;')), '/iframe/@src'), 'embed', 'download')
  into l_download_url
  from dual;
  

  -- Need to download the blob here as AOP expects a filename in the URL. The Office downloads don't have that.

  -- Download the file into a blob
  l_blob := apex_web_service.make_rest_request_b(
    p_url => l_download_url,
    p_http_method => 'GET');

  -- Need to extract filename so that we can get the file extension
  for i in 1.. apex_web_service.g_headers.count loop
    if apex_web_service.g_headers(i).name = 'Content-Disposition' then
      -- Value will look like: attachment; filename="wo-test.docx"
      l_file_name := trim('"' from  regexp_substr(apex_web_service.g_headers(i).value, '".*"'));
      exit; -- Don't need to parse headers anymore
    end if;
  end loop;

  -- Store in Collection for retrieval from AOP
  apex_collection.create_or_truncate_collection(
    p_collection_name => 'AOP_TEMPLATE');

  -- Load
  apex_collection.add_member(
    p_collection_name => 'AOP_TEMPLATE',
    p_c001 => l_file_name,
    p_c002 => apex_string_util.get_file_extension(l_file_name),
    p_blob001 => l_blob
  );
end;

• Plugin: UC - APEX Office Print (AOP) - DA [Plug-In]
  • Note: You’ll to have installed the AOP Plugins for this to show up in list
  • Settings:
    • Template Type: SQL
    • Template Source:

1
2
3
4
5
6

select
  ac.c002 file_ext,
  ac.blob001 blob_contents
from apex_collections ac
where 1=1
  and ac.collection_name = 'AOP_TEMPLATE'

• Data Type: SQL
• Data Source:

1
2
3
4
5
6
7
8

-- Change this to reference you tables etc
select
  'demo' as "filename",
  cursor(
    select to_char(sysdate, 'DD-MON-YYYY') as "today"
    from dual
  ) as "data"
from dual

• Output Type: PDF
  • Note you can change this to a different type of file later but for the preview in APEX, PDF is required
• Output To: Inline Region (pdf/html/md/txt only)
  • This is what will make the PDF show up in APEX

Using the iFrame URL, users should be able to see the preview in APEX based on changes made in Office 365 (see video at top of article)

After upgrading to macOS Big Sur I couldn’t run Oracle SQL Developer. I got the following error: The application “SQLDeveloper.app” can’t be opened

To resolve this issue I got help from Kris Rice and Niels de Bruijn. The issue is caused by Apple and the Java applet plugin.

Here’s how I got SQL Developer working:

List your Java Versions

1
2
3
4
5
6
7
8
9

/usr/libexec/java_home -V

# This will give an output like:

Matching Java Virtual Machines (3):
    11.0.2 (x86_64) "Oracle Corporation" - "Java SE 11.0.2" /Library/Java/JavaVirtualMachines/jdk-11.0.2.jdk/Contents/Home
    1.8.271.09 (x86_64) "Oracle Corporation" - "Java" /Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home
    1.8.0_201 (x86_64) "Oracle Corporation" - "Java SE 8" /Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home
/Library/Java/JavaVirtualMachines/jdk-11.0.2.jdk/Contents/Home

The one that caused the problem is the "Oracle Corporation" - "Java" /Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home and you’ll need to remove it.

Remove the Applet Plugin

1
2
3
4
5
6
7
8
9
10
11

sudo rm -rf "/Library/Internet Plug-Ins/JavaAppletPlugin.plugin/"


# After deleting this folder it should be removed from the Java Home list
/usr/libexec/java_home -V

# Output
Matching Java Virtual Machines (2):
    11.0.2 (x86_64) "Oracle Corporation" - "Java SE 11.0.2" /Library/Java/JavaVirtualMachines/jdk-11.0.2.jdk/Contents/Home
    1.8.0_201 (x86_64) "Oracle Corporation" - "Java SE 8" /Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home
/Library/Java/JavaVirtualMachines/jdk-11.0.2.jdk/Contents/Home

If you run SQL Developer now it should work. I’m not sure about the side effects of removing this Java applet plugin. If I find it to be critical to other things I’ll be sure to update this post.

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:

• How to take a screenshot
• Screenshot tool features

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
• Where: Subscribe at apex.oracle.com/officehours so you can receive reminders for this special event.
• Format: Each talk will begin on the hour. They will be 45 mins followed by a moderated 10min Q&A then a 5 min break where the speakers will change.

The full schedule and links to talk can be found here. The schedule includes a link to add each talk that you want to attend to your calendar.

[email protected] is for the community, by the community. Please help spread the word to your friends, colleagues, schools, clients, etc!

- Adrian, Martin, and Trent

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),
    ...
    where rowid  = :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.
• Server-side Condition > Type: Rows Returned. SQL Query:

1
2
3
4
5
6

select 1
from apex_application_pages aap
where 1=1
  and aap.application_id = :app_id
  and aap.page_id = :app_page_id
  and lower(aap.page_mode) = 'modal dialog'

Add a button to this region called RELOAD_MODAL:

• Button Name: RELOAD_MODAL
• Behavior
  • Action: Redirect to URL
  • Target
    • Type: URL
    • URL: javascript:location.reload();
• Optional (that I used for this demo)
  • Button Template: Text with Icon
  • Template Options > Type: Warning
  • Icon: fa-refresh

Demo of the Reload button in action

Update (moving the button to the button bar)

The screenshots and demo above all show the Reload button at the top of the page. This changes the UI behavior in dev. To move it to the modal button bar, on Page 0 do the following:

• Add a new DA: DEV_ONLY: onPageLoad
  • Event: Page Load
  • Action: Execute JavaScript Code
    • Affected Elements
      • Selection Type: Button
      • Button: RELOAD_MODAL
      • JavaScript Code:

1
2
3
4
5

// Bottom left button container
var btnContainer = $('.t-Dialog-footer').find('.t-ButtonRegion-col--left > .t-ButtonRegion-buttons');

// Move to button container
this.affectedElements.appendTo(btnContainer);

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=
  select sysdate
  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.

• Intro to aliases by Jeff Smith
• login.sql

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 Functions are functions whose output is referenced as a table in a SQL statement. Here’s an example of a simple pipelined function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- Prefix: Create supporting objects
create or replace type emp_info as object (
  ename varchar2(255),
  job varchar2(255)
);
/

create or replace type emp_info_arr as table of emp_info;
/

-- Pipelined Function (with error handling)
create or replace function 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
    pipe row (emp_info(x.ename, x.job));
  end loop;

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 *
from table(f_pipeline_demo)
where rownum <= 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
where 1=1
  and scope like '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.

In Oracle, most triggers are defined similar to the snippet below:

1
2
3
4
5

create or replace trigger trigger_name
  before insert or update or delete
  on my_table for each row
begin
...

All demos below reference the following table:

1
2
3

create table demo_table(col_x varchar2(10), col_y varchar2(10));
insert into demo_table values('hello', 'world');
commit;

They’re some cases where you only want the trigger to be run when updating specific columns. They’re various ways to do this which are shown below.

In Trigger Definition (update of ...)

Using the update of clause you can explicitly list the columns that will execute the trigger when updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

create or replace trigger demo_trigger
  before update of col_x
  on demo_table for each row
begin
  dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x);
end;
/

set serveroutput on

-- Update another column
update demo_table
set col_y = 'world';

1 row updated.

-- Note: trigger wasn't run as it's not in the list of columns defined in "update of ..."


-- Update the "triggered" column
update demo_table
set col_x = 'goodby';

-- Note: This output shows the trigger was executed
***COL_X*** is being updated: goodby

1 row updated.

Checking Column (updating('col_name'))

In this example, the trigger will run for all update statements and explicit checks can be added to see if a given column is being updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

create or replace trigger demo_trigger
  before update
  on demo_table for each row
begin

  dbms_output.put_line('Trigger started');

  if updating('col_x') then
    dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x);
  end if;
end;
/

-- Running the same examples as first demo
set serveroutput on

-- Update another column
update demo_table
set col_y = 'world';

Trigger started

1 row updated.

-- Note: trigger was run (as shown by "Trigger Started" message) but COL_X wasn't updated

-- Update the "triggered" column
update demo_table
set col_x = 'goodby';

Trigger started
***COL_X*** is being updated: goodby

1 row updated.

-- Trigger was run and COL_X was updated

I recommend reading the Oracle documentation on Conditional Predicates for Detecting Triggering DML Statement

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

select column_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 on 1=1
  join (select column_value job from apex_string.split('CLERK,MANAGER', ',')) x on 1=1
    and x.job = e.job
;

Recently Stefan Dobre tweeted 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
where 1=1
  and e.job member of 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

create or replace function wrapper_split(
  p_str in varchar2,
  p_sep in varchar2)
  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
where 1=1
  and e.job member of wrapper_split('CLERK,MANAGER', ',')
;

-- ...

-- Check Logger:
select count(1) cnt
from logger_logs_5_min
where scope = 'wrapper_split'
;

   CNT
______
    14

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
where 1=1
  and e.job member of (select wrapper_split('CLERK,MANAGER', ',') from dual)
;

-- ...

-- Check Logger:
select count(1) cnt
from logger_logs_5_min
where scope = '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.