Friday, February 3, 2017

Convert Common Oracle Application Express in a CDB to Local APEX in PDBs

Introduction

When Oracle Multitenant was released with Oracle Database 12.1.0.1.0 in June 2013,  Oracle Application Express was installed by default common in the CDB in CDB$ROOT, PDB$SEED, and all PDBs. In fact, that was the architecture that we recommended at that time. As we have gained experience with multitenant in our internal deployments, we realized that our initial recommendation was wrong. Having APEX installed common locks you in to one version for your entire CDB, and when you need to upgrade APEX, you have to upgrade every PDB, even if that PDB is not using APEX. This would cause unnecessary added time to APEX upgrades.

Applying the lessons we have learned in our internal deployments, we have been recommending that customers remove Oracle Application Express from their CDBs and install APEX locally in PDBs since version 5.0.

What do you do if you have been using the common APEX from 12.1.0.1.0 or 12.1.0.2.0, and now you want to follow our new recommendation of having APEX installed locally in a PDB? We have been asked that question many times by both internal and external customers. Unfortunately there is no simple answer, no magic SQL script you can run to do the conversion, so we have never really had a good answer.

Then my manager, Joel Kallman, had a novel idea. He reminded me of the work I did for the Oracle Database Cloud Schema Service, to effectively export almost every single artifact of a workspace, using the command line utility APEXExport, so that a schema service tenant could be moved from one APEX instance to another. This involved greatly expanding our workspace export code, modifying APEXExport to allow for more export options, and then scripting the calls to APEXExport, including creating an installer script to invoke all the exports, and finally zipping the scripts produced. Joel's idea was to reuse this method in a process to convert a CDB where APEX is installed common, to a CDB where APEX is installed locally in PDBs.

The Big Idea

The high level steps to convert common APEX in a CDB to local APEX in PDBs are:

  1. Identify all PDBs in your CDB that have APEX instances
  2. In each PDB, identify all workspaces
  3. Run a script to export all artifacts of all workspaces identified in step 1 into zip files with installers
  4. Completely back up your CDB
  5. Run the APEX removal script to remove APEX from the CDB and all PDBs
  6. Install APEX locally in each PDB identified in step 1. (This may be a good opportunity to upgrade to the latest version!)
  7. Unzip each of your files from setp 3, and run the installer script from each, connected to the appropriate PDB

The Setup

I have a 12.1.0.2.0 CDB with two PDBs running APEX. One PDB has multiple workspaces, the other PDB has only one workspace. The CDB is the default APEX 4.2.5.0.00.08 that shipped with 12.1.0.2.0. The workspaces may have SQL scripts uploaded to SQL Workshop, SQL History and Saved SQL that we want to preserve. There are sample and packaged applications that we want to preserve. There are also custom built applications on objects in the schema associated with the workspaces. There are workspace images, and static files that we also want to preserve. I have already completely backed up my CDB.

HR Workspace in APEX1 figures

Applications in HR

SQL Script in SQL Workshop

SQL History in HR

Workspace image in HR

Static file in HR

FINANCE Workspace in APEX1 figures

Applications in FINANCE workspace

Saved SQL in SQL Workshop

MARKETING Workspace in APEX2 figures

Applications in MARKETING workspace

Workspace Images in MARKETING workspace

Saved SQL in SQL Workshop

The Hook

Identify all PDBs that have APEX instances

SQL> select p.pdb_name, r.version, r.status from sys.dba_pdbs p, sys.cdb_registry r where p.pdb_id = r.con_id and r.comp_id = 'APEX' order by 1;

PDB_NAME               VERSION                  STATUS
------------------------------ ------------------------------ -----------
APEX1                   4.2.5.00.08              VALID
APEX2                   4.2.5.00.08              VALID


Identify all workspaces

SQL> set num 40      
SQL> alter session set container=APEX1;

Session altered.

SQL> select workspace_id from apex_workspaces where workspace_id > 100;

                WORKSPACE_ID
----------------------------------------
            2222525459993115
            2336806112004961

SQL> alter session set container=APEX2;

Session altered.

SQL> select workspace_id from apex_workspaces where workspace_id > 100;

                WORKSPACE_ID
----------------------------------------
            2224405822867543

Run a script to export all artifacts

The original script that I wrote was in C shell and did the job, but my colleague Vlad Uvarov rewrote it in bash (apparently anyone under 40 no longer uses C shell) and vastly improved it. A slightly revised version of the script is listed below.

#!/bin/bash -e

THIS_SCRIPT=$(basename "$0" )
THIS_DIR=$(cd "$(dirname "$0" )" && pwd )
CURRENT_DIR=$(pwd )

# ===================================================================================
# Function that displays script usage instructions and terminates the script
#
f_usage () {
    cat <<EOF
Usage: $THIS_SCRIPT <JDBC connection string> <user> <password> <workspace ID>

Parameters:
    JDBC connection string    Database connection URL in JDBC format
    user                      Database username
    password                  Password of the database user
    workspace ID              ID of the workspace to be exported

Example:
    ./$THIS_SCRIPT //apex.example.com:1521/orcl apex_040200 secret 1234567890
EOF
    exit 1
}

# ===================================================================================
# First, check environment variables and input parameters
#
[ -z "$ORACLE_HOME" ] && echo "Error: ORACLE_HOME environment variable is not set." && exit 1

