Wednesday, 12 October 2016

APEX URLs and HTML need not be in your SQL report queries

I've seen a few examples recently where people have been embedding URLs and HTML into their SQL queries. I tend to advocate keeping such things out of the query itself and using APEX functionality such as link target and html expressions (in SQL Query column attributes).

First example, a full page URL embedded into a column like so (conditionally based on some condition):

case when job = 'PRESIDENT'
then 'f?p='||:APP_ID||':2:'||:APP_SESSION||'::::P2_EMPNO:'||empno
else  'f?p='||:APP_ID||':3:'||:APP_SESSION||'::::P3_EMPNO:'||empno
end dest_url

Then on the column attributes, you make it a link by specifying the type as a link and the target as a URL specifying the target as #DEST_URL#




Taking a look at the URL, we can see there is only one bit changing, and that is the page number. So, we can avoid embedding the whole URL in our query by simply adding a column for our destination page:

case when job = 'PRESIDENT'
then 2
else  3
end dest_page

Then, when we specify our target, we use the page in this application option, and pass in the row value #DEST_PAGE#, like so:




Another example it is not uncommon to come across in samples is actually generating much more HTML in the query, for example:

'<button onclick="void(0);" type="button"'
        || ' class="t-Button t-Button--hot t-Button--small actionButton"'
        || ' data-empno="' || empno || '"'
        || ' data-job="' || job || '">'
        || ' <span class="t-Button-label">Click me!<span></button>'
    awesome_btn

After adding this, you also need to set security options to not escape special characters, in order for this to be rendered properly.

But again, it really is not necessary to add all this noise to your query. In a report, every column has a HTML Expression property where you can modify the column/row value to use custom HTML. So, from the previous example, we change the query just to add a new column - if you need a new field, use that, or just add an empty column to put your button in:


Then, in the column attributes, for HTML expression, it's just a matter of specifying that same HTML as from the previous example (less any PL/SQL and SQL bits - and using substitution strings where necessary).

<button 
    onclick="void(0);" 
    type="button" 
    class="t-Button t-Button--hot t-Button--small actionButton" 
    data-empno="#EMPNO#" 
    data-job="#JOB#"> 
    <span class="t-Button-label">
        Click me!(2)
    </span>
</button>

..

Obviously, this will come down to a personal preference on how you deal with such situations. I just prefer to remove unnecessary noise from my queries and use aspects of APEX that are available to me - rather than embedding as much as possible in the query source. 

Thursday, 28 July 2016

Password filling with KeePass2 - Ubuntu, Chrome and APEX

Overview

Whilst I was on holidays, I saw a tweet mentioning about an APEX enhancement request for browsers to handle passwords in the password manager better. In particular, a feature request was logged which you can see here: https://apex.oracle.com/pls/apex/f?p=55447:19:6596361215587:::19:P19_ID:74216354513552635850397961060065487893

Basically, you can have a multitude of passwords under the same domain, but with different workspaces, sometimes not handled greatly.

There was a bit of back and forth, mention of 1Password not having the issue - this is a non-free product and seemingly no (official) client for Linux. And finally, came the following tweet:


This key sequence is a shortcut that you can type so that if you're in the target window, the specified key stroke will be executed. In my limited testing, I only had success with this when specifying which window to map to (which is based off the window title). It's quite neat as well, but I went in a different direction.

So.... upon a bit more digging, I discovered there is a project called KeePassHttp, which exposes the database entries over a little HTTP server. And then another project, chromelPass, which interfaces with KeePassHttp to retrieve/update entries in the keypass database, direct from the browser.

Keep following on for steps on how to use this. Whilst the direction is based on Ubuntu, I'm sure you'll be able to pick up the difference on your preferred platform.

Installation

So, first, you need to install KeePass2. It is worth noting, there are two "keepass" packages in the repos, and you will want to get the one named "keepass2" - as that is the one KeePassHttp interacts with.



In addition, you will want to also have `mono-complete` installed. So at the terminal, run the command:

sudo apt-get install keepass2 mono-complete


KeePass2 has a plugin system, which is how exposing the passwords is done. So, before starting up KeePass, go and grab the plugin file from the GitHub project. You only need the KeePassHttp.plgx file. Once downloaded, it needs to be placed in the directory /usr/lib/keepass2/. So, go ahead and do that, or run the commands:

sudo wget -q https://github.com/pfn/keepasshttp/blob/master/KeePassHttp.plgx?raw=true -O /usr/lib/keepass2/KeePassHttp.plgx
sudo chmod 644 /usr/lib/keepass2/KeePassHttp.plgx

Set up

With that all done, start up KeePass2 and create your password database. Set up a master password and/or key file, and you can start adding entries. I suggest also to set a preference that by default the close button will minimise the application to the tray - this is because it needs to be running for the browser to access the entries. This is done through Tool -> Options, then go to the Interface tab and it's the first checkbox at the top.



Once, that's all done, it's time to go to Chrome and install the extension. This is done through the Chrome web store: https://chrome.google.com/webstore/detail/chromeipass/ompiailgknfdndiefoaoiligalphfdae. Once added, you'll find an icon added to your extension bar.



Initially, it will have a little red cross as it's not connected to KeePass. So click on the extension button then click Connect.



Then give the database a name and click Save.

Fetching entries in the browser

So, let's say I have manually added an entry for my Gumtree account as per:



Now, when I go to login on Gumtree, I'm prompted with the following:



note: This prompt can be disabled through the KeePassHttp options - under the advanced tab, select "Always allow access to entries" and "Always allow updating entries".



Now, after that. The passwords are auto filled without the prompt. If you have multiple accounts for the same domain, after you grant access again, the username field becomes a drop down list where you can choose the relevant account.

Saving entries to the database, from the browser

