Suppose you needed to create a record in a table just to generate an id field which is automatically generated (as an identity column). An example of why you’d want to do such a thing is if you need to generate invoices. All the invoices that are generated at the same time should be part of the same invoice_batch. Before creating an invoice, the parent record (invoice_batch) needs to be created.

So how do you insert a record into a table with no values? The follow examples shows how to do this in Oracle:

Table Setup

1
2
3
4

create table invoice_batch (
  invoice_batch_id number generated always as identity not null
  -- ... (additional columns)
);

Examples

Suppose you want to insert a record into the invoice_batch table just to get an invoice_batch_id it could look like:

1
2
3
4
5

insert into invoice_batch;

-- Error
SQL Error: ORA-00926: missing VALUES keyword
00926. 00000 -  "missing VALUES keyword"

The above code raises an error as Oracle requires a values clause. But what do you put in there? If null is used the following happens:

1
2
3
4
5
6

insert into invoice_batch(invoice_batch_id)
values(null);

-- Error
SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"

To get around this you need to use the keyword default instead of null:

1
2
3
4
5

insert into invoice_batch(invoice_batch_id)
values(default);

-- Success
1 row inserted.

In our case it would be recommended to have a returning into clause to get back the invoice_batch_id to be used elsewhere.

I was recently developing an APEX page that had a lot of page items and a lot of Dynamic Actions (DA) on it. This is not usually a recommended approach (given that it can add complexity) but was justified given the business requirements. When testing the page I kept getting the This page is asking you to confirm that you want to leave - data you have entered may not be saved. when navigating away from the page despite not having explicitly changed any values.

If you’re not too familiar with how this message works / is configured read APEX: Warn on Unsaved Changes

I tried a few quick checks to see if I could isolate which page item was causing the issue but wasn’t able to easily find it. Instead I created a script to find out which page item had changed. Here is the script I created to find out which item(s) changed which is based on this article:

I’ve included a direct reference got Github Gist so that if updated it’ll be reflected here

A few notes / enhancements:

• Consider using apex.item().isChanged() syntax
• Could explicitly loop through all APEX page items using the undocumented function apex.page.forEachPageItem that Neil Fernandez wrote about

Once I found the problem item I could easily resolve why it was changing and the warning didn’t show up any more.

If you think there’s some additional elements that I should add to this list please update the Gist

When an user makes a change to a form (ex: updates their phone number) and tries to navigate away (ex: hit back button, close tab, etc) without saving their changes they’ll get the following warning message: This page is asking you to confirm that you want to leave - data you have entered may not be saved.

To configure this option at a page level modify the page attribute Warn on Unsaved Changes

In some cases some page items will be changed but users shouldn’t be warned about them. Usually these are items that you have to store “temporary data” to be used in Dynamic Actions. To ignore these page items from change warnings, modify the page item and set its Warn on Unsaved Changes:

Follow up article: How to Find Which Item has Been Changed in APEX

If you want to connect to a Autonomous Transaction Processing (ATP) database in the Oracle Cloud (OCI) using SQL*Plus, SQLcl, or SQL Developer you need to download a Credentials Wallet. If you’re unaware of this process please read this blog before continuing.

When connecting to to the database you may get the following error: ORA-28759: failure to open file. I got this error when connecting using a Docker container for SQLcl. Note here are some docker images for both SQLcl and sql*plus

To resolve this issue you just need to modify the sqlnet.ora file (in the downloaded zip file) and change:

1

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))

to:

1

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))

Reminder: You need to set your TNS_ADMIN string

Thanks to Adrian Png for the help on this and related Github Issue

There’s been some feedback I’ve received on this so read all the way through

Let’s start with a little quiz. Without checking, what do you think happens/returns running the following query:

1
2
3

-- Note the two digit year
select to_date('24-Jan-21', 'DD-MON-YYYY')
from dual;

I recently asked this same question on Twitter and the results were quite interesting given that this should be a very straight forward answer. Over 50% of the people got it wrong:

The correct answer is that the query returns: 24-Jan-0021. If you thought an error would be raised you’re in good company. I reached out to several Oracle ACEs (i.e. world leading Oracle experts) and they all got it wrong as well (myself included).

By using a date format of DD-MON-YYYY if a user does not explicitly enter a four digit year, Oracle will left pad the number with 0s. I.e. 21 becomes 0021. This makes sense that one would expect 5-Jan-2021 to really be 05-Jan-2021.

What does this have to do with your APEX application? In all my applications I tend to set the default date format to DD-MON-YYYY as it’s very explicit. Note: to set the default date format in APEX go to Shared Components > Globalization > Application Date Format. Most of the Date items that I use allow users to either enter the date manually or select from the date picker (via button click). Since entering a two digit year is a valid date neither APEX or Oracle raises an error so it’s extremely difficult to catch.

To get around this issue you can change the Application Date Format to: DD-MON-RRRR. From the Oracle documentation:

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

Using our initial example

1
2
3
4
5

select to_date('24-Jan-21', 'DD-MON-YYYY')
from dual;

-- Returns
24-jan-2021

The nice thing about using the RRRR format is you can still display years as four digits but users can enter them in as two digits with expected results. If users are entering past dates (ex: cataloging old library books) I suggest you read the documentation to see if the RRRR format is the right setting for your application.

Update

Based on some Twitter feedback by Andy Sayer he pointed out that we could use the FX option in the date format to enforce format exact. Some examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

select to_date('24-Jan-21', 'FXDD-MON-YYYY')
from dual;

-- Error
ORA-01862: the numeric value does not match the length of the format item

select to_date('24-Jan-2021', 'FXDD-MON-YYYY')
from dual;

-- Returns
24-jan-2021

-- But not including a "0" prefix for the day number will also result in an error
select to_date('4-Jan-2021', 'FXDD-MON-YYYY')
from dual;

-- Error
ORA-01862: the numeric value does not match the length of the format item

As you can see they’re some pros and cons to this approach. Documentation on the FX modifier can be found here

There was also some discussion about using the FM modifier in conjunction with the FX modifier to resolve the day padding problem. This doesn’t solve our two digit year problem as it will just be padded with 0s which means we’re back to our original problem as shown below. Documentation on FM modifier can be found here.

1
2
3
4
5

select to_date('24-Jan-21', 'FXFMDD-MON-YYYY')
from dual;

-- Returns
24-jan-0021

In some Dynamic Actions (DA) settings in APEX you have the option to use a JavaScript (JS) Expression. A common example of this is setting a value where the Set Type is JavaScript:

As the name (JavaScript Expression) suggests this should be an expression such as 1 + 2; If you try to run multiple lines of code with a return statement (as shown above) the following error is raised:

1

Uncaught TypeError: apex.da.initDaEventList is not a function

To resolve this issue you can use an immediately-invoked Function Expressions (IIFE) as the JavasScript Expression. The following example re-writes the code from the first example as an IIFE and will work as a valid JavaScript Expression in the DA:

1
2
3
4

(function() {
  var today = new Date();
  return today.getFullYear();
})()

Thanks to Adrian Png and Trent Schafer for the help on this.

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