JAVA_EXE=$ORACLE_HOME/jdk/bin/java
[ ! -x "$JAVA_EXE" ] && echo "Error: Java executable not found ($JAVA_EXE)." && exit 1

OJDBC_JAR=$ORACLE_HOME/jdbc/lib/ojdbc6.jar
[ ! -f "$OJDBC_JAR" ] && echo "Error: Oracle JDBC library not found ($OJDBC_JAR)." && exit 1

APEXEXPORT=$ORACLE_HOME/apex/utilities/oracle/apex/APEXExport.class
[ ! -f "$THIS_DIR/$APEXEXPORT" -a ! -f "$APEXEXPORT" ] && echo "Error: Missing APEXExport utility ($APEXEXPORT)." && exit 1

(( $# < 4 )) && echo "Error: This script requires 4 parameters." && echo && f_usage

# ===================================================================================
# Step 1: Export APEX workspace, including applications and files
#
EXP_DIR=$(mktemp -d ${TMPDIR:-/tmp}/$(basename "$THIS_SCRIPT" .sh ).XXXXXX )
cd "$EXP_DIR"

cat <<EOF
Oracle Application Express workspace export

Using ORACLE_HOME=$ORACLE_HOME
EOF
$JAVA_EXE -version
echo

CP=$OJDBC_JAR:$THIS_DIR:$ORACLE_HOME/apex/utilities
if [ "$THIS_DIR" != "$CURRENT_DIR" ]; then
    CP=$CP:$CURRENT_DIR
fi

$JAVA_EXE -cp $CP oracle.apex.APEXExport -db "$1" -user "$2" -password "$3" -workspaceid "$4" -expWorkspace -expTeamdevdata
$JAVA_EXE -cp $CP oracle.apex.APEXExport -db "$1" -user "$2" -password "$3" -workspaceid "$4" -expFiles
$JAVA_EXE -cp $CP oracle.apex.APEXExport -db "$1" -user "$2" -password "$3" -workspaceid "$4" -expLocked -expSavedReports

# ===================================================================================
# Step 2: Create ZIP archive with a generated installation script and all export files
#
INSTALL_SCRIPT=install_${4}.sql
cat > "$INSTALL_SCRIPT" <<EOF
prompt
spool install_${4}.log

prompt Oracle Application Express workspace export
prompt Workspace ID $4 exported on $(date )

alter session set current_schema = APEX_050100;

@@w${4}.sql
EOF

for f in *.sql; do
    if [ "$f" != "w${4}.sql" -a "$f" != "$INSTALL_SCRIPT" ]; then
        echo "@@$f" >> "$INSTALL_SCRIPT"
    fi
done

cat >> "$INSTALL_SCRIPT" <<EOF

spool off
prompt
exit
EOF

echo
ZIP_FILE="$CURRENT_DIR/install_${4}_$(date +%Y%m%d ).zip"
rm -f "$ZIP_FILE"
zip -9 "$ZIP_FILE" *.sql

echo
echo "Generated $ZIP_FILE"
echo

# ===================================================================================
# Step 3: Clean up
#
cd "$CURRENT_DIR"
rm -rf "$EXP_DIR"
workspace_export.sh

One thing to note in the script above is that the install script being generated assumes that APEX 5.1 is installed in your target APEX instance. If that is not your plan, change the line "alter session set current_schema = APEX_050100" to match the APEX schema of the version you plan to install locally in your PDBs.

Also, for convenience, I am going to temporarily unlock APEX_040200 in my CDB and set a known password, and pass those necessary credentials to the workspace_export.sh script. You could really use any database user that has been granted the APEX_ADMINISTRATOR_ROLE.

SQL> alter user apex_040200 account unlock identified by oracle container=all;

User altered.


I saved the script above in a directory where I want all my zip files to end up. Now I will call the script passing each of the workspace IDs I identified in the previous step.

[jstraub@slc02otv cdbconvertapex]$ ./workspace_export.sh //localhost:1521/APEX1 APEX_040200 oracle 2222525459993115
Oracle Application Express workspace export

Using ORACLE_HOME=/scratch/jstraub/app/product/12.1.0/dbhome_1
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

Exporting Workspace 2222525459993115:'HR'
  Completed at Fri Feb 03 08:29:07 PST 2017
Exporting Application 100:'Sample Database Application'
  Completed at Fri Feb 03 08:29:16 PST 2017
Exporting Application 102:'Checklist Manager'
  Completed at Fri Feb 03 08:29:17 PST 2017
Exporting Application 103:'EMPLOYEES'
  Completed at Fri Feb 03 08:29:18 PST 2017
Exporting Application 104:'Sample Websheet Application - Big Cats'
  Completed at Fri Feb 03 08:29:18 PST 2017

  adding: f100.sql (deflated 88%)
  adding: f102.sql (deflated 89%)
  adding: f103.sql (deflated 93%)
  adding: files_2222525459993115.sql (deflated 55%)
  adding: install_2222525459993115.sql (deflated 39%)
  adding: w2222525459993115.sql (deflated 87%)
  adding: ws104.sql (deflated 64%)

Generated /scratch/jstraub/cdbconvertapex/install_2222525459993115_20170203.zip


[jstraub@slc02otv cdbconvertapex]$ ./workspace_export.sh //localhost:1521/APEX1 APEX_040200 oracle 2336806112004961
Oracle Application Express workspace export

Using ORACLE_HOME=/scratch/jstraub/app/product/12.1.0/dbhome_1
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

Exporting Workspace 2336806112004961:'FINANCE'
  Completed at Fri Feb 03 10:17:59 PST 2017
Exporting Application 101:'Sample Database Application'
  Completed at Fri Feb 03 10:18:13 PST 2017
Exporting Application 105:'Group Calendar'
  Completed at Fri Feb 03 10:18:14 PST 2017
Exporting Application 106:'QUOTES'
  Completed at Fri Feb 03 10:18:14 PST 2017

  adding: f101.sql (deflated 88%)
  adding: f105.sql (deflated 89%)
  adding: f106.sql (deflated 93%)
  adding: files_2336806112004961.sql (deflated 64%)
  adding: install_2336806112004961.sql (deflated 39%)
  adding: w2336806112004961.sql (deflated 82%)

Generated /scratch/jstraub/cdbconvertapex/install_2336806112004961_20170203.zip


And then for the other PDB, APEX2, we pass a different connect string:

[jstraub@slc02otv cdbconvertapex]$ ./workspace_export.sh //localhost:1521/APEX2 APEX_040200 oracle 2224405822867543
Oracle Application Express workspace export

Using ORACLE_HOME=/scratch/jstraub/app/product/12.1.0/dbhome_1
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

Exporting Workspace 2224405822867543:'MARKETING'
  Completed at Fri Feb 03 10:20:04 PST 2017
Exporting Application 100:'Sample Database Application'
  Completed at Fri Feb 03 10:20:13 PST 2017
Exporting Application 101:'Sample Websheet Application - AnyCo IT Department'
  Completed at Fri Feb 03 10:20:13 PST 2017
Exporting Application 102:'PROJECT_TASKS'
  Completed at Fri Feb 03 10:20:14 PST 2017
Exporting Application 103:'Decision Manager'
  Completed at Fri Feb 03 10:20:14 PST 2017
Exporting Application 104:'P-Track'
  Completed at Fri Feb 03 10:20:17 PST 2017

  adding: f100.sql (deflated 88%)
  adding: f102.sql (deflated 93%)
  adding: f103.sql (deflated 89%)
  adding: f104.sql (deflated 89%)
  adding: files_2224405822867543.sql (deflated 54%)
  adding: install_2224405822867543.sql (deflated 40%)
  adding: w2224405822867543.sql (deflated 82%)
  adding: ws101.sql (deflated 58%)

Generated /scratch/jstraub/cdbconvertapex/install_2224405822867543_20170203.zip

The long game

I have a complete backup of my database, and I have all the zip files that will reconstruct my workspaces. It is time to do the actual conversion.

Remove APEX from the CDB and all PDBs

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @apxremov_con.sql

PL/SQL procedure successfully completed.

Performing installation in multitenant container database in the background.
The installation progress is spooled into apxremov*_con*.log files.

Please wait...

catcon: ALL catcon-related output will be written to apxremov1_con_catcon_9311.lst
catcon: See apxremov1_con*.log files for output generated by scripts
catcon: See apxremov1_con_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to apxremov2_con_catcon_9723.lst
catcon: See apxremov2_con*.log files for output generated by scripts
catcon: See apxremov2_con_*.lst files for spool files, if any
catcon.pl: completed successfully


Installation completed. Log files for each container can be found in:

apxremov*_con*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

Install APEX 5.1 locally in the PDBs

SQL> alter session set container=APEX1;

Session altered.

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/


SQL> @apex_rest_config.sql

SQL> @apxchpwd.sql

SQL> @apex_epg_config.sql /scratch/jstraub/apx51 -- only if you are using EPG, very doubtful

SQL> alter session set container=APEX2;

Session altered.

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

 
SQL> @apex_rest_config.sql

SQL> @apxchpwd.sql

SQL> @apex_epg_config.sql /scratch/jstraub/apx51 -- only if you are using EPG, very doubtful

Recreate the workspaces using the generated zip files

First, I am again going to temporarily unlock and change the password for the APEX database user, in this case APEX_050100. Since now APEX_050100 is a local user to the PDB, I have to do it in both APEX1 and APEX2 PDBs.

SQL> alter session set container=APEX1;

Session altered.


SQL> alter user APEX_050100 account unlock identified by oracle;

User altered.


SQL> alter session set container=APEX2;

Session altered.

SQL> alter user APEX_050100 account unlock identified by oracle;

User altered.


Then I unzip each complete workspace export and run the corresponding install script.

[jstraub@slc02otv cdbconvertapex]$ mkdir hr
[jstraub@slc02otv cdbconvertapex]$ unzip install_2222525459993115_20170203.zip -d hr
[jstraub@slc02otv cdbconvertapex]$ cd hr
[jstraub@slc02otv hr]$ sqlplus apex_050100/oracle@localhost/APEX1

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 3 12:05:24 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @install_2222525459993115.sql  


[jstraub@slc02otv cdbconvertapex]$ mkdir finance
[jstraub@slc02otv cdbconvertapex]$ unzip install_2336806112004961_20170203.zip -d finance
[jstraub@slc02otv cdbconvertapex]$ cd finance
[jstraub@slc02otv finance]$ sqlplus apex_050100/oracle@localhost/APEX1

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 3 12:14:21 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Feb 03 2017 12:05:24 -08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @install_2336806112004961.sql 


[jstraub@slc02otv cdbconvertapex]$ mkdir marketing
[jstraub@slc02otv cdbconvertapex]$ unzip install_2224405822867543_20170203.zip -d marketing
[jstraub@slc02otv marketing]$ sqlplus apex_050100/oracle@localhost/APEX2

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 3 12:16:11 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @install_2224405822867543.sql


Now we login to each workspace and check that all the artifacts from the workspace migrated over.

HR workspace in APEX1 figures - APEX 5.1

Applications in HR Workspace

Static Files and Images in HR Workspace

SQL Script in SQL Workshop

SQL History in SQL Workshop

FINANCE workspace in APEX1 figures - APEX 5.1

Applications in FINANCE Workspace

Saved SQL in SQL Workshop

MARKETING workspace in APEX2 figures - APEX 5.1

Applications in MARKETING Workspace

Static Workspace Files in MARKETING

Saved SQL in SQL Workshop

One more thing

Actually, two more things. First, this process will not bring over "Template Applications" and "Public Themes." These features are not well known and rarely used, but you can easily determine if you have them by logging into internal administration, click on Manage Instance, and then click the Public Themes and Template Applications links. If there are applications in either area, simply export them, and them import them into internal administration after you recreate your workspaces.

Second, you may want to create another PDB, call it APEX51SEED, and install APEX into it. Put the PDB in read only mode, and then when you create a new PDB, you can create it from APEX51SEED and it will already have APEX 5.1 installed locally. In our case, we could not use this method to install APEX only once, because we needed the schemas that were in our APEX1 and APEX2 PDBs.

Conclusion

Having APEX installed locally in PDBs provides greater flexibility in your CDB, reduces upgrade time, and as well simplifies unplug/plug operations of your PDBs between CDBs. If you have a CDB where APEX is installed common, and you are using APEX in some or all of those PDBs, it is possible to do an in-place conversion using the method described above. Unfortunately, there is no magic SQL or script that can simplify this process. I hope you are successful if you go down this path, but be sure you have a complete backup of your CDB before trying. You can also use parts of this method to simply move a workspace from one APEX instance to another. That would require you to also move the schema, using datapump or another utility.

Friday, January 6, 2017

Ooops... I did it again.

APEX 5.1 is released! As Shaun points out at this forum post, the appendix of the Oracle Application Express 5.1 Installation Guide is missing instructions to downgrade back to 5.0, after a failed upgrade attempt.

My bad. I signed off on the Oracle Application Express Installation Guide as being complete for 5.1. Unfortunately, I did not pay enough attention to the appendix. We will get this corrected soon in a refreshed version of the Installation Guide, but in the meantime, below are the instructions we will be including.


Reverting to Release 5.0 in a non-CDB
To revert to a previous Oracle Application Express release 5.0 in a non-CDB:
  1. If you altered your images directory, revert it back to the release you want to revert to. See "Reverting the Images Directory."
  2. Change your working directory to apex/core in the 5.0 source.
  3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password 

On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
  1. Execute the following commands:
alter session set current_schema = SYS;   

@wwv_flow_val.sql 
@wwv_flow_val.plb 

begin
     dbms_utility.compile_schema('APEX_050000');
end;
/ 

set define '^'   
@validate_apex x x APEX_050000

begin
  for i in ( select owner, trigger_name
               from sys.dba_triggers
              where owner        =    'APEX_050000'
                and trigger_name like 'WWV_FLOW_UPGRADE_%'
              order by 1 )
  loop
    sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
    execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
  end loop;
end;
/

ALTER SESSION SET CURRENT_SCHEMA = APEX_050000; 
exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_050100','APEX_050000');
ALTER SESSION SET CURRENT_SCHEMA = SYS; 
drop context APEX$SESSION;
create context APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
declare
     l_apex_version varchar2(30); 
     l_schemas sys.dbms_registry.schema_list_t;
begin
     l_apex_version := apex_050000.wwv_flows_release;
     dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
     dbms_registry.downgraded('APEX',l_apex_version);
     select username
       bulk collect into l_schemas
       from all_users
      where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
      order by 1;
     sys.dbms_registry.update_schema_list('APEX', l_schemas);
     validate_apex;
end;
/
  1. See the next section, "Removing the Oracle Application Express Release 5.1 Schema."


Reverting to Release 5.0 in a CDB

To revert to a previous Oracle Application Express release 5.0 in a CDB:
  1. If you altered your images directory, revert it back to the release you want to revert to. See "Reverting the Images Directory."
  2. Change your working directory to apex/core in the 5.0 source.
  3. Create a new text file in that directory named apx50dgrd1.sql consisting of the following:
alter session set current_schema = SYS;   

@wwv_flow_val.sql 
@wwv_flow_val.plb 

begin
     dbms_utility.compile_schema('APEX_050000');
end;
/ 

set define '^'   
@validate_apex x x APEX_050000

begin
  for i in ( select owner, trigger_name
               from sys.dba_triggers
              where owner        =    'APEX_050000'
                and trigger_name like 'WWV_FLOW_UPGRADE_%'
              order by 1 )
  loop
    sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
    execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
  end loop;
end;
/

ALTER SESSION SET CURRENT_SCHEMA = APEX_050000; 
exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_050100','APEX_050000');
ALTER SESSION SET CURRENT_SCHEMA = SYS; 
drop context APEX$SESSION;
create context APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
declare
     l_apex_version varchar2(30); 
     l_schemas sys.dbms_registry.schema_list_t;
begin
     l_apex_version := apex_050000.wwv_flows_release;
     dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
     dbms_registry.downgraded('APEX',l_apex_version);
     select username
       bulk collect into l_schemas
       from all_users
      where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
      order by 1;
     sys.dbms_registry.update_schema_list('APEX', l_schemas);
     validate_apex;
end;
/
  1. Create a second new text file in that directory named apx50dgrd.sql consisting of the following:
set define '^'   
whenever sqlerror exit   
column :xe_home new_value OH_HOME NOPRINT 
variable xe_home varchar2(255)   
set serverout on 
begin 
-- get oracle_home     
sys.dbms_system.get_env('ORACLE_HOME',:xe_home);     
if length(:xe_home) = 0 then
    sys.dbms_output.put_line(lpad('-',80,'-'));         
    raise_application_error (-20001,'Oracle Home environment variable not set' );  
end if; 
end; 
/ 
whenever sqlerror continue   
set termout off 
select :xe_home from sys.dual; 
set termout on   
host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx50dgrd apx50dgrd1.sql
  1. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
  1. Execute the following commands:
@apx50dgrd.sql
  1. See the next section, "Removing the Oracle Application Express Release 5.1 Schema."
 

Friday, October 28, 2016

"ORA-20001: Application ID and current security group ID are not consistent" encountered during APEX Upgrade from 4.x to 5.x

Recently, our QA team at Oracle was testing database upgrades, and the following error appeared in the database upgrade log while the APEX component was being upgraded:

...Installing Theme 1

API Last Extended:20130101
Your Current Version:20130101
This import is compatible with version: 20130101
COMPATIBLE (You should be able to run this import without issues.)
--application/set_environment
APPLICATION 108801 - Theme: Simple Red
--application/delete_application
begin
*
ERROR at line 1:
ORA-20001: Application ID and current security group ID are not consistent.
ORA-06512: at "APEX_050000.WWV_FLOW_API", line 1419
ORA-06512: at "APEX_050000.WWV_FLOW_API", line 2106
ORA-06512: at line 2


A day later, our support organization reported that a customer ran into exactly the same error when they attempted an APEX upgrade from 4.2.5 to 5.0.4. I tried to reproduce the issue myself, but was unable to in a database I created using DBCA. I then created a database in our RDBMS development environment, and was able to reproduce this issue upgrading 4.2.5 to 5.0.4.

I noticed in the log of my failed installation a message that errors exist in the upgrade progress table.

SQL> select upgrade_error from apex_050000.wwv_flow_upgrade_progress where upgrade_error is not null;

UPGRADE_ERROR
--------------------------------------------------------------------------------
ORA-01000: maximum open cursors exceeded


I checked the open_cursors database parameter of the database where the upgrade failed, and found it was set to 50. I then checked the parameter in the database I created using DBCA, where the APEX upgrade was successful, and noted that it was set to 300. I restored the database of the failed upgrade, and issued the following command:

SQL> alter system set open_cursors=300 scope=both;

I retried the APEX upgrade to 5.0.4 and it completed successfully without errors. If you encounter the above error while doing an APEX upgrade, check the open_cursors parameter, and set it to at least 300.

Friday, April 17, 2015

Upgrading to Application Express 5.0 in Your Oracle Database Release 12.1 Multitenant Container Database (CDB)

Woo hoo! Oracle Application Express 5.0 has been released. Read all about it here.

The purpose of this blog post is to provide information on how to upgrade your 12.1 Multitenant Container Databases (CDBs) to APEX 5.0, and specifically ones where you have Oracle Application Express installed in CDB$ROOT (the root), and you are making use of Oracle Application Express 4.2.x in the PDBs in this CDB. There has been confusion on this topic, even from some of my colleagues at Oracle.

You can ignore this blog post if any of the following conditions are true in your situation:
  1. You are on a release lower than 12.1
  2. You are running 12.1, but it is a non container database (non-CDB)
  3. You are running a 12.1 CDB, but you have de-installed Oracle Application Express from the CDB, and you are running Oracle Application Express locally in all or some of your PDBs
Since you are still reading this post, this means you have a 12.1 CDB, where Oracle Application Express is installed in the root, and you are actively using APEX in your PDBs, and you wish to upgrade the root and all of your PDBs to APEX 5.0.

We have tried to make it painfully clear in multiple places in the 5.0 documentation set, and on our known issues page, that if you want to upgrade your 12.1 CDB to APEX 5.0, you must first download and install the database patch number 20618595 prior to upgrading to APEX 5.0.

Below is a step by step guide based on my experience in upgrading one of our 12.1.0.2.0 development environments to Oracle Application Express 5.0.

First, install the latest OPatch into your $ORACLE_HOME, by doing the following:
  1. Login to support.oracle.com
  2. Click Patches & Updates tab
  3. Simple search on patch number 6880880 and Platform Linux x86-64
  4. Download the patched titled "Patch patch of version 12.1.0.1.7 for Oracle software releases 12.1.0.x (APR 2015)"
  5. Execute the following OS commands
cd $ORACLE_HOME
mv OPatch OPatch_save
unzip /p6880880_121010_Linux-x86-64.zip
$ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.1.0.1.7

OPatch succeeded.


Next, you need to download and apply database patch 20618595.
  1. Login to support.oracle.com
  2. Click Patches & Updates tab
  3. Simple search on patch number 20618595
  4. Download either the 12.1.0.1.0 or 12.1.0.2.0 patch. I was on 12.1.0.2.0 so I downloaded that version.
  5. Execute the following OS commands
unzip /p20618595_121020_Linux-x86-64.zip
cd 20618595
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Ensure you see "Prereq "checkConflictAgainstOHWithDetail" passed." in the output. If so, proceed with shutting down all services for $ORACLE_HOME.

lsnrctl stop
sqlplus / as sysdba
shutdown immediate
exit

$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory

You should now see something in your terminal similar to:

Patch  20618595     : applied on Thu Apr 16 13:17:43 PDT 2015
Unique Patch ID:  18723077
   Created on 9 Apr 2015, 15:28:51 hrs PST8PDT
   Bugs fixed:
     20618595


If you do not, do not proceed. Get help from a DBA. Do not attempt to to upgrade to APEX 5.0. Do not pass Go! Do not collect $200.

Now, if you did see output in your terminal similar to the above, do the following.

lsnrctl start
sqlplus / as sysdba
startup
alter pluggable database all open;
exit

cd $ORACLE_HOME/OPatch
./datapatch

You should see output similar to:

    The following patches will be applied:
      20618595 ()

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_26531.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...done
SQL Patching tool complete on Thu Apr 16 13:49:29 2015


Again, if you do not, and you see any type of error, get help from a DBA and do not proceed until this patch is successfully applied to the database. Once the patch is successfully applied, simply connect to CDB$ROOT as SYSDBA, and run either apexins.sql or apxrtins.sql depending on your installation type. That's it. At the end of the installation your CDB and all PDBs will be successfully upgraded to APEX 5.0. I guarantee it!



Thursday, August 15, 2013

Accepting PayPal Payments with your Oracle Cloud Database Service

If you haven't signed up for an Oracle Database Service on the Oracle Cloud, you should do so right now.

I wrote a white paper called "Integrating Application Express with PayPal PaymentsPro" about six years ago and also made available a corresponding application for download on OTN. The application still works as long as you configure it properly by supplying the right substitutions in the application properties, most notably your PayPal API developer username, password and signature.

My colleague Rick Greenwald let me know about an Oracle Cloud customer that was trying to integrate PayPal in an application in their database service. They had seen the white paper I had written years ago but there are two requirements of the solution described in the paper that simply won't work on the cloud:
  1. You must be able to use UTL_HTTP with a wallet
  2. You must define a PL/SQL procedure and grant access to public on it
The reason for the public procedure is that the Classic PayPal API (formerly known as the NVP API) requires a redirect URL, which Pay Pal appends a token parameter and a PayerID parameter. The public procedure had two parameters of exactly the same name. It occurred to me that what might work on the Oracle Cloud would be to create a RESTful service that would accept those parameters with exactly the same names. With help from another colleague, Colm Divilly, I was able to create such a RESTful resource template with a get handler.

It also occurred to me that instead of using UTL_HTTP directly you could simply use the APEX_WEB_SERVICE API, specifically apex_web_service.make_rest_request. This package can be freely used on the Oracle Cloud, and it supports HTTPS calls through an instance wallet.
Below is a cookbook for modifying the existing PayPal integration sample application and using it on the Oracle Cloud. The application uses the Classic API, so look for a white paper by Rick Greenwald on how to use the updated PayPal RESTful API and OAuth2. (A much more modern solution.)
  1. Sign up and activate a database service on the Oracle Cloud
  2. Sign up for a PayPal developer account
  3. Follow the instructions to create sandbox accounts you can use to test transactions
  4. Login to your database service, click SQL Workshop, RESTful Services, and Create a new one with the following attributes (see figure 1):
a)      Name: PayPal Accept
b)      URI Template: pay_pal?token={token}&PayerID={payerid}
c)      Method: GET
d)     Source Type: PL/SQL
e)      Source:
begin
    paypal_accept(token => :token,
    PayerId => :payerid);