Now, to save passwords into KeePass directly from the browser. When you login, the extension button at the top of the browser will start flashing. If you click this you can opt to save a new entry in the database.



So click New and it will be added to the database under the "KeePassHttp" group, where you can re-organise is to one of your other groups.

It's also a good idea to disable Chrome from saving your passwords so you aren't prompted twice. There is done in the settings under the section "Passwords and Forms".


APEX usage

Now, with that all the way, how do we work with the workspace login screen? Go to KeePassHttp settings, and enable custom fields.




Now, to set up a couple of examples of workspace logins. In KeePass, I set up a new group for workspaces (APEX Workspaces), then created some entries as per:



Because we have 3 fields associated to a login (workspace, username and password), we need to record the additional field. So, in the Advanced tab, add a new String field. The name needs to be prefixed with KPH: <name>. <name> is not important for this case as it's the only additional field (otherwise, it would get populated in alphabetical order). So, I just named my field "KPH: Workspace". So, it would end up looking something like this:



The next step, which only need to be done once for each domain, is to specify the form fields, so KeePassHttp knows where to map the values to. If you click the extension icon, you will see a button at the top right, "Choose own credential fields for this page". Click that, and you will be able to specify the fields. All in all, you should see the following flow:







Clicking confirm, and we are ready to form fill. Either click the username where you can select the relevant account, or click the extension icon and you will see a list of all saved accounts for the domain which can be applied.


APEX app usage

One other problem I've found with the regular password manager is that because a bunch of apps are on the same path, with a different slightly query string parameter (f?p=appid:*), it's often treated as the same site and as such quite difficult to have multiple entries for different applications.

One way around is to design your entry names in such a fashion to make it clear what app the credentials map to. So, in my previous examples I prefixed with Workspace so I know they're for workspace logins, and with app logins, I name the title with app ID and name, giving me a selection like so (which makes it quite clear which password entry I'm using).


Wednesday, 27 April 2016

Preparing for CSS grids

I was just listening to "The Web Ahead" podcast where they were talking about the upcoming CSS grid system. If you can spare the time, go and take a listen (episode 114). Upcoming in the sense that there is a spec, but it's not yet mainstream - you can enable it through a flag in WebKit based browsers; use Firefox nightly (e.g I'm on Chrome 51 and it seems to be available). So you can't use it in your production applications just yet - soon'ish.

So, to get started (if using Chrome), you will want to enable experimental web platform features:



You can see the current working draft here - https://www.w3.org/TR/css-grid-1/

It's worth taking note of the motivation behind this. If you look for CSS layouts, you will see a bunch of solutions (probably involving floats) but using techniques not really designed for a full page layout.

Let's get started making a grid. The containing element should have a display property of either grid or inline-grid.

Without specifying any column widths, when placing elements on the same row, the column widths will be of equal widths.

As per:

<div id="gridContainer">
  <div id="col1">one</div>
  <div id="col2">two</div>
  <div id="col3">three</div>
</div>

<style type="text/css">
#gridContainer {
  display: grid;
}

#col1 {
  grid-column: 1;
  background-color:lightgreen;
}

#col2 {
  grid-column: 2;
  background-color:lightcyan;
}

#col3 {
  grid-column: 3;
  background-color:lightsalmon;
}
</style>


Output:



You may want span elements over multiple columns - in that case, imagine numbered lines for each column. Values are separated with a forward slash (/), and here you would specify the starting column  line and the ending column line. Alternatively, we can specify the number columns to span, prefixed with the span keyword

If we update the CSS to:

#gridContainer {
  display: grid;
}

#col1 {
  grid-row: 1;
  grid-column: 1 / 2;
  background-color:lightgreen;
}

#col2 {
  grid-row: 2;
  grid-column: 1 / 3;
  background-color:lightcyan;
}

#col3 {
  grid-row: 3;
  grid-column: 1 / 4;
  background-color:lightsalmon;
}

or

#gridContainer {
  display: grid;
}

#col1 {
  grid-row: 1;
  grid-column: 1 / span 1;
  background-color:lightgreen;
}

#col2 {
  grid-row: 2;
  grid-column: 1 / span 2;
  background-color:lightcyan;
}

#col3 {
  grid-row: 3;
  grid-column: 1 / span 3;
  background-color:lightsalmon;
}


We get:



We can make our styles more readable, by specifying the layout spec in the containing element, and giving each line a name. This is done in square parenthesis ([,]). In the container element, we define the layout spec with the property grid-template-columns and grid-template-rows. Here, you would specify the width / height of the columns / rows.

(note: If you don't declare the columns and rows, as per the previous example, the grid is implicitly created based on your grid data - an explicit layout makes the grid more concise)

So, with that applied, when specifying the column - we can use the name rather than the column index, as per:

#gridContainer {
  display: grid;
  grid-template-columns: [start] 100px [col2] 100px [col3] 100px [end];
  grid-template-rows: [top] auto [middle] auto [end];
  
}

#col1 {
  grid-row: top;
  grid-column: start / col2;
  background-color:lightgreen;
}

#col2 {
  grid-row: middle;
  grid-column: start / col3;
  background-color:lightcyan;
}

#col3 {
  grid-row: end;
  grid-column: start / end;
  background-color:lightsalmon;
}


If you rows and columns follow the same spec for the whole page, you can use the repeat function which accepts two parameters - 1. How many times to repeat; and 2. the column/row spec.


e.g.: grid-template-columns: repeat(4, [col] 100px)

In this example, when referring to the column we would use the index, or the name "col index"

Well, I just wanted to give a brief overview of this new technologies. There are countless examples on http://gridbyexample.com/ that you can check out/try out.

Monday, 11 April 2016

Understanding variable scope

