Monthly Archives: May 2017

REST Enabling Oracle Exadata Express using SQLDeveloper

Oracle SQLDeveloper makes it very easy to REST enable your database, I wanted to see if this was possible with the new Exadata Express service or if it was one of the restrictions. It turns out it is very easy to do.

Firstly, we need to connect to the Instance and we’ll enable REST Services by right clicking on the Connection

Now we can go to a table (the ubiquitous EMP table) and REST enable it.

When you click this option, you can give the object an alias (which is useful to hide the details of your internal object names).

For the purposes of this example I’m going to uncheck the Authorization required option since I want anyone to be able to query this rest end-point.

Now we can test the REST endpoint in our browser, in my case the URL is of the format

https://<instance>-<domain identity>

where jes represents my schema name and emp is the table name I just REST enabled.

If I access this URL I see –

How cool and easy is that?





Create a NodeJS Application to connect to Exadata Express

Anyone who knows me, knows I’m a big NodeJS geek, so I thought I’d see how easily I could create a NodeJS application to connect to my Exadata Express instance.

First of all, let’s create a basic Node application with the node-oracledb driver installed.

bash-3.2$ pwd
bash-3.2$ npm init -y
Wrote to /Users/jes/Work/Node/oracle/package.json:

"name": "oracle",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
"keywords": [],
"author": "",
"license": "ISC"

The installation instructions for the Oracle Node driver are pretty comprehensive, but at a high level we need to –

  • Set OCI_LIB_DIR andOCI_INC_DIR environment variables to point to a valid instant client location
  • Run npm install oracledb

On my Mac I already have the 12c Instant Client installed –

bash-3.2$ export OCI_LIB_DIR=/Users/jes/tools/instantclient/instantclient_12_1
bash-3.2$ export OCI_INC_DIR=/Users/jes/tools/instantclient/instantclient_12_1/sdk/include

So I can just go ahead and run the npm install

bash-3.2$ npm install oracledb --save

> oracledb@1.13.1 install /Users/jes/Work/Node/oracle/node_modules/oracledb
> node-gyp rebuild

gyp WARN download NVM_NODEJS_ORG_MIRROR is deprecated and will be removed in node-gyp v4, please use NODEJS_ORG_MIRROR
gyp WARN download NVM_NODEJS_ORG_MIRROR is deprecated and will be removed in node-gyp v4, please use NODEJS_ORG_MIRROR
gyp WARN download NVM_NODEJS_ORG_MIRROR is deprecated and will be removed in node-gyp v4, please use NODEJS_ORG_MIRROR
CXX(target) Release/
CXX(target) Release/
CXX(target) Release/
CXX(target) Release/
CXX(target) Release/
CXX(target) Release/
../src/njs/src/njsIntLob.cpp:1711:3: warning: cannot refer to a non-static member from the handler of a constructor function try block
1 warning generated.
CXX(target) Release/
CXX(target) Release/
../src/dpi/src/dpiEnvImpl.cpp:117:3: warning: cannot refer to a non-static member from the handler of a constructor function try block


clang: warning: libstdc++ is deprecated; move to libc++ with a minimum deployment target of OS X 10.9 [-Wdeprecated]
oracle@1.0.0 /Users/jes/Work/Node/oracle
└─┬ oracledb@1.13.1
└── nan@2.5.1

npm WARN oracle@1.0.0 No description
npm WARN oracle@1.0.0 No repository field.

I did get some warnings, but I’ve done this enough times to know that this successfully installed it.

Now lets create a basic Node application that connects to a local XE instance (just to check connectivity).

var oracledb = require('oracledb');


user: 'jes',

password: 'password',

connectString: 'localhost:1521/xe'


