DBA Hub

📋Steps in this guide1/7

SQLcl and APEXExport : Export APEX Applications and Workspaces From the Command Line

This article describes how to use SQLcl and the APEXExport utility to export applications and workspaces.

oracle miscconfigurationintermediate
by OracleDba
114 views
1

SQLcl

The APEXExport utility has been deprecated. Instead use the following SQLcl commands. More information about these commands can be found by using the following help commands directly from SQLcl. The rest of the article will discuss the now deprecated APEXExport utility.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
-- Application- Single file
apex export -applicationid 100

-- Application - Split file
apex export -applicationid 100 -split

-- Workspace
apex export -workspaceid 1908816359534887

help apex export-application
help apex export-application syntax
help apex export-application examples
2

Software

You will need the APEX software and a copy of the "ojdbc8.jar" file. You can download ojdbc8.jar, or copy it from an existing product such as SQL Developer. - APEX - ojdbc8.jar
3

Identify Applications

The view displays information about the APEX applications.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Connect to the workspace user.
conn dev_ws/dev_ws@//localhost:1521/pdb1

COLUMN workspace FORMAT A20
COLUMN workspace_id FORMAT 99999999999999999999
COLUMN application_id FORMAT A14
COLUMN application_name FORMAT A20

-- List applications.
SELECT workspace,
       workspace_id,
       application_id,
       application_name
FROM   apex_applications
ORDER BY 1;

WORKSPACE          WORKSPACE_ID APPLICATION_ID APPLICATION_NAM
--------- --------------------- -------------- ---------------
DEV_WS         2600821031139055            101 Demo App

SQL>
4

Windows Example

Place the software into a directory of your choice. In this example we will use the "C:\temp" directory. Unzip the APEX software and copy the "ojdbc8.jar" file to the utilities directory. Set the environment variables to allow the APEXExport utility to function, and the settings to tell it how to connect to the relevant database to capture the application or workspace of interest. Run the APEXExport utility to extract the SQL representation of the applications or workspace.

Code/Command (click line numbers to comment):

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
41
42
43
44
45
46
47
48
49
50
51
52
53
cd c:\temp
unzip apex_20.1_en.zip
copy ojdbc8.jar c:\temp\apex\utilities

set JAVA_HOME="C:\Program Files\Java\jre1.8.0_261"
set APEX_BASE=C:\temp\apex
set CLASSPATH=%APEX_BASE%\utilities\ojdbc8.jar;%APEX_BASE%\utilities

set DB_HOST="ol7_19.localdomain"
set DB_PORT=1521
set DB_SERVICE=pdb1
set DB_USERNAME=MY_APEX_DB_USER
set DB_PASSWORD="MyPassword1"
set APEX_APP_ID=101
set WORKSPACE_ID=2600821031139055

Rem Application

%JAVA_HOME%\bin\java oracle.apex.APEXExport ^
  -db %DB_HOST%:%DB_PORT%/%DB_SERVICE% ^
  -user %DB_USERNAME% ^
  -password %DB_PASSWORD% ^
  -workspaceid %WORKSPACE_ID% ^
  -applicationid %APEX_APP_ID% ^
  -expOriginalIds 

Rem All Apps in Workspace

%JAVA_HOME%\bin\java oracle.apex.APEXExport ^
  -db %DB_HOST%:%DB_PORT%/%DB_SERVICE% ^
  -user %DB_USERNAME% ^
  -password %DB_PASSWORD% ^
  -workspaceid %WORKSPACE_ID% ^
  -expOriginalIds 

Rem Workspace

%JAVA_HOME%\bin\java oracle.apex.APEXExport ^
  -db %DB_HOST%:%DB_PORT%/%DB_SERVICE% ^
  -user %DB_USERNAME% ^
  -password %DB_PASSWORD% ^
  -expWorkspace ^
  -workspaceid %WORKSPACE_ID% ^
  -expOriginalIds 

Rem All Workspace

%JAVA_HOME%\bin\java oracle.apex.APEXExport ^
  -db %DB_HOST%:%DB_PORT%/%DB_SERVICE% ^
  -user %DB_USERNAME% ^
  -password %DB_PASSWORD% ^
  -expWorkspace ^
  -expOriginalIds
5

Linux Example

Place the software into a directory of your choice. In this example we will use the "/tmp" directory. Unzip the APEX software and copy the "ojdbc8.jar" file to the utilities directory. Set the environment variables to allow the APEXExport utility to function, and the settings to tell it how to connect to the relevant database to capture the application or workspace of interest. Run the APEXExport utility to extract the SQL representation of the applications or workspace.