In APEX, we have two primarily languages we would tend to work with:

  • PL/SQL
  • JavaScript
So, it's worth being aware of how variable scoping works in any program units you are developing.

If you don't already know it, JavaScript has function level scope, rather than block level scope. If you come from C-based language, and declare a variable inside a for loop, for instance, you would not expect that variable to live on outside of the loop.

This is not the behaviour of JavaScript, so let's give this a test to see:



When the variable i is declared, it is actually hoisted up to the to the top of the function. If you added a statement to the top of the function referencing i, i would have the value of undefined rather than a ReferenceError about using an undeclared variable.

No matter where a variable is declared in JavaScript, it is hoisted to the top of the containing function - something to be aware of. That's why you will often see JavaScript programs with all variables declared at the top of the function - and considered good practice.

If we introduced this into a language such as Java, we would get a compilation error for trying to use an unknown symbol - that is of course because the variable is only available in that particular block.

So, then, how does PL/SQL behave?

Well, for starters, PL/SQL has a bit more structure to it requiring variables to be declared in the declaration block - as opposed to having the ability to declare anywhere throughout the body of the program (aside from loops where the iterator can be declared inline - `for i in 1..100`).

In saying that, you can declare more variables inline by nesting additional blocks, and those nested blocks will naturally inherit properties declared above them. 



The same applies to named sub-units.



If we add a variable to a sub-unit of the same name, then we have a new variable to work with without over-writing the existing variable. 


What may be useful, is that we are able access the variable from the parent program unit by prefixing the name or label of the program unit with dot notation when accessing the variable. Be careful here though, if you have a label with the same name as a named program unit, it will use the closest match.



Sunday, 3 April 2016

The making of my APEX competition dashboard map

The other day, I submitted my entry into the APEX dashboard competition. It was interesting, as I had never done any projects with map visualisations so gave me the opportunity to learn a little on the topic - now that I've submitted my entry and my demo is set up, I think it's time to share what I learnt along the way.

First of all, GovHack (Australia) has this article on all things maps - http://govhack-toolkit.readthedocs.org/technical/making-maps/. So, having read that, I decided D3JS was the way forward. I managed to find a sample of a German map set up using this library (D3JS and topoJSON) - http://bl.ocks.org/oscar6echo/4423770. It uses a JSON file that contains all the data points to render all the data, but I had no clue how this data was obtained/generated just from that example - so I kept digging.

Which led me onto this great article, which pretty much takes you step by step on drawing the map components: https://bost.ocks.org/mike/map/ - and importantly it tells you a place to get the data, and make it the the correct format (JSON) that D3JS can use. This resource is Natural Earth which has a great many collection of geographic data - http://www.naturalearthdata.com.

The conversion process involves two tools:

  1. ogr2ogr - generating a GeoJSON file
  2. topojson - generating a topoJSON file
This guide seems to reference an OS X tool for getting the ogr2ogr tool, so I instead did a search in my package manager and found that tool to be a part of the gdal-bin package

$ apt-cache search ogr2ogr
gdal-bin - Geospatial Data Abstraction Library - Utility programs

So I installed that package, and installed topojson using npm as per the article.

Next, I went ahead and grabbed the data for the map I wanted to produce. I ended up grabbing the 1:10m scale, although in retrospect I need not have gone for such a highly detailed scale. Being only interested in states, I grabbed the "Admin 1 – States, Provinces" data - with the download link: http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_admin_1_states_provinces.zip

Back to the guide, it had these commands:

ogr2ogr \
  -f GeoJSON \
  -where "ADM0_A3 IN ('GBR', 'IRL')" \
  subunits.json \
  ne_10m_admin_0_map_subunits.shp

topojson \
  -o uk.json \
  --id-property SU_A3 \
  --properties name=NAME \
  -- \
  subunits.json \
  places.json

It was pretty straight forward to see what the inputs meant. On the ogr2ogr command
  • format as GeoJSON
  • Filter by some country codes
  • output file
  • input file
and topojson:

  • output file
  • set id property
  • set state name
  • pass input files
(the example actually uses to GeoJSON files merged into one, whereas I only went with the one - states)

All looked pretty clear, except it was obviously referencing fields in the shape file, and I wondered how I was supposed to know which fields to use - aside from of course following that guide.

A little bit of online research, and I found there was a package on Ubuntu that was able to read the data in a shape file - qgis

This package with two GUI programs:
  1. QGIS Desktop
  2. QGIS Browser
The latter being the one I needed to use. So I launched it and opened the shape file that I downloaded earlier (extracted from the zip - ne_10m_admin_1_states_provinces.shp). Scrolling through that file, I was able to find the "adm0_a3" field that was referenced in that file - as was name, but I couldn't see SU_A3. 



After a bit of analysis, I decided to use the field "adm1_code" as the id field, given me the following two commands to run:

ogr2ogr -f GeoJSON -where "ADM0_A3 = 'DEU'" states.json ne_10m_admin_1_states_provinces.shp

topojson -o de.json --id-property adm1_code --properties name=name -- states.json

Once all that was done, it was just a matter of prototyping the map. I started by doing this in a local file on my computer, before moving it into APEX and eventually a plugin in APEX. 

By default, the map is rendered quite small, so it needs to be scaled up to some figure. I just experimented a bit with that - and found applying a height to the svg element itself made it the right size for the screen. So my general code became:

var projection = d3.geo.mercator()
    .scale(500);

var path = d3.geo.path()
    .projection(projection);

var svg = d3.select("#germanMap")
    .attr("height", computedHeight);

d3.json(pluginFilePrefix + "de.json", function(error, de) {

    var states = topojson.feature(de, de.objects.states);

    svg.selectAll(".state")
        .data(states.features)
        .enter().append("path")
        .attr("class", function(d) { return d.id + " germanState"; })
        .attr("d", path)
        .on("click", germanMapRenderer.onClickState);
});