end;
  1. Download and unzip the original PayPal Integration sample application
  2. On the Application Builder Home Page, Choose Import, and use paypal_installer.sql, do not choose to install supporting objects, you need to make changes to them and will install them later.
  3. Click Edit Application, and then Supporting Objects
  4. Click Prerequisites and remove check next to Create Synonym  and click Apply Changes
  5. Click Installation Scripts, edit paypal_accept.sql and replace all of its contents with code listing 1 and click Apply Changes
  6. Edit paypal_api.plb and replace all of its contents with code listing 2 and click Apply Changes
  7. Click Install Supporting Objects, choose Yes and click Next and then Install
  8. Finally you need to change the substitution values in the application properties, click Edit Application and then Edit Application Properties
  9. Navigate to the Substitutions section and supply values for the following:
a)      API_USERNAME : This is the username you find at developer.paypal.com, click Applications, Sandbox accounts, and then Profile under the account that was initially created when you signed up for the developer account. (It should be Type Business. Upgrade it to Pro so that you can test the direct credit card payment feature.) On the profile window, click API credentials tab. Your username, password, and signature will be listed. Use this to fill out the substitutions of the same name.
b)      API_PASSWORD : See API credentials tab
c)      API_SIGNATURE : See API credentials tab
d)     RETURN_URL : https://database-.db.us1.oraclecloudapps.com/apex/pay_pal
e)      CANCEL_URL : https://database-.db.us1.oraclecloudapps.com/apex/
  1. Replace above with your actual identity domain and ensure you don’t have and leading or trailing spaces in the fields, and that you have pasted the entire API signature, it will span multiple lines. Click Apply Changes.
