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 for standalone databases. I will update this post with the CDB instructions later.


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;
/

Tuesday, July 9, 2013

Application Express in Oracle Database 12c Now on OTN

So there have been a few questions about Oracle Application Express and Oracle Database 12c, especially about the new multitenant architecture. Thanks to David Peake, there is now a brand new page on OTN providing more information, collateral, and known issues. You can find it by clicking on the big
button, and then on the Application Express in Oracle Database 12c heading.

Monday, July 8, 2013

Application Express 4.2.2.00.11 for Oracle Database 12c Container Database

I wrote in my previous post that you cannot patch the Application Express 4.2 version in a Oracle Database 12c container database to 4.2.2.00.11, however as of a little while ago, you can now remove Application Express 4.2 from your container database and install Application Express 4.2.2.00.11 as a new install. The apex_4.2.2.zip and apex_4.2.2_en.zip files have been updated on OTN to include the appropriate _con.sql scripts used for installing into a container database.

To install Application Express 4.2.2.00.11 into a container database:

  1. Download or re-download either apex_4.2.2.zip or apex_4.2.2_en.zip from OTN. Old distributions will not work and will not have the proper scripts.
  2. Unzip apex_4.2.2[_en].zip and cd to the apex directory.
  3. Connect as SYS as SYSDBA to the CDB$ROOT container of your container database.
  4. Run apxremov_con.sql as described here the only difference is you will be using apxremov_con.sql from this distribution and not from the $ORACLE_HOME/apex directory.
  5. Exit SQL*Plus, and reconnect as SYS as SYSDBA to the CDB$ROOT container.
  6. Run apexins_con.sql or apxrtins_con.sql from the updated 4.2.2 distribution depending on the type of install you want, similarly described here.
  7. If you want to configure EPG as your web listener, do the following additional steps:
  8. Ensure you are still connected to the CDB$ROOT container as SYS and run apex_epg_config_con.sql passing the path to where you unzipped apex_4.2.2[_en].zip.
  9. Unlock the ANONYMOUS account.
  10. Change your container to the PDB that you want to be an Application Express instance and then run apxconf.sql as described here. Use apxconf.sql from the 4.2.2 distribution, not the one in $ORACLE_HOME/apex.
Note that until database patch 16946990 is available you will not be able to install Application Express locally in a PDB as described here. The instructions above however will allow you to install Application Express 4.2.2.00.11 as a common option in the container database.

Wednesday, June 26, 2013

Oracle Database 12c Multitenant Option and Application Express

As you probably know by now, Oracle Database 12c was made generally available on Tuesday, June 25th. We will be publishing a detailed page about Application Express in 12c on our OTN page but I wanted to make a couple of points about Application Express in the new multitenant architecture. You should also review this chapter if you plan on installing an Oracle Database 12c as a container database.

First, if you create a container database in 12c, Application Express will be installed common in the root container. You should never connect directly to a PDB, for any reason, and run a standard Application Express install script or patch script. About the only scripts that should ever be run directly against a PDB are apxconf.sql and apxchpwd.sql, both of which pertain only to configuring a single instance of Application Express.

Second, we have not released a new version of Application Express since Oracle Database 12c was released two days ago, so there is no software available to upgrade or patch the version of Application Express (4.2) that comes in the container database. The next version or patch of Application Express will contain the appropriate scripts to update a container database, so you are stuck with the version shipped with 12c until then. This seems to be contradicted by section 1.3,
"Upgrading to Application Express Release 4.2.1 or Later" in the "Oracle® Application Express Installation Guide Release 4.2 for Oracle Database 12c." It should be noted that the section currently only applies to non-container 12c databases.

My comments above apply only to container databases. Non-container 12c databases behave just like prior versions of Oracle, and you can patch Application Express to 4.2.1, or 4.2.2 as you normally would.