Posts

Showing posts from 2016

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

Image
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 = 'P

Password filling with KeePass2 - Ubuntu, Chrome and APEX

Image
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: @rimblas @sspendol for KeePass this works - custom field & keystroke sequence (need to focus the WS item first) — Jeffrey Kemp (@jeffreykemp) July 18, 2016 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

Preparing for CSS grids

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

Understanding variable scope

Image
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 pr

The making of my APEX competition dashboard map

Image
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

Accepting payments with Stripe in APEX, Part 3

Image
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 the

Accepting payments with Stripe in APEX, Part 2

Image
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

Accepting payments with Stripe in APEX, Part 1

Image
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: Include a form for customers to enter there card information The form gets submitted to Stripe's servers 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 . K

APEX Dashboard Competition for English Locale

Image
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. Your are invited to the #orclapex Dashboard Competition: https://t.co/u47QyBRtVN #letswreckthistogether pic.twitter.com/G2fLM9kzw1 — Tobias Arnhold (@tobias_arnhold) January 17, 2016 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 tha

Upgrading APEX on XE without the patch sets

Image
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: The Oracle Java database driver - typically $ORACLE_HOME/lib/ojdbc5.jar or $ORACLE_HOME/jdbc/lib/ojdbc5.jar 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/util

APEX Instance Admin Preferences Cheat Sheet

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