Monday, October 15, 2018

Export from APEX 5.1 after you have upgraded to APEX 18.2

I just wrapped up an upgrade for one of the development environments from APEX 5.1 to APEX 18.2 when the phone rang:

I need to export some changes I was doing in APEX 5.1, an issue has suddenly changed from low priority to high priority. The work in only in DEV, and I don't have a recent export!!  Is APEX 18.2 backwardly compatible with APEX 5.1?
 I wouldn't like to try exporting from APEX 18.2 back to APEX 5.1 - that's fraught with danger.
But it is possible to do an export from an old APEX schema provided you haven't dropped the schema, here's how..

variable name     varchar2(255)
variable contents clob
    l_files apex_050100.wwv_flow_t_export_files;
    l_files   := apex_050100.wwv_flow_export_api.get_application(p_application_id => &APPLICATION_ID. );
    :name     := l_files(1).name;
    :contents := l_files(1).contents;
set feed off echo off head off flush off termout off trimspool on
set long 100000000 longchunksize 32767
col name new_val name
select :name name from sys.dual;
spool &name.
print contents
spool off
Surprisingly easy!!!
So now the developer imports the application into the TEST environment, which is still APEX 5.1, finalizes the change and make an emergency release to promote to UAT and PROD.

Monday, October 1, 2018

ORA-28014: cannot drop administrative users

Just so I don't forget :)

SQL> drop user apex_180100 cascade;

Error starting at line : 1 in command -
drop user apex_180100 cascade
Error report -
ORA-28014: cannot drop administrative users

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> drop user apex_180100 cascade;

User APEX_180100 dropped.

Tuesday, January 9, 2018

APEXExportSplitter deprecated in Oracle APEX 5.1.4

According to the Oracle APEX 5.1.4 patchset notes the APEXExportSplitter Java utility is deprecated.

That's not a bad thing, because it's simply been included into the APEXExport utility as an extra command option.

Better yet, it's been bundled into the Oracle SQLcl 17.4 utility.

In the screenshot above you can see the following commands run from within SQLcl.

!sql -version
This issues a host command to show the SQLcl version being used.

Typing apex lists the apex applications owned by the current user.

!mkdir test
This issues a host command to create a new directory.

cd test
Within SQLcl change to the newly created directory.

apex export -applicationid 200 -skipExportDate -split
The bit we are interested in; the command to export an application from the database, and split it into seperate files in one statement.

!ls -l
Proving the f200.sql file was created, and then split into components in the f200 directory.

To see the full syntax available simply type "apex export" to view the help.

cardiac_own@orcl> apex export
    -applicationid:    ID for application to be exported
    -workspaceid:      Workspace ID for which all applications to be exported or the workspace to be exported
    -instance:         Export all applications
    -expWorkspace:     Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified
    -expMinimal:       Only export workspace definition, users, and groups
    -expFiles:         Export all workspace files identified by -workspaceid
    -skipExportDate:   Exclude export date from application export files
    -expPubReports:    Export all user saved public interactive reports
    -expSavedReports:  Export all user saved interactive reports
    -expIRNotif:       Export all interactive report notifications
    -expTranslations:  Export the translation mappings and all text from the translation repository
    -expFeedback:      Export team development feedback for all workspaces or identified by -workspaceid to development or deployment
    -expTeamdevdata:   Export team development data for all workspaces or identified by -workspaceid
    -deploymentSystem: Deployment system for exported feedback
    -expFeedbackSince: Export team development feedback since date in the format YYYYMMDD
    -expOriginalIds:   If specified, the application export will emit ids as they were when the application was imported
    -split:            Split the exported file
    -splitFlat:        Split with no directory strucure
    -splitUpdate:      Generate update.sql file while splitting
    -splitNoCheckSum:  Overwrite all files

 Application Example:            apex export -applicationid 31500
 Workspace Example:              apex export -workspaceid 9999
  Instance Example:               apex export -instance
 Export All Workspaces Example:  apex export -expWorkspace
 Export Feedback to development environment:
         apex export -workspaceid 9999 -expFeedback
 Export Feedback to deployment environment EA2 since 20100308:
         apex export -workspaceid 9999 -expFeedback -deploymentSystem EA2 -expFeedbackSince 20100308
Happy new year! Now, I've got some command scripts to update...

Tuesday, April 11, 2017

Auto-dismiss APEX Universal theme success messages