That’s it. The major changes were to create a RESTful service resource template and GET handler to handle the re-direct from PayPal, change the paypal_accept procedure to call f directly instead of redirecting to it, and to change do_post in paypal_api to use apex_web_service instead of UTL_HTTP directly.

Now you are ready to test the application.
  1. Click Run Application
  2. Login with your credentials, you may have to create an Application Express end user account first
  3. After you are done laughing at the hugely simplistic and ugly UI (hey, it was like 6 years ago!), choose a quantity, leave PayPal checked and click Check Out
  4. You will be redirected to the PayPal sandbox site. You need to login using a Personal sandbox account you should have created way back in step 3 of the cookbook.
  5. After you log in, you will see a review screen from PayPal, click continue
  6. Clicking continue should have caused you to redirect back to your RESTful services resource template, and you will now be back in your Application Express application on a confirm purchase page. Click Purchase.
  7. You should be on a Purchase Confirmed page now with transaction ID, order time and amount. Click Purchase More Tickets.
  8. Choose Payment Option Credit Card and Check Out
  9. Use the Account type you created when creating the Sandbox personal account. You get the account number and expire date from the Funding tab of the account profile. Enter the expire date in the form MMYYYY no spaces, no dashes.
  10. Click Pay Now and you should be back on the Purchase Confirmed page.
