Oracle Exadata Express Service – Kicking the Tires (Part 2 – Initial Setup)

In the last blog post I discussed signing up for the Exadata Express service, now I have access to it let’s see what I can do.

In the email I received after signing up, it contains some key information (obviously I’ve obscured it in the screenshot below) –

Much like the Oracle DBaaS service I get my own Identity Domain and a URL that I can use to access my service via a browser.

There is nothing in the email about how to connect via SQLPlus etc, but we’ll come to that in later posts (hopefully!).

So, let’s point my browser at the URL in the email and enter my OTN credentials, I end up in my Cloud Dashboard page, but I now see an Exadata Express section.

(Side note – I did hear of a few people who didn’t see that region after signing up and they had to add it manually, but I did not experience that behaviour).

Ok, so lets hit the ‘Create Instance’ link and see where it takes us

So now it recognises that Exadata Express instances are available to me (that option was obviously not available to me before I signed up for it).

On the next screen I just need to specify my new Instance name (I chose aeexa01 which was about as imaginative as I could get).

(again note I’ve obscured details like my Identity domain and email address)

Clicking next takes me to a confirmation page where I can click on confirm and create my instance.

Returning back to the Dashboard I can see my Instance is being created

The eagle-eyed amongst you will also see a Red icon showing I had a previous failure – the first time I tried to create an instance it just hung – since it was the first one I created I wasn’t sure how long it should take, but it was a definite failure so I terminated it (even though it didn’t seem to be accessible).

Roughly a couple of minutes after initiating the creation of the (second time) instance I received an email informing me it had been created, I could confirm this since the icon turned from Orange to Green in the dashboard.

Drilling into the link showed me the Instance was indeed up and running

I’m pretty impressed, the time to create the instance (~ 2 minutes) was substantially quicker than the time it took to purchase the instance (~ 5 minutes buying in the Oracle Store and then over 2 hours waiting for the confirmation email)

So that’s how easy / quick it is to provision an Oracle Exadata Express instance in Oracle Cloud!

In the next post we’ll explore the instance by clicking on that lovely inviting Service Instance URL link 😉

 

Oracle Exadata Express Service – Kicking the Tires (Part 1 – SignUp)

Pretty much immediately after I read the announcement that the Oracle Exadata Express service was available in Europe I decided to sign up to test it out.

Looking at the 3 options available (Exadata Express – X20, X50 and X50IM), I decided to go for the X20 option – primarily because I was interested how you connect to these instances, rather than.

After looking at the pricing options, I noticed a couple of points that jumped out at me.

Firstly you get 1 PDB, no mention of an option to purchase additional ones (I’m guessing you would need to sign up for a new instance rather than being able to clone an existing PDB for Dev / Test / Prod etc).

Secondly it has APEX already installed, which is obviously great if you want to get up and running with APEX right away.

Ok, so let’s go through the signup process and see how smooth it is…..!

After clicking the “Buy Now” button, I’m redirected to the Oracle Store (which if you didn’t notice is an APEX application!).

Clicking on the X20 option let’s me, choose whether I want to be billed –

  • Month-to-Month
  • 1 Year
  • 2 year
  • 3 Year

I must admit, I was slightly confused at this stage what the benefit of going for 1-Year or 2-Year etc versus Month-to-Month was. I didn’t seem to get a discount for going multi-year and in terms of flexibility for the same cost I could sign up month-to-month and cancel whenever I wanted to (please feel free to point out if I’m being dumb here, but I think they could highlight the differences clearer).

Then it’s a simple matter of Adding my choice to the cart, hitting checkout and paying for it (nope I’m not going to show you that bit, too many personal details on that page!).

All in all, I was pretty impressed – not too many clicks to sign up. I do have to say that I still find the Oracle Cloud payment / invoicing aspect slightly disconnected versus say Amazon. In Amazon AWS they already have my payment details – I just launch a new instance and get billed for it. Whereas with Oracle Cloud, I need to go through paying for each new instance before I can launch a new one (so in essence I’m paying to increase my quota for a specific service type). It might sound like a small quirk, but part of the real ‘wow’ factor of Amazon is the immediacy of being able to spin up an instance on demand quickly. Oh well…I’m sure there’s reasons for doing it this way.

So, after I sign up it tells me that I’ll receive an email once my service is available and that I can keep checking on progress via my Orders.

So, I wait…

and wait…

and wait…

About 2 1/2 hours (150 minutes!) later (I lost track of time but it was roughly then), I receive an email –

Note – I’ve omitted the majority of the email since it contains a lot of details on my service URL, CSI etc.

Again, not to gripe too much but 2.5 hours seems WAY too long to wait. As a frequent Presenter at Conferences it would be nice to walk through showing how easy Exadata Express is to setup, but I’m not sure the attendees would wait 2.5 hours for my email confirmation to come through.

Either there’s an element of human interaction going on here (why? Surely all this can be automated), or Oracle is so inundated with people signing up for the service that I ended up at the end of a very long queue. Either way I really do hope this signup time decreases in future or I predict people getting frustrated waiting for the mail to come through.

So that’s it, I now have access to an Exadata Express instance to play with. In the next blog post I’ll go through setting it up and accessing it.

 

Oracle Exadata Express – Now Available in Europe!

I was very interested to see the announcement by Oracle that the Exadata Express service was now available in Europe

https://blogs.oracle.com/dbaas/exadata-express-cloud-service-now-available-in-europe

I’ve been using the Oracle DBaaS service since it was (more or less) first publicly available and have been very impressed with the general performance and availability,

If you’re not familiar with it, the Exadata Express service comes in 3 flavours,

  • Exadata Express – X20
  • Exadata Express – X50
  • Exadata Express – X50IM

As you would expect, the main differences are around the amount of memory and storage you get, together with some differences in feature availability.

At the time of writing, it breaks down as –

  • Exadata Express – X20
    • 20GB Storage
    • 3GB PGA, 3GB SGA
    • 120GB / Month data transfer
  • Exadata Express – X50
    • 50GB Storage
    • 5GB PGA, 5GB SGA
    • 300GB / Month data transfer
  • Exadata Express – X50IM
    • 50GB Storage
    • 5GB PGA, 10GB SGA (5GB RAM for use with Database In-Memory Column Store)
    • 300GB / Month data transfer

So what about cost (prices correct at time of writing)?

  • Exadata Express – X20
    • $175.00 / Month (£141.00 / Month)
  • Exadata Express – X50
    • $750.00 / Month (£604.00 / Month)
  • Exadata Express – X50IM
    • $950.00 / Month (£765.00 / Month)

To be honest, when I saw these prices – I wondered what the break-point would be for choosing Exadata Express versus spinning up a dedicated DBaaS instance. The X20 looks like a decent price, the X50 and X50IM, well I’m not quite sure yet….I suspect the prices are too high for the average individual user, but the specs aren’t high enough for corporate users (50Gb storage these days isn’t a lot).

So…next steps…I’m going to sign up for the X20 and start kicking the tires!

APEX 5.1 – Packaged Apps & JET Charts

I was looked around at the Packaged Apps in APEX 5.1 and noticed the Sample Charts application has been updated with the new JET charts.

If you’re not familiar with the Packaged Apps, they’re a great way to learn about the features and functionality in APEX, since if you see something you like in a packaged app you not only know that it must be possible to do that in APEX (since the packaged apps are APEX applications), but also you have access to the application so you can see exactly how the Oracle APEX development team accomplished it (note this will involve unlocking the sample app which has implications so make sure you’re happy with those implications before doing that).

So let’s go ahead and install the Sample Charts Packaged Application

after accepting the defaults in the wizard you’ll be able to run the application.

I like the look of this application and think it’ll be a great resource to refer back to, not only to see the different types of charts available but also how to implement them (remember I said you have full access to the packaged applications).

For example, looking at the new JET Bubble chart

I can dig into the SQL Query and see that it uses –

In other words, I need to define a query like

select country country,
       name,
       (employee/100) employee,
       (employer/100) employer,
       (total/100) total
 from eba_demo_chart_stats
 

and then you declaratively define which columns should be used for –

• Label
• X (position on the X axis)
• Y (position on the Y axis)
• Z (i.e. the size of the bubble)

So rather than trying to figure out from scratch how to produce a particular type of chart I can jump into the Packaged Application, find the example I like and then see how to implement it – that is a huge time saver.

There are quite a few of the chart types that still use either the old Anychart or D3 types and haven’t been updated to JET examples yet, but I’m sure that’s coming in APEX 5.2, but so far I’m loving the Oracle JET integration in APEX 5.1!

APEX 5.1 – Package Application – Quick SQL

You might have seen some demos of Live SQL from Oracle, however you might not know that there is a packaged application in APEX 5.1 called Quick SQL with very similar (if not identical) functionality that you can use locally.

Firstly – why the name difference between Live SQL and Quick SQL? Answer – I have no idea, perhaps to seperate the two applications in terms of one is a packaged application (Quick SQL) whilst the other (Live SQL) is a hosted application (by Oracle) that can be used by anyone with a web-browser regardless of if they have a local APEX installation or not. Also, since Live SQL is hosted publically and Quick SQL is shipped with APEX, it’s possible that Live SQL contains some newer features / bug fixes etc that may or may not be included in an updated packaged application in the future when APEX itself is patched (or 5.2 comes out).

For the rest of this post I’m going to refer to Quick SQL, however as far as I’m aware most of the functionality can equally be used on the public Live SQL site.

So what is Quick SQL? Well if you’ve spent any time with Oracle (or indeed any other RDBMS), you’ll have lost count of the number of times you’ve hand-typed DDL statements like –

create table foo (
  id number not null, 
  data varchar2(20) not null, 
  constraint foo_pk primary key (id) enable 
);

If you’ve used Oracle for a while, you might have some pre-defined templates you use to make this task less manual, however it is still a pretty manual task (unless you obviously use something like SQL Developer Data Modeller).

What Quick SQL does is allow you to write out SQL is shorthand notation and it transforms that shorthand into the full syntax.

So, let’s install the Quick SQL Packaged Application

The list of packaged applications can be quite long, so I just filtered for ‘sql’

Follow the wizard steps and you should have it installed in under a minute, once you run the application and login using your workspace credentials it will ask you a couple of questions about Access Control (I just went with the defaults).

After hitting “Complete Setup”, you get to the main application, which at first glance might look a bit underwhelming.

That’s it, just two panes, one pane you can type into, whilst the other pain is for output. So let’s go ahead and type this into the left hand pane –

foo
  id
  data

type it exactly like that, as soon as you hit return at the end (or press the ‘Generate’ button) you should see the right hand pane become populated with output.

in my case, the output was

-- create tables
create table FOO (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint FOO_ID_PK primary key,
    DATA                           VARCHAR2(4000)
)
;

-- load data
 
-- Generated by Quick SQL Tuesday June 06, 2017  10:06:13
 
/*
foo
  id
  data

# settings = {  }
*/

You’ll notice a few things here –

• The ID column definition has some Oracle 12c syntax (the GENERATED BY DEFAULT ON NULL AS IDENTITY piece)

• It includes a comment on the input that was used to generate this output

• The DATA column was defined as a VARCHAR2(4000) by default

Let’s say instead of a VARCHAR2(4000) column, the data column should be a VARCHAR2(20) and also NOT NULL, just type this into the left hand pane –

foo
  id
  data vc20 /nn

which should give the following output (I’ve omitted the comments section for brevity)

create table FOO (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint FOO_ID_PK primary key,
    DATA                           VARCHAR2(20) not null
)
;

so the VC20 was interpretted as VARCHAR2(20) and the /nn is shorthand for “NOT NULL”, we can also use any length datatype, e.g. VC5, VC10 etc.

Ok, so that’s not the worlds most impressive demo for saving time, but lets look at another use-case. Let’s say a typical Master Detail scenario, try typing this into the left hand pane –

dept
  deptno /nn
  dname /nn
  emp
    empno /nn
    deptno /nn
view emps_vw dept emp

this should produce the text

-- create tables
create table DEPT (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint DEPT_ID_PK primary key,
    DEPTNO                         VARCHAR2(4000) not null,
    DNAME                          VARCHAR2(255) not null
)
;

create table EMP (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint EMP_ID_PK primary key,
    DEPT_ID                        NUMBER
                                   constraint EMP_DEPT_ID_FK
                                   references DEPT on delete cascade,
    EMPNO                          VARCHAR2(4000) not null,
    DEPTNO                         VARCHAR2(4000) not null
)
;

 

-- indexes
create index EMP_i1 on EMP (DEPT_ID);

-- create views
create or replace view EMPS_VW as 
select 
    DEPT.ID                                            DEPT_ID,
    DEPT.DEPTNO                                        DEPT_DEPTNO,
    DEPT.DNAME                                         DNAME,
    EMP.ID                                             EMP_ID,
    EMP.EMPNO                                          EMPNO,
    EMP.DEPTNO                                         EMP_DEPTNO
from 
    DEPT,
    EMP
where
    EMP.DEPT_ID(+) = DEPT.ID
/

-- load data
 
-- Generated by Quick SQL Tuesday June 06, 2017  10:15:57
 
/*
dept
  deptno /nn
  dname /nn
  emp
    empno /nn
    deptno /nn
view emps_vw dept emp

# settings = {  }
*/

notice here that simply by indentation, Quick SQL has figured out it needs to generate a foreign key relationship between the two tables.

I have also created a view joining the two tables, in only 7 lines of Quick SQL markup I’ve output close to 40 lines of DDL, saving myself a huge amount of typing.

There are a huge number of Table level directives you can use, for example –

  • /api     Generate PL/SQL package API to query, insert, update, and delete data within a table.
  • /audit     Adds Oracle auditing, by default audit all on table name.
  • /auditcols, /audit cols, /audit columns     Automatically adds an updated, updated_by, inserted, and inserted_by columns and the trigger logic to populate.
  • /compress, /compressed     Table will be created compressed.
  • /history     Generate code to log changes into a history table for this specific table.
  • /insert NN     Generate NN SQL INSERT statement(s) with random data.
  • /select     Generate SQL SELECT statement.
  • /rest     Generate REST enablement of the table using Oracle REST Data Services (ORDS).
  • /uncomment, /uncommented     If Yes will cause generated SELECT or INSERT SQL statements to not be commented. Reference /insert and /select.

and also column level directives –

  •  /idx, /index, /indexed     Will create a non unique index on the associated column.
  • /unique     Creates a unique constraint.
  • /check     Creates a check constraint with with comma or white space delimited values e.g. /cc Y N
  • /constant     When generating random data for a table set this column to a constant value, quotes will be added if needed.
  • /values     Comma separated list of values to use when generating random data. For example /values 1, 2, 3, 4 or /values Yes, No.
  • /upper     Forces column values to upper case.• /lower     Forces column values to lower case.
  • /nn, /not null     Adds a not null constraint on the column.
  • /between     Adds a between check constraint on the column.
  • /hidden, /invisible     Hidden columns are not displayed using select * from table.
  • /references, /reference, /fk     Foreign key references e.g. /references table_name. You can also reference tables that are not part of your model.
  • /pk     Identifies single column as table primary key. It is recommended not manually specify primary keys and let this app create them for you.

So you can a huge amount of control over the output DDL.

However, remember earlier when I said the DDL used some 12c syntax – what if you’re still on 11g? Well you can control that via the settings, either by including –

#db: 11g

in your markup in the left pane, or use the settings button to change it. It’s worth also looking in the Syntax and Examples section as it contains (funnily enough) lots of syntax and examples that you can learn from.

Using LISTAGG in APEX Reports

One design pattern I’ve hit many times over the years is the need to display master-detail related information in a report.

For example with the typical EMP, DEPT tables you might want to display a report of all Departments, together with the Employees in each department.

Let’s look at a quick way to achieve this.

Firstly lets create a standard report using the following query –


select *
from dept d

this would produce a report like –

so, let’s say we want to include a list of all the employees for a given department, we can use the LISTAGG function.

It looks more complicated than it actually is, so lets jump ahead to a working example, we can adapt out query to


select d.deptno,
d.dname,
d.loc as location,
(select listagg(e.ename, ',')  within group (order by ename)

from emp e

where e.deptno = d.deptno) as employees
from dept d

this new query produces the following report

that’s great but we can use HTML to format it a bit nicer.

Let’s change the query to wrap the item inside an <li> </li> container

select d.deptno,
       d.dname,
       d.loc as location,
       (select listagg('<li>' || e.ename || '</li>', ',') 
               within group (order by ename) 
          from emp e 
         where e.deptno = d.deptno) as employees
 from dept d

Now we can use the HTML Expression setting in the EMPLOYEES column in the report to wrap a <ul> around the content

We also need to change “Escape special characters” to No, this gives the following output in the report

As you can see this is quite a simple technique, but gives you a lot of control over styling the individual detail elements – you could for example use span elements and style them as bullets, for example

APEX 5.1 – Interactive Grid – Master Detail

For as long as I’ve been using APEX, a constant cry is “But what about Master Detail?”, well you’ve always been able to do that with Tabular Forms and for complex examples you’ve always been able to create your own editable reports using the APEX_ITEM API. However it’s always been just that little bit more difficult than it should be (not to mention long term maintainability of the code wasn’t always straight-forward – if for example the underlying table definitions changed).

This has been one of the promises of the Interactive Grid feature in APEX 5.1, so let’s take a look at how easy it is.

Once again, we’ll use the ubiquitous EMP and DEPT tables, so let’s create a Master Interactive Grid using the query –

select d.*
  from dept d

Let’s also add another Interactive Grid for our Employees, using the query –

select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno       
  from emp e   

Ok great, so we have 2 Interactive Grids, but they aren’t linked in any way.

To setup the relationship between the two Grids, we need to do a few steps –

• Configure the Employee Grid so that it “knows” the Departments region is the Master (hence the Employee region is a Detail region)
• Link the Master / Detail columns between the two regions

So firstly lets define the Master Detail relationship

Now we link the DEPTNO column in the Employees Interactive Grid with the DEPTNO column from the Master region (Departments)

If we run the page now, we’ll get an error –

this is quite self-explanatory but basically the DEPT table didn’t have a Primary Key defined (for whatever reason), so let’s go and define the DEPTNO column as the Primary Key.

Now when we run our page, if we select a row in the Departments Grid, we’ll see the corresponding Employees for that department in the Employees grid.

This might have seemed like a few steps to go through, but really it’s just a couple of additional steps to what you’d need to do to define your reprts anyway. Congratulations to the Oracle APEX team for making this so declarative and simple to implement.