In the APEX 5.1.1 Universal Theme demonstration application, a code snippet is included showing how to automatically dismiss Success messages via JavaScript using the apex.theme42.configureSuccessMessages API.
Trouble is it only works in some circumstances, as can be seen in screenshot from a demonstration application below.

You can try the demo application to observe the issue, and a solution that works right now. You can download the application, which probably works on earlier versions of the Universal theme - I haven't tested it.

Wednesday, March 1, 2017

APEX 5.1 - Goodbye Component View

In Oracle APEX 5.1 it's possible to remove the Component View from the Page Designer.

From the Page Designer open the browsers console window and type the following code:

This is a per user preference, and persists across APEX sessions.

For more details see John Snyders' blog, I've just repeated it here so I don't forget.

Wednesday, January 4, 2017

Oracle ACE Alumnus - so long, and thanks for all the fish

The Oracle ACE program has morphed once again, with ACEs at all levels now required to fill out their contributions in the ACE Program app (written in Oracle APEX of course).

The level of detail includes:
  • type of activity (presentation, book, article, blog, forum activity, tweets etc.)
  • substantiation (conference links, presentation links, book ISBN etc)
  • analytics where relevant (blogs, tweets)

Meaning the Oracle ACE program website is out of date:
What expectations does Oracle have of an Oracle ACE?
The Oracle ACE designation is given to individuals as recognition of his/her past contributions.
Oracle's only future expectation is that the recipient maintains his/her level of community activity.
Currently to maintain an ACE status requires 200 points of activity for a year.
I did look at entering my activity, but quickly decided it just wasn’t worth the time and effort to do so.

Here is how I calculated what I would need to do to generate 200 points using my typical activities:

A new presentation takes about 100 hours to write and prepare.
A decent blog post takes about 2-4 hours to write, a cynical one to rort the system takes a few minutes.

2 presentations (2*100 hours) presented 3 times (20 points each)             => 200 hours, 120 points
16 blogs (16*3 hours) (5 points each, unless mention "Cloud" for 15 points ) =>  64 hours,  80 points

So roughly:
6 weeks to produce content (spread over months of evenings and weekends)
1 week  to present content (travel, accommodation)

Direct cost to present content (lost income, travel and accomodation expenses) => 2 weeks salary
Indirect cost to family and work/life balance - incalculable.

Plus the time taken to record activities; all this effort for something that is just a nice accolade.

I don’t get any financial benefit for presenting, either directly or indirectly, it’s just my way of giving back to the Oracle community and sharing my insights. Similarly with blogging, I only blog when I’ve got something worth sharing.

I believe the ACE program contributions criteria will have the following impacts:
  • large reduction in the number of ACEs (probably the intent)
  • a number of disenchanted and disgruntled ex-ACEs
  • rules driven behaviour to maintain status at the cost of quality/integrity
  • ACEs predominantly coming from consulting/training firms with a direct financial incentive.

Overall I think I’m better off to not bother with the ACE program, hence I've now converted to an Oracle ACE Alumnus.
Instead I'll contribute when I have something worth sharing,  and use the time/money recouped with my wife and family.

So in the words of Douglas Adams, "so long, and thanks for all the fish".

Monday, January 2, 2017

Always read the latest documentation

Something that stuck in my mind from a presentation I saw several years ago was:

Always read the latest documentation, even if you're not using that version

- Connor McDonald

 So just before Christmas APEX 5.1 was released, giving me some new APEX 5.1 documentation to read over the break. Yeah - "get a life" I hear you thinking.

Where I'm currently working we're already pretty current (Oracle 12.1 database, APEX 5.0), so I'm hoping I can convince the DBA to upgrade to APEX 5.1 reasonably soon. For me the case to upgrade is pretty simple - the application was built in 5.0 using declarative development as much as possible, has legacy features switched off, and only went live a few weeks ago, so not much data or users just yet.

Obviously reading the latest documentation is completely relevant in this situation, but why would you read the latest documentation if you're unlikely to upgrade for some time?

Here's a couple of reasons:

  • documentation improves with new releases just like code does, so you will often see better examples, improved content, or features that were previously un(der)documented.
  • by knowing whats in newer versions of APEX, you can make better decisions on how to build your application and thus future proofing todays solutions
  •  APEX 5.1 has quite a few deprecated features and desupported features, so knowing what they are allows you to start pro-actively removing them from your application now, even before an official application upgrade project kicks off.
I'm sure you can think of more/better reasons, so now you have no excuse - get reading!!