Two weeks ago I had the privilege of attending the Oracle Cloud World 2023 conference, an event that showcased Oracle’s commitment to a new era of collaboration and customer-centricity, aptly termed “One Oracle”. The conference focused on three key pillars: Artificial Intelligence (AI), Autonomous Database and APEX. What made this experience even more special was the opportunity to speak at the event, which not only brought together a diverse and enthusiastic audience but also led to some delightful interactions and feedback.
Discover Boyd’s captivating blog right here..
Some end users have been complaining to me about losing their public and private reports after installing a new version of an application. Somewhere, somehow, the connection between the application and the saved reports has been lost. This has now happened several times and something has to change. I’ve chosen to revoke the create public report privilege of all end users and convert all public reports to alternative reports.
When exporting the application from acceptance environment the following settings are met.
Public and private reports aren’t exported because all the saved reports, except primary and alternative, only exist in the production environment. The Export with Original IDs option is set to yes, this will ensure that all the component IDs are the same as of the last import. When upgrading an application through the DTAP-street the component IDs should be the same. These IDs are what APEX uses to load the correct items on a page. When an ID changes a mismatch will happen, and the component isn’t loaded.
Somewhere along the line some of the IDs have been changed and as a result the end users can no longer find the saved public reports.
First, we have to find the missing public reports. When connecting to the APEX-schema, you can find all the views which are used by APEX. The view apex_application_page_ir_rpt contains all the saved Interactive Reports, including the reports of the builder. The following query retrieves all reports excluding the APEX builder reports and the primary reports.
select
workspace
, application_id
, page_id
, interactive_report_id
, report_id
, application_user
, report_name
, status
, report_type
from
<apex_user>.apex_application_page_ir_rpt
where
report_type != 'PRIMARY_DEFAULT'
and workspace != 'INTERNAL';
The application_user shows the user who saved the report and interactive_report_id contains the foreign-key id to the report region id. This can be found using the view apex_application_page_ir. The report_type and status columns are important, both should contain “PUBLIC” to be of interest here.
When all the (missing) public reports have been found, we could take back control over them by changing the report type and application user. We no longer allow the creation of public reports, so all reports should be default (alternative or primary) reports. This gives the control back to developers and should reduce the proliferation of reports in the application.
The following statement updates one or all public reports. By setting the application_user to “APXWS_ALTERNATIVE” all developers become owner of the reports. It’s also possible to update the interactive_report_id column with the correct IDs, but this isn’t the solution I’m after.
-- individual update
update <apex_user>.wwv_flow_worksheet_rpts
set application_user = 'APXWS_ALTERNATIVE'
, is_default = 'Y'
where id = < report_id >;
-- update all public reports
update <apex_user>.wwv_flow_worksheet_rpts
set application_user = 'APXWS_ALTERNATIVE'
, is_default = 'Y'
where application_user not in ('APXWS_ALTERNATIVE','APXWS_DEFAULT')
and r.status != 'PRIVATE';
Lastly, if you want to change filters or the selected columns of a saved report it is also possible to do this via the database. The table wwv_flow_worksheet_rpts contains multiple columns for filtering, sorting and computing the report. Updating the columns will result in a different saved report.
Conclusion
By updating the application user to “APXWS_ALTERNATIVE” in the table wwv_flow_worksheet_rpts it’s possible to change the owner of a saved report. This helped me as a developer to take back control over the saved reports.
Oracle DBAs love to use SQL*Plus when installing database objects. As a developer, I make SQL install scripts. Which then are executed by these DBAs. These scripts contain references to other files with the actual SQL statements. Below is an example of an install script, here the user will be prompted with the step and then the file will be executed.
Prompt install tables
@@table_emp.sql
Prompt install triggers
@@trigger_emp.sql
Prompt install views
@@view_emp.sql
...
Normally the script runs and all the statements are executed without problems. Lately, I’ve encountered some problems after I started to use the IDE PL/SQL Developer for writing my scripts.
When executing the install script using sqlplus, errors start to appear. Listed below, is a snippet of the logging. The error “SP2-0734:” is a general error for statements that aren’t recognized.
install tables
SP2-0734: unknown command beginning "create ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
install triggers
...
When I look at the create table statement nothing seems incorrect. Even when I copy the statement and execute this in sqlplus the statement is accepted and the table is created. This means something should be wrong with the file and not with the statement.
When searching for the SP2-0734 error most of the solutions lay in correcting its statement. But here it wasn’t the case. When I delved deeper into the logging file I found some interesting errors:
SP2-0734: unknown command beginning "create ..." - rest of line ignored. SP2-0734: unknown command beginning "for ..." - rest of line ignored.
For the first time, I noticed an error in my statement, namely . I don’t see these weird characters in my statement, but sqlplus does see them. This must mean that there is an error in the file itself. Opening the file with SQL Developer or PL/SQL Developer gave me no further insight.
Notepad++, on the other hand, does. Notepad++ gives you the option to save a file with a certain encoding. When I looked at this encoding I saw that my files were saved with the UTF-8 BOM encoding instead of UTF-8.
BOM (Byte Order Mark) is a particular usage of the special Unicode character, whose appearance as a magic number at the start of a text stream can signal several things to a program reading the text. BOM use is optional. Its presence interferes with the use of UTF-8 by software that does not expect non-ASCII bytes at the start of a file but that could otherwise handle the text stream. [1] The use of BOM, therefore, is discouraged.
Here you have it, software that does not expect non-ASCII bytes at the start of a file can behave differently. Saving all the files with encoding UTF-8 instead of UTF-8 BOM and running the install script again resulted in 0 errors.
Now that the install script was working correctly I needed to find out why PL/SQL Developer saves files with BOM encoding. When searching the preferences I found in Files > Format the option “Encoding”. Default the checkbox “Save with BOM (Unicode Byte Order Mark)” is checked. By unchecking this option the files will now be saved correctly.
Conclusion
When faced with the “SP2-0734: unknown command beginning ..” error in SQL*Plus and you’re sure the statement is valid, please look for the encoding of your file. When files have the encoding UTF-8 BOM change this to UTF-8.