Here, I applied the adm1_code as a class to each state so I could apply the appropriate styles (for the purpose of this project, I wanted a heat map of the states based on population numbers) and also a class named germanState just to react on a click event on that class.

The full working example can be seen here: https://apex.oracle.com/pls/apex/f?p=94455
...and any code related to the project here: https://github.com/tschf/2016ADCEntry

Friday, 26 February 2016

Accepting payments with Stripe in APEX, Part 3

Hooking everything up in APEX

In part 1 of the series, we set up a form that would accept payment information. In part 2 we set up a PL/SQL API. Now, the final step is that we want to actually charge the card after the user submits the payment information, which is relatively straight forward.

First, we can set up a couple of substitution strings for our stripe secret and the wallet information




Next, add a new (hidden) item to the page that we can set the token into it (I called mine "P1_PMT_TOKEN"). Once that is on the page, we can modify our existing dynamic action (from part 1), so the code now looks like:

var handler = StripeCheckout.configure({
    key: 'pk_test_oXgwgmJbBmhzODwHxoKE8zAz',
    locale: 'auto',
    token: function(token) {
        $s('P1_PMT_TOKEN', token.id);
    }
});

handler.open({
    name: 'Demo Site',
    description: '2 widgets',
    currency: "aud",
    amount: 2000
});

Now, after they click the Pay button, we'll want to trigger the charge - where we need to call some PL/SQL. This can be done either from a page submission, or more than likely you will want to trigger this from a dynamic action. This is done easily with a change action on your P1_PMT_TOKEN page item.

begin

    stripe_api.set_secret(:STRIPE_TOKEN);
    
    stripe_api.charge_card (
        p_amount => 2000
      , p_source => :P1_PMT_TOKEN
      , p_description => 'Charged from a D.A'
      , p_wallet_path => :WALLET_PATH
      , p_wallet_password => :WALLET_PASSWORD
    );

end;

Now, if we submit a payment and look in our dashboard we will see the payment appear.



Finally, you may like to parse the response to return some more valuable information. Such as, the charge response includes a status field which according to the documentation can either be "succeeded" or "failed". APEX comes with APEX_JSON to work with JSON objects since version 5, so we can easily modify our API to return this (the response could also be an error object, which does NOT return a status field).

To return the status from our charge API call, we convert the procedure to a function so that it now looks like this

function charge_card (
    p_amount in NUMBER
  , p_currency in varchar2 default 'aud'
  , p_source in varchar2
  , p_description in varchar2
  , p_wallet_path in varchar2
  , p_wallet_password in varchar2
) 
return varchar2
as

    l_charge_resp CLOB;

begin
    
    l_charge_resp := 
        apex_web_service.make_rest_request(
            p_url => 'https://api.stripe.com/v1/charges'
          , p_http_method => 'POST'  
          , p_wallet_path => p_wallet_path
          , p_wallet_pwd => p_wallet_password
          , p_username => g_secret_token
          , p_parm_name => apex_util.string_to_table('amount:currency:source:description')
          , p_parm_value => apex_util.string_to_table(p_amount ||':'||p_currency||':'||p_source||':'||p_description)
        );
    
    apex_json.parse(l_charge_resp);
    
    return apex_json.get_varchar2(p_path => 'status');
    
end charge_card;

And updating our pay dynamic action to (with another page item to return the status into):

begin

    stripe_api.set_secret(:STRIPE_TOKEN);
    
    :P1_PMT_STATUS := 
        stripe_api.charge_card (
            p_amount => 2000
          , p_source => :P1_PMT_TOKEN
          , p_description => 'Charged from a D.A'
          , p_wallet_path => :WALLET_PATH
          , p_wallet_password => :WALLET_PASSWORD
        );

end;



Expanding from this going forward, you would more than likely want to return more than the status. You may want to also log the charges made locally (even though the info is all available from Stripe). This also needs better checking to cater for the situation of an error being returned instead of a Charge response.

Well, this series has just been one small component of what is possible with the Stripe API (I could be here all year if I set up examples of all components of the API). It makes it dead easy to integrate with - and if you are planning to offer same payment processing, I believe Stripe is a very good option!

Thursday, 25 February 2016

Accepting payments with Stripe in APEX, Part 2

Adding the PL/SQL API

In the previous part of this series, we hooked up the payment form so that users can enter their credit card information to accept payment. After this, the next thing you need to do is to charge the user for the goods or services. In the payment form, it's all on the client side using JavaScript; This time, we need to make some requests in PL/SQL. There is no client libraries for PL/SQL, so we need to set up some to make requests to the Stripe API.

All the Stripe API's have the base URL: https://api.stripe.com/ - so we first need to set up an ACL. Just whilst figuring everything out, we will assign this to the schema you are developing out of (down the track, when we switch to APEX_WEB_SERVICE, we can also assign the ACL to the APEX schema).

DECLARE

    l_filename varchar2(30) := 'stripe.com.xml';
    l_schema varchar2(20) := '[SCHEMA?]';

BEGIN

    BEGIN
        DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
            acl => l_filename
        );

    EXCEPTION WHEN OTHERS THEN
        NULL; -- ACL does not exist yet
    END;

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
          acl           => l_filename
        , description   => 'All requests to test utl_http'
        , principal     => l_schema -- schema name
        , is_grant      => TRUE
        , privilege     => 'connect'
    );

    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
          acl       => l_filename
        , principal => l_schema -- schema name
        , is_grant  => TRUE
        , privilege => 'resolve'
    );

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
          acl           => l_filename
        , host          => 'api.stripe.com'
        , lower_port    => 443
    );

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
        acl             => l_filename
      , host            => 'api.stripe.com'
      , lower_port      => 80
  );