function(err, connection) {

if (err) {





// The statement to execute

"SELECT * "+

"FROM emp "+

"WHERE ename = 'SCOTT'",


function(err, result) {

if (err) {





console.log(result.metaData); //

console.log(result.rows); //




function doRelease(connection) {


function(err) {

if (err) {




this is a pretty simple Node / Oracle script (very similar to some of the examples you’ll find in the node driver documentation).
So let’s run our script to check it works ok –
bash-3.2$ node index.js
[ { name: 'EMPNO' },
{ name: 'ENAME' },
{ name: 'JOB' },
{ name: 'MGR' },
{ name: 'HIREDATE' },
{ name: 'SAL' },
{ name: 'COMM' },
{ name: 'DEPTNO' } ]
[ [ 7788,
20 ] ]
Success! So we can connect to a local XE database fine.
But what about our Exadata Express instance?
The first step is to copy the file to a location your node script can access it, you should also unzip it.
bash-3.2$ ls -al
total 16
drwxr-xr-x 6 jes staff 204 15 May 12:39 .
drwxr-xr-x 13 jes staff 442 15 May 11:40 ..
drwxr-xr-x 3 jes staff 102 15 May 12:39 credentials
-rw-r--r-- 1 jes staff 816 15 May 12:04 index.js
drwxr-xr-x 4 jes staff 136 15 May 11:53 node_modules
-rw-r--r-- 1 jes staff 271 15 May 11:53 package.json</div>
<div>bash-3.2$ unzip credentials/
Archive: credentials/
extracting: cwallet.sso
extracting: sqlnet.ora
extracting: tnsnames.ora
extracting: ewallet.p12
extracting: keystore.jks
extracting: truststore.jks
 Note it’s really important that nobody else can access these files – otherwise they’ll be able to access your Exadata Express instance.
Now we need to edit the sqlnet.ora file to specify the path of the WALLET_LOCATION, so in my case it meant changing –
(METHOD_DATA = (DIRECTORY="?/network/admin")))</div>
(METHOD_DATA = (DIRECTORY="/Users/jes/Work/Node/oracle/credentials")))</div>
Now we need to export the TNS_ADMIN environment variable to this location
<div>export TNS_ADMIN=/Users/jes/Work/Node/oracle/credentials</div>
We can update our node script to reference the Exdata Express Instance
<div>var oracledb = require('oracledb');</div>
<div>user: 'PDB_ADMIN',</div>
<div>password: '<MY PASSWORD>',</div>
<div>connectString: 'dbaccess'</div>
<div>function(err, connection) {</div>
<div>if (err) {</div>
<div>// The statement to execute</div>
<div>"SELECT * "+</div>
<div>"FROM emp "+</div>
<div>"WHERE ename = 'SCOTT'",</div>
<div>function(err, result) {</div>
<div>if (err) {</div>
<div>console.log(result.metaData); //</div>
<div>console.log(result.rows); //</div>
<div>function doRelease(connection) {</div>
<div>function(err) {</div>
<div>if (err) {</div>
Note the only difference here is that I’ve changed the username to be PDB_ADMIN (together with the correct password) and also changed the connectString to be dbaccess (which is referenced from the file).
Now let’s try our script again –
bash-3.2$ node index.js
[ { name: 'EMPNO' },
{ name: 'ENAME' },
{ name: 'JOB' },
{ name: 'MGR' },
{ name: 'HIREDATE' },
{ name: 'SAL' },
{ name: 'COMM' },
{ name: 'DEPTNO' } ]
[ [ 7788,
20 ] ]
Now I’m sure you might say – that’s just the same output, let’s verify by changing the query in our script to –
SELECT host_name from v$instance
now we get
bash-3.2$ node index.js
[ { name: 'HOST_NAME' } ]
[ [ '<intentionally obscured>' ] ]
So we are 100% accessing the Exadata Express instance on Oracle Cloud.
I’m very impressed with how simple Oracle have made it to connect the different client tools to the Oracle Cloud, this is really low barrier to entry and I can see many more client applications moving to using Oracle Cloud as a backend service.

Using SQLDeveloper Drag and Drop to Create Objects to Exadata Express

In a previous post I showed how to connect SQLDeveloper to an Exadata Express Instance.

I was creating some schema objects whilst playing around with my Instance and seemed to remember hearing that you can drag and drop objects straight from SQLDeveloper so I decided to give it a try.

Firstly you need to open up a connection to your Exadata Express instance which I’ll use as my destination and a connection to your source instance.

Currently my destination schema has no objects in it

Now lets select the EMP and DEPT tables from the source connection and drag to our Exadata Express connection and you should see the following dialog pop-up.

I’ll go with the defaults here, after a moment or two the migration should have worked and you can see the new objects in the Exadata Express instance.

See how easy that was? It even created the primary keys and foreign constraints.

Note you’re not just limited to tables for this, it works for any type of database objects – packages, views, sequences etc.

Very cool!

Oracle Exadata Express Service – Kicking the Tires (Part 6 – Managing your Instance)

In the previous post I covered connecting SQLDeveloper to your Exadata Express instance, but how do you do basic DBA level tasks?

Well, SQLDeveloper to the rescue – it has a very nice (albeit fairly minimal) DBA interface.

Assuming you already have a connection to your Instance (this will need to be a user with sufficient privileges – in this case I’ll use the PDB_ADMIN user).

In SQLDeveloper clicking the view menu reveals a DBA menu item.

once you click this you can select your Exadata Express connection and you’ll see a new DBA panela become visible.

From here you can view Database Configuration details such as the Initialisation Parameters, Database Feature Usage etc

One of my favourite views (that not many people know about apparently) is the Instance Viewer, which gives you a great overview of the current status of your database and what it is doing.

since I’m running in a PDB, I’m not sure about the accuracy of some of these figures, since it shows me I’m currently using 262Gb of SGA (but the X20 service should only have 3GB PGA and 3GB SGA). I’m assuming I’m getting insight into the figures from the CDB. I must admit when I first saw these figures I raced back to check I’d signed up for the X20 service and not an entire Exadata Instance to myself!

So how about Importing and Exporting data? Well we can do Datapump Imports and Exports right out of SQLDeveloper

Under the Performance option we can also view ASH and AWR reports – you’ll get a warning about checking licence but the Exadata Express instance allows that option (at the time of writing!).

Under the Security section we can create users

You can create tablespaces directly from SQLDeveloper too

As you can see SQLDeveloper offers a rich enviroment for GUI DBA control over your Exadata Express Instance (and you can always drop down into the SQLcl command-line to perform manual DBA tasks!).

Oracle Exadata Express Service – Kicking the Tires (Part 5 – SQL Developer Connectivity)

In the last post we discussed connecting SQLcl to Oracle Exadata Express, now lets see how we can connect SQL Developer

EDIT: After writing this post, I found there was a detailed help available here –

Step 1 – Create a new connection in SQL Developer

Note – I’ve obscured my existing connections for obvious reasons.

You’ll need to populate

  • Connection Name
  • Username (I’m going to use PDB_ADMIN)
  • Password
  • Connection Type – (there is a new connection  type of Cloud PDB)
  • Configuration File
  • Keystore Password


Step 2- Ensure the JCE Policy files are installed

Recall in the post on connecting SQLcl to Exadata Express we needed to install the JCE Policy Files, we need to do the same for SQLDeveloper.

Fortunately SQLDeveloper shows an error message which shows the location it is using to try and find the files.

Hmmm, that’s weird – that is the location I put the files for my SQLcl installation.

Ah! I realised my mistake, SQLDeveloper was already running when I installed the files for SQLcl, so I just needed to restart SQLDeveloper so it picked up the new JCE Policy files.

After a quick restart, everything looks ok

Now we just select the same file we used earlier for the SQLcl connection and enter the keystore password that was entered when you downloaded the credentials file.

Clicking the Test button shows we can connect ok!

Now we can save and open the connection and we should be good to go

In this example I connected as PDB_ADMIN, you can use the same technique to connect as your real development SCHEMA user.


Oracle Exadata Express Service – Kicking the Tires (Part 4 – SQLcl Connectivity)

In the last post we looked round connecting to our Exadata Express Instance via a browser to play with App Builder and SQL Workshop.

How about if we want to connect via SQLcl (you can use SQLPlus if you like but SQLcl is sooo much more productive!)?

Well if you recall from the earlier post there is an option in the Service Console to enable client access


So, let’s click that link

Enable Client Access

Clicking the ‘Enable Client Access’ button is a tad underwhelming (I’m not sure what I expected), but the Service Console now has some new options available.



In order to connect via SQLcl we need to download some client credentials (think of it like private keys in SSH).

We can also download drivers for other languages (e.g. NodeJS), also there are handy links for downloading SQLcl, SQL Developer etc.

So clicking on the Download Client Credentials asks me to enter a password:


It took me a few attempts to create a password with sufficient complexity (even using 1Password), once I had a valid password it took 20 seconds or so to download the credentials (I assume in the background it’s generating a private / public key)


There is a very handy link at the end of the Instructions for step-by-step walkthroughs of connecting via the various clients. In essence it boils down to:


For SQLcl, I just needed to move the downloaded zipfile somewhere SQLcl will be able to access it – note you do not need to unzip the file.

bash-3.2$ ls -al
total 48
drwxr-xr-x 3 jes staff 102 9 May 21:22 .
drwxr-xr-x 3 jes staff 102 9 May 21:22 ..
-rw-r--r--@ 1 jes staff 22421 9 May 21:22

Now fire up SQLcl, using nolog mode.

bash-3.2$ sql /nolog

SQLcl: Release 4.1.0 Release Candidate on Tue May&amp;nbsp;9 21:32:12 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Now following the instructions we just need to tell SQLcl about the cloudconfig:

SQL> set cloudconfig
***** CAN NOT CONNECT TO PDB Service without it ****
 Current Java: /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home/jre
 Follow instructions on

Hmm ok, that didn’t work too well.

Ah! Looking back at the requirements up above, I didn’t install the Java Cryptography Extension. Ok let’s do that by visiting the helpful link in the error message:


When you download this zipfile you’ll find it contains 3 files:

  • local_policy.jar
  • README.txt
  • US_export_policy.jar

If you read the README.txt (everyone does that right?) – you’ll see you need to copy those 2 jar files to the location of your JRE (overwriting the originals after taking a backup incase you want to revert!).

In my case, on a Mac I needed to copy them to-


Once I’d done that I needed to quit out of SQLcl and reconnect so it picked up the new jar files, then I could retry the cloudconfig setting

SQL> set cloudconfig
Using temp directory:/var/folders/2l/lnw1_vds343f1byjvzlr70y80000gn/T/oracle_cloud_config5160868892063171139

Curiously it doesn’t prompt me for my password here (the one I used when I Downloaded Client Credentials (even though the help says it should). Now according to the Help I should be able to just connect

and…here’s where some problems started….this implies I should use the syntax

conn <username>/<password>@<servicename>

So I tried all types of combinations of my email address (assuming that was the right username), also for servicename I used –

  • aeexa01
  • aeexa01-<Identity Domain>
  • aeexa01-<Identity Domain>.

but almost everything I tried resulted in an error similar to

SQL> conn <MY USERNAME>@aeexa01
Password? (**********?) ****************
URL = jdbc:oracle:thin:@aeexa01
Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"
Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"

Warning: You are no longer connected to ORACLE.

Question Everything

So, I fell back on an old technique I always have in these situations, step away from the computer for a bit, have a coffee, do something else entirely and then come back to it.

The first thing I tried (I’m still not sure what made me do it), was trying the exact same connection details shown in the help, i.e.

conn pdb_admin/welcome1@dbaccess

and i received this

SQL> conn pdb_admin/welcome1@dbaccess
USER = pdb_admin
URL = jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1522)( (connect_data=( (security=(ssl_server_cert_dn=",O=Oracle Corporation,L=Redwood Shores,ST=California,C=US")) )
Error Message = ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

check out the “the account is locked” message!

How could this be? My instance is not named dbaccess so am I connecting to someone elses instance?

Remember I told you earlier you didn’t need to unzip that zip file? Well that’s exactly what I did to see what was in it.

bash-3.2$ ls -al
total 112
drwxr-xr-x 9 jes staff 306 9 May 07:54 .
drwxr-xr-x 4 jes staff 136 9 May 07:53 ..
-rw-------@ 1 jes staff 7045 9 May 22:20 cwallet.sso
-rw-------@ 1 jes staff 7000 9 May 22:20 ewallet.p12
-rw-------@ 1 jes staff 3181 9 May 22:20 keystore.jks
-rw-r-----@ 1 jes staff 134 9 May 22:20 sqlnet.ora
-rw-r-----@ 1 jes staff 334 9 May 22:20 tnsnames.ora
-rw-------@ 1 jes staff 3797 9 May 22:20 truststore.jks

Ah, there’s a tnsnames.ora in there, let’s look at it –

bash-3.2$ more tnsnames.ora
dbaccess = (description=
(connect_data=(service_name=<SOME RANDOM STRING>
(security=(ssl_server_cert_dn=",O=Oracle Corporation,L=Redwood Shores,ST=California,C=US"))

Ah, so that explains it, looks like the default configuration gives a name of dbaccess.

But what about the PDB_ADMIN user that we used? Well lets take a look back of the Service Console page –

Notice the PDB_ADMIN user is mentioned there. Now let’s set a password

Now lets try connecting again –

SQL> conn pdb_admin@dbaccess
Password? (**********?) ***********


Let’s try some basic queries –

SQL> select object_name, object_type from user_objects;
no rows selected

hmmm, not very exciting, let’s check which users are available

SQL> select username from all_users order by username;


49 rows selected.



So quite a lot of default users setup. Notice also the schema named XZ2KKSM1OE which is the same default one I noticed in SQL Workshop in a previous post.

So let’s set the password for that account and trying logging in –

SQL> alter user XZ2KKSM1OE identified by <MY_NEW_PASSWORD>
2 /

User XZ2KKSM1OE altered.

SQL> conn XZ2KKSM1OE@dbaccess
Password? (**********?) ************


SQL> select object_name, object_type from user_objects;
DEPT                              TABLE
SYS_C0013892                      INDEX
EMP                               TABLE
<rest of output ommitted>

So, you see how easy it is to connect from your local machine to Exadata Express via SQLcl (that is, once you know all the quirks!)

Oracle Exadata Express Service – Kicking the Tires (Part 3 – Poking Around)

In the last post, I covered provisioning an Oracle Exadata Express instance. Now we have one up and running let’s click the Service Instance URL and see what the dashboard looks like.

In my case the format of the URL in the Service Console (and emails) was in the format –


For for example if you created –

Instance; test01

Identity domain: foobarcorp

Data Center Location: EMEA

then you might get a URL like:

I like this format, since it’s entirely logical. I remember using the DBaaS service when it was first available and it seemed to me that the URL’s were far from logical (I noticed pretty quickly that newer instances got more logical URL’s, but some of our early instances stuck with the original format).

Ok, so lets click on that URL (note my real URL, not the made up one above which certainly won’t work!)

I immediately see this –

A couple of things to note –

  • It’s HTTPS by default (great!)
  • You can only access that page if you’re logged into your Cloud Account (i.e. it’s not visible to the Public Internet-at-large yet)

The one that intrigues me straight-away is access to SQL Workshop and App Builder, which are both part of Oracle APEX.

If I click on SQL Workshop, it takes me right into it (no need to re-authenticate since I’m already logged into my Cloud Account so it knows who I am).

Now the really interesting thing here is right down in the bottom right corner, it shows us the installed APEX version –

Application Express

That’s the latest and greatest (at the time of writing), so that’s fantastic. I haven’t read anything so far on what the upgrade options are going to be once new versions of APEX are released (that would be interesting to know). But hey, at least it’s the latest version with all the nice features like Interactive Grid etc.

So let’s jump into SQL Commands and run a quick query to see what objects are installed:

So just the typical EMP / DEPT tables and a DEMO app / package, nothing unexpected.

One key thing I noticed, take a look at the schema name it lists

Urgh…that’s clearly generated and non-human-friendly.

To be honest I didn’t spot when I was provisioning the schema that I wasn’t prompted for a schema name to use. However for those of you with good memories one of the options right back in the first screenshot above was the ability to create a new schema.

Ok, now let’s try jumping into App Builder (which is part of APEX), clicking the link on the main dashboard takes me to the familiar App Builder interface:

Notice the Sample Database Application is already pre-installed. I wanted to know if this application was available to anyone without having to be authenticated to the Cloud Account (i.e. could anyone on the internet access it?).

Running the application gave me the (very familiar to me) Sample Application –

So I fired up Google Chrome in Incognito mode from another laptop and tried to access the application directly using the same URL –

Hmmm, so by default you need to be logged into the Oracle Cloud to access your APEX applications. How can we change that?

I went looking in the Authentication Scheme for the Application and sure enough it was set to a new type of Authentication – ‘Oracle Cloud Identity Management’, which makes sense.

I created a new Authentication Scheme based on APEX Accounts and enabled it and retried accessing the application from another browser:

Success – the application is now visible to anyone on the internet!

In the next post we’ll explore how to connect to the Instance remotely via SQLcl