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...