You are now ready to accept payments in your database service Oracle Cloud applications. You simply change the API_URL substitution in Application Properties to point to the production PayPal API URL.

Figure 1




Code Listing 1

create or replace procedure paypal_accept
  (token in varchar2,
   PayerId in varchar2)
as
begin
    for c1 in (select session_id, app_id,
                      page_id, payer_id_item
                 from paypal_session_map
                where session_token = token ) loop
        f(p=>c1.app_id||':'||c1.page_id||':'||c1.session_id||'::::'||c1.payer_id_item||':'||PayerId);
        exit;
    end loop;
end paypal_accept;
/

Code Listing 2

set define off

create or replace package body paypal_api
as
function do_post(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_method                in varchar2,
    p_parm01                in varchar2,
    p_parm02                in varchar2 default null,
    p_parm03                in varchar2 default null,
    p_parm04                in varchar2 default null,
    p_parm05                in varchar2 default null,
    p_parm06                in varchar2 default null,
    p_parm07                in varchar2 default null,
    p_parm08                in varchar2 default null,
    p_parm09                in varchar2 default null,
    p_parm10                in varchar2 default null )
    return varchar2
is
    l_response       varchar2(4000);
    l_post           varchar2(4000);
begin
    l_post := 'USER='||p_api_username||'&PWD='||p_api_password||'&SIGNATURE='||p_signature||
        '&'||p_parm01;
    if p_parm02 is not null then
        l_post := l_post||'&'||p_parm02;
    end if;
    if p_parm03 is not null then
        l_post := l_post||'&'||p_parm03;
    end if;
    if p_parm04 is not null then
        l_post := l_post||'&'||p_parm04;
    end if;
    if p_parm05 is not null then
        l_post := l_post||'&'||p_parm05;
    end if;
    if p_parm06 is not null then
        l_post := l_post||'&'||p_parm06;
    end if;
    if p_parm07 is not null then
        l_post := l_post||'&'||p_parm07;
    end if;
    if p_parm08 is not null then
        l_post := l_post||'&'||p_parm08;
    end if;
    if p_parm09 is not null then
        l_post := l_post||'&'||p_parm09;
    end if;
    if p_parm10 is not null then
        l_post := l_post||'&'||p_parm10;
    end if;
    l_post := l_post||'&VERSION=2.6&METHOD='||p_method;

    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded; charset=utf-8';
    l_response := apex_web_service.make_rest_request(
        p_url         => p_api_url,
        p_http_method => 'POST',
        p_body        => l_post);
    return wwv_flow_utilities.unescape_url(l_response);
