Otherwise reported as “WWV_FLOW_FORM_REGION.NO_PRIMARY_KEY_ITEM” or merely “Internal error”, this bug caused me to waste hours of investigation, twice, because a simple Google search was not returning any results. This post is merely to remedy that situation.
On an APEX 19.1 page with a new Form region combined with an Interactive Grid region, when the user makes a change to a record and Submits the page, they get this error.
A common requirement is to format data for display in an Interactive Report, for example showing an icon, making part of the data a clickable link or a button, or showing it in different colours.
The problem with embedding formatting in the data for the report is that it is not only used for display in the web page; it is also used for the Download option, causing user confusion when HTML code is exposed in the generated CSV file.
If the logic for the formatting is not data-driven, the solution is to enter the HTML in the HTML Expression attribute on the report column definition. The HTML Expression is used only when displaying the data in the interactive report; the underlying column data is used for the Download. The underlying column data is also used to generate the filter list for the column (if Column Filter Type is “Default Based on Column Type”).
If the logic for the formatting is data-driven, a simple solution is to generate the HTML in an underlying column in the query. However, you don’t want this HTML appearing in the Download CSV, so what you can do is generate the HTML in an additional, hidden column in the report; then use the hidden column in the HTML Expression attribute. This works because the HTML Expression attribute is allowed to refer to any column in the query, even hidden ones.
For example, here is a query with a generated column that determines an icon to be displayed within the “job” column:
select ename,
job,
job
|| ' <span class="fa '
|| case when mgr is null then 'fa-gear' else 'fa-user' end
|| '"></span>' as job_html
from emp
The following column attributes are set:
JOB: HTML Expression = #JOB_HTML#
JOB_HTML: Type = Hidden Column
When the report is run, we see the icons displayed:
When the report is downloaded as CSV, the Job column is plaintext as desired:
If you’re using Atlassian SourceTree with a git repository and you do a local commit but then realise the message was incorrect, you can amend it before you push it to remote. To set this up, you can create a “Custom Action” in SourceTree:
Tools -> Options -> Custom Actions
Click Add
Set Menu caption, e.g. “Amend commit message”
Select “Open in a separate window” and unselect “Run command silently”
Set Script to run to “git.exe” including path
Set Parameters to “commit –amend”
Now, whenever you want to correct the message on your latest commit, you right-click on the commit, and select “Custom Actions” -> “Amend commit message“. A terminal window will open with a vi editor open with the current commit message at the top of the file (there will also be some instructions with # at the start of each line; you can leave them alone).
If you know vi, you’ll know what to do. Here’s a quick guide:
To start editing, press “i” and then edit the message (on the first line of the file).
To delete everything on the line, press ESC, followed by “ddO” (this will delete the line and then return you to edit mode to type the new message)
To save and quit, press ESC, followed by “:wq“
To quit without making any changes, press ESC, followed by “:q!“
A user sent me a CSV they had downloaded from my APEX application and then subsequently updated. I needed to know which database column was the source for each column in the spreadsheet; this was not as simple as you might think because the labels from this report (that has a bewilderingly large number of columns) were quite often very different from the database column name.
To map their spreadsheet columns to database columns I used this simple query:
select x.interactive_report_id, x.report_label, x.column_alias
from apex_application_page_ir_col x
where x.application_id = <my app id>
and x.page_id = <my page id>
order by x.interactive_report_id, x.report_label;
After upgrading APEX I found this query useful to review all the plugins I had installed across multiple workspaces and in multiple applications to find ones that needed to be upgraded.
select name
,plugin_type
,version_identifier
,workspace
,application_id
,application_name
from APEX_APPL_PLUGINS
where workspace not in ('INTERNAL','COM.ORACLE.CUST.REPOSITORY')
order by name, workspace, application_id;
I was working on an application in an APEX instance that was not configured for email (and would not be), but a number of interactive reports were allowing users to use the “Subscription” or the “Download as Email” features. If they tried these features, those emails would just go into the APEX mail queue and never go anywhere, so I needed to turn these off.
I listed all the interactive reports that need fixing with this query:
select page_id
,region_name
,show_notify
,download_formats
from apex_application_page_ir
where application_id = <my app id>
and (show_notify = 'Yes' or instr(download_formats,'EMAIL') > 0);
I needed to get a list of all the tabular forms in my application along with which table they were based on. This query did the trick:
select page_id
,attribute_02 as data_source
,page_name
,region_name
,attribute_03 as key_column
from apex_application_page_proc
where application_id = ...my app id...
and process_type_code = 'MULTI_ROW_UPDATE'
order by 1, 2;
If you have an ordinary Oracle APEX form with the standard Automatic Row Fetch process, and the page has an optional item with a default value, APEX will set the default on new records automatically. However, if the user queries an existing record, Oracle APEX will also fill in a missing value with the default value. This might not be what is desired – if the user clears the value they would expect it will stay cleared.
If you only want the default to be applied for new records, change the attribute on the item so that the default is only applied to new records:
Set Default Value Type to PL/SQL Expression
Set Default value to something like: case when :P1_ID is null then 'xyzzy' end
APEX_EXPORT – new package with supported methods for exporting APEX applications and other scripts into a CLOB: get_application, get_workspace_files, get_feedback, get_workspace
APEX_JWT – new package for JSON Web Tokens – encode, decode, validate
APEX_SESSION – new procedures: create_session, delete_session, attach, detach
What I’m particularly looking for is a good reference for the interactiveGrid API. Hopefully they’ll add this soon. There is a reference for a “grid” widget but that is not the API for interactive grids. I notice there are APIs for actions and model which are relevant to interactive grids.
I’ll be presenting a talk about some APEX applications I’ve been building gradually over the past five years at the AUSOUG Connect 2017 conference in Perth, Western Australia on 29 November.
My hope is that you’ll get some ideas on how best to set up the overall architecture for your APEX applications that may need to serve more than one customer – whether different organisations or different business groups within your organisation.
Although it’s just one day, there’s a lot of good content packed in for developers, DBAs and eBusiness professionals so you really don’t want to miss it!
I’m looking forward in particular to hearing the following talks:
