John's Blog

Materialized views (MV's) - the developers go-to tool for poor performing queries. :) Of course that isn't really their only purpose, and creating one just because a query isn't performing too well should be the last resort after fully investigating the root cause of the problem. We can however use MV's as a tool in optimizing our query performance...

I've seen this question come up a few times on the Oracle Community Forums recently, so I thought I'd write a quick blog post about it. The question is along the lines of "How can I schedule a concurrent program to run every X minutes between Y o'clock and Z o'clock each day?".For this example, we'll assume we have a program "Active Users" that we ...

Just over three years ago I wrote a blog post on Monitoring Oracle Data Integrator (ODI ) Load Plan Executions with BI Apps where I demonstrated a little APEX application I had developed to bypass some of the pain and clunkiness of doing the same in the ODI console. I had a few people ask me if they could have a copy of the app, however at the time...

A while back I wrote a blog post on The Power of R, and I want to extend that a little now by looking at how easy it is to source data from an APEX Cloud instance directly into a R program.To give a bit of background on this, for the past few weeks I've been looking after 5 baby rabbits that were rescued by our friend who is a vet. We had to weigh ...

Traditionally the development strategy for customisations within an organisation using Oracle E-Business Suite has been roughly the following.Data Entry - OA Framework or Oracle FormsTransactional Reporting - Oracle XMLP or Oracle Reports, FSG'sAd/Hoc and Analytic - OBIEE or DiscovererMore and more organisations are now realising however the power ...

I remember when I moved into our first house, and I had to do a bit of DIY in the bathroom to replace some silicon sealant. I removed the old stuff using a utility knife, which whilst worked, involved a lot of messing about, a scratched bath panel, a cut finger and quite a bit of cursing. Several years on I had to do something similar, however this...

​I'm presenting at a small group in the near future on sources of personal data in Oracle E-Business Suite and how this relates to the GDPR regulations. If you're interested in this, you can find my slides below. File Name: General-Data-Protection-Regulation-GDPR-and-Oracle-E-Business-Suite File Size: 658 kb Download File

It's been a while since I wrote the first few posts on APEX exploits, so I'm going to briefly discuss an attack that is very widespread and potentially dangerous.SQL InjectionIn very brief terms, SQL injection involves an attacker causing the server to execute SQL code that it was not intending to, by passing that SQL into a vulnerable statement. D...

I wrote recently on the complexities of achieving GDPR compliance in an environment. Last week Oracle published the following MOS note: Oracle E-Business Suite Data Removal Tool Roadmap (Doc ID 2384629.1) which whilst doesn't contain a great deal of information, is worth bookmarking.The current planned delivery timeline (subject of course to O...

If there's one thing that is causing a lot of stir within organisations at the moment it's GRPR. As the date of enforcement approaches rapidly (25 May 2018), more and more data controllers are becoming increasingly nervous as to whether their Enterprise Systems can support the requirements of GDPR. Particularly, but not onlyRight to AccessRight to ...

I'm quite a fan of row generators. If I know I need a row for every day of the year for example, I generally avoid holding (and maintaining) a table of these dates, and instead will generate them. This is by no means a universal rule, so take each case on its merits.select trunc(sysdate,'YYYY') + level -1 dt from dualconnect by level <= add_mont...

If you haven't already used Live SQL then you're missing out. This free (Enterprise Edition) database instance provided by Oracle as a sandpit for trying things out and learning new stuff is a great resource. More importantly, it tends to be kept in line with the latest "hot off the press" database release.So it came as no real surprise last week w...

This issue arose at a customer today, and I've seen it happen in the past, so I thought it worthwhile making a quick note. A situation had arisen which had caused the overnight ETL execution to be delayed. Once this had completed the users complained that the dashboard was missing some data. This was tracked down to a shared filter which contained ...

With machine learning being one of te big things at the moment, I thought I'd cast my mind back to my first ever c programming assignment at university - write the game of Pangolins. The game is based on the 20 Questions game, whereby the user thinks of an object and the machine aims to guess that object by asking simple yes/no style questions - id...

Unless you've been hiding under a stone for the past few years you'll know that the cloud is the big thing at Oracle. There are fewer and fewer on-premise installations for greenfield projects. With the new pricing structure it is easy to see why more and more organizations are considering cloud services for their new developments. An easy venture ...

If you haven't already started using Oracle Cloud services, then what better way than to sign up for $300 free credits for a pay-as-you-go subscription (valid for one month)? Simply visit https://cloud.oracle.com/tryit and click Sign Up. I did it earlier - it's really easy and was set up within an hour. To try it out, I provisioned myself a Standar...

I know there are already a good number of blogs/guides out there already describing various methods of automating the backup of APEX applications, however I thought I'd share the method I recently implemented internally as it uses a remote subversion repository. This gives rise to a number of subsequent benefits such as holding a full version histo...

Oracle APEX Exploitation - Part 3This is the third in my series of short posts about methods thatc an be used to exploit your Oracle APEX applications. The first two posts concentrated on URL Injection which is relatively easy to protect against, however this third post is going to focus on something that is a bit more difficult to stop, and not qu...

Enter your custom HTML codes in this section ...Taking a break out of the APEX Exploits series for this as it's something I keep coming across at different client sites.select p.full_name, su.supervisor from per_people_x p, per_assignments_x a, per_people_x s where a.person_id = p.person_id and a.person_id (+) = a.supervisor_id;With me (or the read...

Oracle APEX Exploitation - Part 2Following on from my previous post in a series on common exploits in Oracle Application Express, in this post I am going to continue the theme of URL modification, however this time to allow us to execute procedures where we shouldn't be able to. This issue arises from the fact that we can use the construct BRANCH_T...