end do_post;
function get_parameter(
    p_response              in varchar2,
    p_parameter             in varchar2 )
    return varchar2
is
    l_start     number;
    l_end       number;
begin
    if instr(p_response,p_parameter||'=') = 0 then
        return null;
    end if;
    l_start := instr(p_response,p_parameter||'=') + length(p_parameter) + 1;
    l_end := instr(p_response,'&',l_start);
    if l_end != 0 then
        return substr(p_response,l_start,l_end - l_start);
    else
        return substr(p_response,l_start);
    end if;
end get_parameter;
procedure set_express_checkout(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_session_id            in varchar2,
    p_return_page           in varchar2,
    p_payerid_item          in varchar2,
    p_redirect_url          in varchar2,
    p_return_url            in varchar2,
    p_cancel_url            in varchar2,
    p_amount                in varchar2,
    p_description           in varchar2,
    p_token_item            out varchar2 )
is
    l_response  varchar2(4000);
    l_token     varchar2(30);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'SetExpressCheckout',
        p_parm01        => 'RETURNURL='||p_return_url,
        p_parm02        => 'CANCELURL='||p_cancel_url,
        p_parm03        => 'AMT='||p_amount,
        p_parm04        => 'DESC='||p_description );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    l_token := replace(get_parameter(l_response,'TOKEN'),'-','%2D');
    p_token_item := l_token;
    delete from paypal_session_map where session_id = p_session_id;