Code/Command (click line numbers to comment):

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
41
42
43
44
45
46
47
48
49
50
51
52
53
cd /tmp
unzip apex_20.1_en.zip
cp ojdbc8.jar /tmp/apex/utilities

JAVA_HOME=/u01/java/latest
APEX_BASE=/tmp/apex
export CLASSPATH=${APEX_BASE}/utilities/ojdbc8.jar:${APEX_BASE}/utilities

DB_HOST="ol7_19.localdomain"
DB_PORT=1521
DB_SERVICE=pdb1
DB_USERNAME=MY_APEX_DB_USER
DB_PASSWORD="MyPassword1"
APEX_APP_ID=101
WORKSPACE_ID=2600821031139055

# Application

${JAVA_HOME}/bin/java oracle.apex.APEXExport \
  -db ${DB_HOST}:${DB_PORT}/${DB_SERVICE} \
  -user ${DB_USERNAME} \
  -password ${DB_PASSWORD} \
  -workspaceid ${WORKSPACE_ID} \
  -applicationid ${APEX_APP_ID} \
  -expOriginalIds 

# All Apps in Workspace

${JAVA_HOME}/bin/java oracle.apex.APEXExport \
  -db ${DB_HOST}:${DB_PORT}/${DB_SERVICE} \
  -user ${DB_USERNAME} \
  -password ${DB_PASSWORD} \
  -workspaceid ${WORKSPACE_ID} \
  -expOriginalIds 

# Workspace

${JAVA_HOME}/bin/java oracle.apex.APEXExport \
  -db ${DB_HOST}:${DB_PORT}/${DB_SERVICE} \
  -user ${DB_USERNAME} \
  -password ${DB_PASSWORD} \
  -expWorkspace \
  -workspaceid ${WORKSPACE_ID} \
  -expOriginalIds

# All Workspaces

${JAVA_HOME}/bin/java oracle.apex.APEXExport \
  -db ${DB_HOST}:${DB_PORT}/${DB_SERVICE} \
  -user ${DB_USERNAME} \
  -password ${DB_PASSWORD} \
  -expWorkspace \
  -expOriginalIds
6

Full Usage Information

The following usage is displayed when the APEXExport utility that ships with version 19.1 is called without parameters.

Code/Command (click line numbers to comment):

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
$ ${JAVA_HOME}/bin/java oracle.apex.APEXExport -h
Usage java oracle.apex.APEXExport [option]...
Available options:
    -h:                    Print this help
    -db:                   Database connect url in JDBC format
    -user:                 Database username
    -password:             Database password
    -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
    -expNoSubscriptions:   Do not export references to subscribed components
    -expComments:          Export developer comments
    -expSupportingObjects: Pass (Y)es, (N)o or (I)nstall to override the default
    -expACLAssignments:    Export ACL User Role Assignments
    -dir:                  Save all files in the given directory, e.g. -dir some/sub/directory
    -list:                 List all changed applications in the workspace or components in the application
    -changesSince:         Expects date parameter (yyyy-mm-dd). Limit -list values to changes since the given date
    -changesBy:            Expects string parameter. Limit -list values to changes by the given user
    -expComponents:        Export application components. All remaining parameters must be of form TYPE:ID
    -debug:                Print debug output
    -nochecksum:           Overwrite existing files even if the contents have not changed
    -split:                Split applications into multiple files

    Application Example:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -applicationid 31500
    Workspace  Example:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999
    Instance Example:
       APEXExport -db localhost:1521:ORCL -user system -password systems_password -instance
    Components Example:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -applicationid 31500 -expComponents PAGE:1 PAGE:2 AUTHORIZATION:12345678
    List Example:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -list -changesSince 2019-07-29
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -applicationid 31500 -list -changesBy EXAMPLE_USER
    Export All Workspaces Example:
       APEXExport -db localhost:1521:ORCL -user system -password systems_password -expWorkspace
    Export Feedback to development environment:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -expFeedback
    Export Feedback to deployment environment EA2 since 20100308:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -expFeedback -deploymentSystem EA2 -expFeedbackSince 20100308
$
7

What About Import?

The APEXExport utility produced regular SQL files, so you don't need a specific tool to import them. You can just use SQL*Plus or SQLcl to load them. To load a workspace export, you need to connect to a database user with the APEX_ADMINISTRATOR_ROLE role granted to it. To load an application export, you should connect to the workspace database user. You can also use tools like Liquibase to manage your database, including APEX applications. For more information see: - APEXExport : Export APEX Applications and Workspaces From the Command Line - APEX Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!