END;
/

Now, with that, we can set up a generic procedure to output the contents of a HTTP request:

CREATE OR REPLACE PROCEDURE output_html (p_url IN VARCHAR2) AS
    l_http_request UTL_HTTP.req;
    l_http_response UTL_HTTP.resp;
    l_text VARCHAR2(32767);
BEGIN
    
    l_http_request := UTL_HTTP.begin_request(p_url);
    l_http_response := UTL_HTTP.get_response(l_http_request);

    BEGIN
        LOOP
            UTL_HTTP.read_text(l_http_response, l_text, 32766);
            DBMS_OUTPUT.put_line (l_text);
        END LOOP;
        
        EXCEPTION
            WHEN UTL_HTTP.end_of_body 
            THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    
    EXCEPTION
        WHEN OTHERS THEN
            UTL_HTTP.end_response(l_http_response);
            RAISE;
END output_html;
/

set serveroutput on


begin
    output_html('http://api.stripe.com');
end;
/

{
  "error": {
    "message" : "The Stripe API is only accessible over HTTPS.
Please see <https://stripe.com/docs> for more information.",
    "type":
"invalid_request_error"
  }
}


So, that error message is pretty self explanatory. The API only accepts requests over HTTPS. We haven't set up a wallet yet, so we will get an error if trying to do the same request over HTTPS. Being this example is based on setting up procedures on an XE instance - and XE does not come with the wallet manager, you will need to turn to the command line tool openSSL, which can achieve the same result. This process is documented on this blog: https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/.

To recap, grab the certificate chain from https://api.stripe.com (I saved mine as "api.stripe.com.cert"). This should be the Base64 encoded certificate chain. Then on the command line, run:

openssl pkcs12 -export -in api.stripe.com.cert -out ewallet.p12 -nokey

Once that is done, place the file in a suitable location. I placed mine to $ORACLE_HOME/wallets/stripe/. Now, if all worked well, we should be able to do a request over HTTPS. Just this time, with setting the wallet.

begin
    utl_http.set_Wallet(
        path => 'file:/u01/app/oracle/product/11.2.0/xe/wallets/stripe'
      , password => '[password for the wallet]'
    );
    output_html('https://api.stripe.com');
end;
/

{
  "error": {
    "type": "invalid_request_error",
    "message": "You did not
provide an API key. You need to provide your API key in the Authorization
header, using Bearer auth (e.g. 'Authorization: Bearer YOUR_SECRET_KEY'). See
https://stripe.com/docs/api#authentication for details, or we can help at
https://support.stripe.com/."
  }
}

I mentioned earlier about switching the ACL to the APEX schema. We could just as well started these tests with APEX_WEB_SERVICE and APEX schema on the ACL, but it's just the path I tend to take when testing HTTP requests out for the first time.

declare
    l_resp CLOB;
begin
    l_resp := apex_web_service.make_rest_request(
        p_url => 'https://api.stripe.com'
      , p_http_method => 'GET'
      , p_wallet_path => 'file:/u01/app/oracle/product/11.2.0/xe/wallets/stripe'
      , p_wallet_pwd => '[password for the wallet]'    
    );

    dbms_output.put_line(l_resp);
end;
/

Now that we have that all in place, we can flesh out a bit more of a PL/SQL API. First, reviewing the authentication documentation, you will see the BASIC authentication is supported where the username will be your secret token and the password left blank (NULL).

Following on from the previous post, the components you would most likely want to include in your request to complete the charge:
  • amount
  • currency
  • source
  • description (also sent to the user if email receipts are enabled)

With that then, we come up with a initial package:

create or replace package STRIPE_API
as

    procedure set_secret(
        p_secret in varchar2
    );

    procedure charge_card (
        p_amount in NUMBER
      , p_currency in varchar2 default 'aud'
      , p_source in varchar2
      , p_description in varchar2
    );

end STRIPE_API;
/

The basic implementation of charge_card looks like:

procedure charge_card (
    p_amount in NUMBER
  , p_currency in varchar2 default 'aud'
  , p_source in varchar2
  , p_description in varchar2
) 
as

    l_charge_resp CLOB;

begin
    
    l_charge_resp := 
        apex_web_service.make_rest_request(
            p_url => 'https://api.stripe.com/v1/charges'
          , p_http_method => 'POST'  
          , p_wallet_path => 'file:/u01/app/oracle/product/11.2.0/xe/wallets/stripe'
          , p_wallet_pwd => '[password for the wallet]' 
          , p_username => g_secret_token
          , p_parm_name => apex_util.string_to_table('amount:currency:source:description')
          , p_parm_value => apex_util.string_to_table(p_amount ||':'||p_currency||':'||p_source||':'||p_description)
        );
    
    dbms_output.put_line(l_charge_resp);    
    
end charge_card;
/

I've chucked this code at: https://github.com/tschf/pafs, so refer to that for the most up to date implementation.

Note: the above is just doing a dbms_output.put_line so we can see the response object whilst developing the API. Down the track you will likely want to modify this to return some information from the response object, to give the user meaningful feedback that it succeeded/failed.

So, then we can create a charge with the following calls:

begin

    stripe_api.set_secret('[secret removed]');
    
    stripe_api.charge_card(
        p_amount => 300
      , p_source => '[token_removed]'
      , p_description => 'Test the API'
    );

end;

Now, if we look in our dashboard in Stripe, under Payments, will we see that the charge has succeeded.


Stay tuned for the 3rd and final part - Hooking this API up to APEX.

Thursday, 11 February 2016

Accepting payments with Stripe in APEX, Part 1