insert into paypal_session_map values (p_session_id, apex_application.g_flow_id, p_return_page, p_payerid_item, replace(l_token,'%2D','-'));
    apex_application.g_unrecoverable_error := true;
    owa_util.redirect_url(p_redirect_url||l_token);
end set_express_checkout;
procedure get_express_checkout_details(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_token                 in varchar2,
    p_email_item            out varchar2,
    p_fname_item            out varchar2,
    p_mname_item            out varchar2,
    p_lname_item            out varchar2,
    p_shiptoname_item       out varchar2,
    p_shiptostreet_item     out varchar2,
    p_shiptostreet2_item    out varchar2,
    p_shiptocity_item       out varchar2,
    p_shiptocc_item         out varchar2,
    p_shiptozip_item        out varchar2,
    p_phonenum_item         out varchar2 )
is
    l_response  varchar2(4000);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'GetExpressCheckoutDetails',
        p_parm01        => 'TOKEN='||p_token );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    p_email_item := get_parameter(l_response,'EMAIL');
    p_fname_item := get_parameter(l_response,'FIRSTNAME');
    p_mname_item := get_parameter(l_response,'MIDDLENAME');
    p_lname_item := get_parameter(l_response,'LASTNAME');
    p_shiptoname_item := get_parameter(l_response,'SHIPTONAME');
    p_shiptostreet_item := get_parameter(l_response,'SHIPTOSTREET');
    p_shiptostreet2_item := get_parameter(l_response,'SHIPTOSTREET2');
    p_shiptocity_item := get_parameter(l_response,'SHIPTOCITY');
    p_shiptocc_item := get_parameter(l_response,'SHIPTOCOUNTRYCODE');
    p_shiptozip_item := get_parameter(l_response,'SHIPTOZIP');
    p_phonenum_item := get_parameter(l_response,'PHONENUM');
end get_express_checkout_details;
procedure do_express_checkout_payment(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_session_id            in varchar2,
    p_token                 in varchar2,
    p_payerid               in varchar2,
    p_amount                in varchar2,
    p_description           in varchar2 )
is
    l_response  varchar2(4000);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'DoExpressCheckoutPayment',
        p_parm01        => 'TOKEN='||p_token,
        p_parm02        => 'PAYMENTACTION=Sale',
        p_parm03        => 'AMT='||p_amount,
        p_parm04        => 'PAYERID='||p_payerid,
        p_parm05        => 'DESC='||p_description );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    insert into paypal_transactions values (p_session_id, p_token, get_parameter(l_response,'TRANSACTIONID'),
        get_parameter(l_response,'ORDERTIME'), get_parameter(l_response,'AMT'),get_parameter(l_response,'FEEAMT'),
        get_parameter(l_response,'SETTLEAMT'), get_parameter(l_response,'PAYMENTSTATUS'), get_parameter(l_response,'PENDINGREASON'),
        get_parameter(l_response,'REASONCODE'));
end do_express_checkout_payment;
procedure do_direct_payment(
    p_api_url               in varchar2,
    p_api_username          in varchar2,
    p_api_password          in varchar2,
    p_signature             in varchar2,
    p_wallet                in varchar2,
    p_wallet_pwd            in varchar2,
    p_session_id            in varchar2,
    p_ip_address            in varchar2,
    p_amount                in varchar2,
    p_creditcardtype        in varchar2,
    p_account               in varchar2,
    p_expire_date           in varchar2,
    p_first_name            in varchar2,
    p_last_name             in varchar2,
    p_description           in varchar2,
    p_tran_id_item          out varchar2 )
is
    l_response          varchar2(4000);
    l_transaction_id    varchar2(30);
begin
    l_response := do_post(
        p_api_url       => p_api_url,
        p_api_username  => p_api_username,
        p_api_password  => p_api_password,
        p_signature     => p_signature,
        p_wallet        => p_wallet,
        p_wallet_pwd    => p_wallet_pwd,
        p_method        => 'DoDirectPayment',
        p_parm01        => 'PAYMENTACTION=Sale',
        p_parm02        => 'IPADDRESS='||p_ip_address,
        p_parm03        => 'AMT='||p_amount,
        p_parm04        => 'CREDITCARDTYPE='||p_creditcardtype,
        p_parm05        => 'ACCT='||p_account,
        p_parm06        => 'EXPDATE='||p_expire_date,
        p_parm07        => 'FIRSTNAME='||p_first_name,
        p_parm08        => 'LASTNAME='||p_last_name,
        p_parm09        => 'DESC='||p_description );
    if get_parameter(l_response,'ACK') != 'Success' then
        raise_application_error(-20001,'Error: '||l_response);
    end if;
    l_transaction_id := get_parameter(l_response,'TRANSACTIONID');
    insert into paypal_transactions values (p_session_id, null, l_transaction_id,
        null, get_parameter(l_response,'AMT'), null, null, null, null, null);
    p_tran_id_item := l_transaction_id;
end do_direct_payment;
end paypal_api;
/