I've just been investigating a payment gateway for an APEX application. I already knew about the recent startup Stripe, and their fees seemed just as good (if not better) than the other well-known contenders - I figured there would be a good place to start.

Now, I haven't actually worked with the others, but so far, I'm very impressed with the system/API available. From my little bit of research, all the card information will be stored on Stripe's servers, so we need not deal with storing and encrypting customers card information. With that, we are left with this general workflow:

  1. Include a form for customers to enter there card information
  2. The form gets submitted to Stripe's servers
  3. Returned is a token. We use this to then complete the charge

For this first part, I'll be focusing on the form. In particular, Stripe provides a form for us that we can re-use. Check out the documentation on the form here - https://stripe.com/docs/tutorials/checkout. Keep in mind, you can just as easily develop your own form if you have your own design in mind.

The basic example (for a form) they give on the docs is:

<form action="" method="POST">
  <script
    src="https://checkout.stripe.com/checkout.js" class="stripe-button"
    data-key="[redacted]"
    data-amount="2000"
    data-name="Demo Site"
    data-description="2 widgets"
    data-image=""
    data-locale="auto">
  </script>
</form>

Side note: In your account, you can retrieve your API tokens (and test tokens) from: https://dashboard.stripe.com/account/apikeys

So, if you add that to a static region on your page, you will see a nice checkout button and form:




What would then happen after submitting the payment information, is the form would get submitted, with some additional parameters being sent in the POST request. Being on APEX, this won't do since the whole page is wrapped around a form - resulting in this being an embedded form (unsupported).

Looking at the documentation some more, they have a more advanced example giving us more control over what happens once the Pay button is selected.

We still need to leave a reference to checkout.js on our page, so for now this can live in our region where the pay button will live. The next part will be to add a regular APEX button to our region - with the action being Defined by a Dynamic Action.

Then, we need a dynamic action for when the button is clicked - with the true action being to execute a block of JavaScript code (the handler could be set up on page load, but just chucked it in one spot for simplicity)

var handler = StripeCheckout.configure({
    key: '[redacted]',
    locale: 'auto',
    token: function(token) {
        console.log(token);
    }
});

handler.open({
    name: 'Demo Site',
    description: '2 widgets',
    currency: "aud",
    amount: 2000
});


So, now with our advanced implementation, we are left with much the same form:




(This is just test card information supplied with by Stripe - see: https://stripe.com/docs/testing)

So, when the user submits the payment information, the token function will be called. With that we are returned with the following information:


Further, if we look in our dashboard on Stripe for the account, we should see this activity logged:



So, with that information, you would then want to initiate a request to a PL/SQL process to make the actual charge to the customers card. A post for another day.

Saturday, 23 January 2016

APEX Dashboard Competition for English Locale

The other day Tobias announced a new competition where you can win Apple products, and what you need to do is build a dashboard in Oracle Application Express using some supplied data.


I'm about to go on a little trip so I thought I'd load up this data into my local VM so that if I had some down time, I could have a play around with it (not yet sure if I'll enter!). The only problem was, I started receiving errors during the data import, as per:




As from that screenshot, you will see the number is using a decimal point as a comma (as opposed to what I am used to, and what the session is expecting, a period). To be honest, I didn't even realise different locale's used different decimal marks - but after turning to Wikipedia, I see there are a large number of nations that do: https://en.wikipedia.org/wiki/Decimal_mark

After a bit of research, I discovered that you can change the session parameter NLS_NUMERIC_CHARACTERS to support the different locale's. With that I came up with the script:

@/home/trent/Downloads/APEX_DASHBOARD_COMPETITION_DATEN/DATEN_FINAL/DDL_EXPORT.sql
alter session set NLS_NUMERIC_CHARACTERS=',.';
@/home/trent/Downloads/APEX_DASHBOARD_COMPETITION_DATEN/DATEN_FINAL/DML_EXPORT.sql
alter session set NLS_NUMERIC_CHARACTERS='.,';

With those session modifications the data should (it did for me) load seamlessly.

The next task, was that all the data is in German. It's not a huge deal, as it's the numbers that matter. But, I feel it a lot easier to work with data I can at least interpret! So, I wanted to go ahead and come up with a way to tweak this so I can understand the information better. With a bit of google translate help, I made a series of CSV files to create a mapping. Check out this little project: https://github.com/tschf/dashtrans. In the raw folder is a series of tabular data with the word as it is in the data (original) and an English translation (not all perfect!). Once you have the project files, from the same directory, you can apply the updates as per the CSV files with:

sql dash_comp/dash_comp@//192.168.1.15/xe @to_en

Hope it's useful!

Wednesday, 13 January 2016

Upgrading APEX on XE without the patch sets

To get APEX patch sets requires a paid support account to get access to the patch sets for minor updates - so upgrading APEX on our XE database installations can be challenging for those of us without a paid support account.

So effectively, what you need to do is remove APEX, and re-install from scratch. APEX also comes with a Java program to perform backups, where you can for example export invididual workspaces, export all workspaces, export invidual applications, export applications by workspace id, or export all applications. I grabbed the idea from this apexbackup project on GitHub: https://github.com/OraOpenSource/apexbackup. Basically, you need to set your CLASSPATH to point to:

  1. The Oracle Java database driver - typically $ORACLE_HOME/lib/ojdbc5.jar or $ORACLE_HOME/jdbc/lib/ojdbc5.jar
  2. The utilities folder in the APEX installation files

After that has been set,  you can run the program. e.g: 

export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/apex/utilities/
java oracle.apex.APEXExport

With the requires arguments depending on which operation you wish to perform. If you run it without any arguments, output to the screen will be a list of all the options:

Usage APEXExport -db  -user  -password  -applicationid  -workspaceid -instance -expWorkspace -expMinimal -expFiles -skipExportDate -expPubReports -expSavedReports -expIRNotif -expTranslations -expTeamdevdata -expFeedback -deploymentSystem -expFeedbackSince -expOriginalIds -debug  
    -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
    
    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 
    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

So with that, to upgrade our APEX instances on minor releases (patches) we can script up a program to export all our workspaces, and restore them after a fresh installation - by getting the latest, full download from OTN.

I started working on a new project over the weekend to do exactly this - https://github.com/tschf/daxul. It's still in early stages, but to the point it's working for me. Basically, the arguments it accepts (current order being important) is:
  • Path to the apex installation files
  • Database host
  • Database port
  • Database sid
  • System password
  • Sys password
  • Path to where the images are stored
Once you grab the project on your server, you would simply run it like so:

sudo -E ./daxul.sh /home/trent/apex localhost 1521 xe oracle oracle /ords/apex_images

Using the same order of arguments described above.

That goes without saying, this should be run as a user with permissions to write to the destination image directory, and they should have $ORACLE_HOME set. And you will likely want to take a separate backup of all your applications in case anything breaks in the script after the APEX removal - especially in these early stages of the project (I haven't had any other testers so far).

My script is also taking a backup of the instance configuration to restore it post upgrade (though, I haven't got everything working so far due to apex_instance_admin.set_parameter seemingly not being supported for all parameters). The main thing that will be missing post upgrade is all the users previously set up in the INTERNAL workspace. I have my machine installed in VirtualBox, so I took a snapshot beforehand should anything fall over, so I can easily restore it.

Some immediate enhancements requires are to support named arguments so order isn't important, along with default values if any are omitted; as well as adding some additional ones (namely, for the values passed to the APEX install script).

Even if you don't use my specific script/project, it can be a good example case to base off to develop something for your own business.

Have I left any other crucial bits off my script - that would need backup and restoration?

Here's a little video of my running the script:


Monday, 11 January 2016

APEX Instance Admin Preferences Cheat Sheet

APEX comes with an API - APEX_INSTANCE_ADMIN - where you can both get and set instance preferences. There is a list of available properties on the documentation, however I found that not all preferences were documented there. For example, in Feature Configuration, there are some preferences surrounding packaged applications:
















Looking at the documentation, https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_instance.htm#CHDFEICJ, you may find it difficult locating these.

Then, there other properties in there that I don't think are relevant any more (..but not an expert on the subject, so may be wrong). Lets take the example of PASSWORD_HISTORY_DAYS - this states "Defines the maximum number of days a developer or administrator account password may be used before the account expires. The default value is 45 days.". We can locate the property it's talking about under Security - Authentication Control - Development Environment Settings.





If we attempt to query this property name, you will find nothing is returned.

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production



SQL> set serveroutput on

SQL> begin

  2    dbms_output.put_line('Value: ' || apex_instance_admin.get_parameter('PASSWORD_HISTORY_DAYS'));

  3  end;

  4  /

Value:



PL/SQL procedure successfully completed.



SQL>


Digging into this, it looks to me this relates to the parameter ACCOUNT_LIFETIME_DAYS.

So anyway, with all this said, here is what I've come up with a table mapping each field to a parameter name - some on the docs, others not.

Text Version:

Feature Configuration

Application DevelopmentParameter Name
Allow PL/SQL Program Unit EditingPLSQL_EDITING
Create demonstration objects in new workspacesWORKSPACE_PROVISION_DEMO_OBJECTS
Create websheet objects in new workspacesWORKSPACE_WEBSHEET_OBJECTS
Enable SQL Access in WebsheetsWEBSHEET_SQL_ACCESS
Packaged Application install Options
Allow HTTP Header Variable authenticationPKG_APP_AUTH_ALLOW_HHEAD
Allow LDAP Directory authenticationPKG_APP_AUTH_ALLOW_LDAP
Allow Oracle Applicaion Server Single Sign-On authenticationPKG_APP_AUTH_ALLOW_SSO
SQL Workshop
SQL Commands Maximum Inactivity in minutesSQL_COMMAND_MAX_INACTIVITY
SQL Scripts Maximum Script Output Size in bytesSQL_SCRIPT_MAX_OUTPUT_SIZE
SQL Scripts Maximum Workspace Output Size in bytesWORKSPACE_MAX_OUTPUT_SIZE
SQL Script Maximum Script Size in bytesMAX_SCRIPT_SIZE
Enable transactional SQL CommandsENABLE_TRANSACTIONAL_SQL
Enable RESTful ServicesRESTFUL_SERVICES_ENABLED
Monitoring
Enable Database MonitoringALLOW_DB_MONITOR
Application Activity LoggingAPPLICATION_ACTIVITY_LOGGING
Enable Application TracingTRACING_ENABLED
Workspace Administration
Enable Service RequestsSERVICE_REQUESTS_ENABLED
Team Development
Enable Team Developer's File RepositoryWORKSPACE_TEAM_DEV_FILES_YN
Maximum File Size (in MB)WORKSPACE_TEAM_DEV_FS_LIMIT


Security

SecurityParameter Name
Set Workspace CookieWORKSPACE_NAME_USER_COOKIE
Disable Adminstrator LoginDISABLE_ADMIN_LOGIN
Disable Workspace loginDISABLE_WORKSPACE_LOGIN
Allow Public File UploadALLOW_PUBLIC_FILE_UPLOAD
Restrict Access by IP AddressRESTRICT_IP_RANGE
Instance ProxyINSTANCE_PROXY
Checksum Hash FunctionCHECKSUM_HASH_FUNCTION
Rejoin SessionsREJOIN_EXISTING_SESSIONS
Unhandled ErrorsHTTP_ERROR_STATUS_ON_ERROR_PAGE_ENABLED
HTTP Protocol
Require HTTPSREQUIRE_HTTPS
Require Outbound HTTPSREQUIRE_OUT_HTTPS
HTTP Response HeadersHTTP_RESPONSE_HEADERS
RESTful Access
Allow RESTful AccessALLOW_REST
Session Timeout
Maximum Session Length in SecondsMAX_SESSION_LENGTH_SEC
Maximum Session Idle Time in SecondsMAX_SESSION_IDLE_SEC
Workspace Isolation
Allow HostnamesALLOW_HOSTNAMES
Resource Consumer GroupRM_CONSUMER_GROUP
Maximum Concurrent Workspace RequestsQOS_MAX_WORKSPACE_REQUESTS
Maximum Concurrent Session RequestsQOS_MAX_SESSION_REQUESTS
Concurrent Session Requests Kill TimeoutQOS_MAX_SESSION_KILL_TIMEOUT
Maximum Size of FIles in WorkspaceWORKSPACE_MAX_FILE_BYTES
Region and Web Service Excluded Domains
Domain Must Not ContainBAD_URLS
Authentication Control
General Settings
Delay after failed login attempts in SecondsLOGIN_THROTTLE_DELAY
Method for computing the DelayLOGIN_THROTTLE_METHODS
Inbound Proxy ServersINBOUND_PROXIES
Single Sign-On Logout URLSSO_LOGOUT_URL
Development Environment Settings
Username validation expressionUSERNAME_VALIDATION
Require User Account Expiration and LockingEXPIRE_FND_USER_ACCOUNTS
Maximum Login Failures AllowedMAX_LOGIN_FAILURES
Account Password Lifetime (days)ACCOUNT_LIFETIME_DAYS
Current Workspace Authentication SchemAPEX_BUILDER_AUTHENTICATION
Password Policy
Password Hash FunctionPASSWORD_HASH_FUNCTION
Minimum Password LengthPASSWORD_MIN_LENGTH
Minimum Password DifferencesPASSWORD_NEW_DIFFERS_BY
Must Contain At Least One Alphabetic CharacterPASSWORD_ONE_ALPHA
Must Contain At Least One Numeric CharacterPASSWORD_ONE_NUMERIC
Must COntain At Least One Punctuation CharacterPASSWORD_ONE_PUNCTUATION
Must Contain At Least One Upper Case CHaracterPASSWORD_ONE_UPPER_CASE
Must Contain At Least One Lower Case CharacterPASSWORD_ONE_LOWER_CASE
Must Not Contain UsernamePASSWORD_NOT_LIKE_USERNAME
Must Not Contain Workspace NamePASSWORD_NOT_LIKE_WS_NAME
Must Not ContainPASSWORD_NOT_LIKE_WORDS
Alphabetic CharactersPASSWORD_ALPHA_CHARACTERS
Punctuation CharactersPASSWORD_PUNCTUATION_CHARACTERS
Service Administrator Poassword PolicySTRONG_SITE_ADMIN_PASSWORD

Instance Settings

Self ServiceParameter Name
Provisioning StatusSERVICE_REQUEST_FLOW
Require Verification CodeREQUIRE_VERIFICATION_CODE
Notification Email AddressNOTIFICATION_EMAIL
Email Provisioning
Email ProvisioningDISABLE_WS_PROV
MessageDISABLE_WS_MSG
Storage
Require New SchemaREQ_NEW_SCHEMA
Auto Extent TablespacesAUTOEXTEND_TABLESPACES
Bigfile TablespacesBIGFILE_TABLESPACES_ENABLED
Encrypted TablespacesENCRYPTED_TABLESPACES_ENABLED
Delete Uploaded Files After (days)DELETE_UPLOADED_FILES_AFTER_DAYS
Email
Application Express Instance URLEMAIL_INSTANCE_URL
Application Express Images URLEMAIL_IMAGES_URL
SMTP Host AddressSMTP_HOST_ADDRESS
SMTP Host PortSMTP_HOST_PORT
SMTP Authentication usernameSMTP_USERNAME
SMTP Authentication passwordSMTP_PASSWORD
Use SSL/TLSSMTP_TLS_MODE
Default Email From AddressSMTP_FROM
Maximum Emails per WorkspaceWORKSPACE_EMAIL_MAXIMUM
Wallet
Wallet PathWALLET_PATH
Wallet PasswordWALLET_PWD
Report Printing
Print ServerPRINT_BIB_LICENSED
Printer Server ProtocolPRINT_SVR_PROTOCOL
Print Server Host AddressPRINT_SVR_HOST
Print Server PortPRINT_SVR_PORT
Print Server ScriptPRINT_SVR_SCRIPT
Print TimeoutPRINT_SVR_TIMEOUT
Help
Help URLSYSTEM_HELP_URL
Application ID Range
ID MinimumAPPLICATION_ID_MIN
ID MaximumAPPLICATION_ID_MAX

Workspace Purge Settings:

Workspace Purge SettingsParameter Name
EnabledPURGE_ENABLED
LanguagePURGE_LANG
Purge Adminsitration Email AddressPURGE_ADMIN_EMAIL
Send Summary Email toPURGE_SUMMARY_EMAIL_TO
Days until purgePURGE_DAYS_TO_PURGE
Reminder days in advancePURGE_REMINDER_DAYS_IN_ADVANCE
Days inactivePURGE_DAYS_INACTIVE
Grace period (days)PURGE_GRACE_PERIOD_DAYS
Maximum execution time (hours)PURGE_MAX_RUN_HOURS
Maximum number of workspacesPURGE_MAX_WORKSPACES
Maximum number of emailsPURGE_MAX_EMAILS

Image:

Feature Configuration:


Security:




Instance Settings:













































































Workspace purge settings: