Subscribe: Johns Blog
http://jes.blogs.shellprompt.net/feed/atom/
Preview: Johns Blog

Johns Blog



Posts about Oracle APEX (#orclapex), Oracle Technology and all things techie



Updated: 2017-06-07T12:08:33Z

 



WordPress.comREST Enabling Oracle Exadata Express using SQLDeveloper

2017-06-05T19:34:14Z

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 […]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://-.db.em2.oraclecloudapps.com/apex/jes/emp/ 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

2017-06-05T11:57:34Z

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. The installation instructions for the Oracle Node driver are pretty comprehensive, […]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 /Users/jes/Work/Node/oracle bash-3.2$ npm init -y Wrote to /Users/jes/Work/Node/oracle/package.json: { "name": "oracle", "version": "1.0.0", "description": "", "main": "index", "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/obj.target/oracledb/src/njs/src/njsOracle.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsResultSet.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsMessages.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsIntLob.o ../src/njs/src/njsIntLob.cpp:1711:3: warning: cannot refer to a non-static member from the handler of a constructor function try block [-Wexceptions] cleanup(); ^ 1 warning generated. CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnv.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnvImpl.o ../src/dpi/src/dpiEnvImpl.cpp:117:3: warning: cannot refer to a non-static member from the handler of a constructor function try block [-Wexceptions] cleanup(); ^ 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. bash-3.2$ 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'); oracledb.getConnection({ user: 'jes', password: 'password', connectString: 'localhost:1521/xe' }, function(err, connection) { if (err) { console.error(err.message); return; } connection.execute( // The statement to execute "SELECT * "+ "FROM emp "+ "WHERE ename = 'SCOTT'", [], function(err, result) { if (err) { [...]



Using SQLDeveloper Drag and Drop to Create Objects to Exadata Express

2017-06-05T10:37:21Z

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 […]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)

2017-06-05T09:44:23Z

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 […]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)

2017-06-05T09:10:16Z

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 – https://docs.oracle.com/en/cloud/paas/exadata-express-cloud/csdbp/connecting-sql-developer.html#GUID-00D45398-2BF3-48D5-B0E9-11979D5EAFFC Step 1 – Create a new connection in SQL Developer Note – I’ve obscured my existing connections for […]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 – https://docs.oracle.com/en/cloud/paas/exadata-express-cloud/csdbp/connecting-sql-developer.html#GUID-00D45398-2BF3-48D5-B0E9-11979D5EAFFC 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 client_credentials.zip 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)

2017-06-05T07:20:25Z

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 […]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 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 client_credentials.zip Now fire up SQLcl, using nolog mode. bash-3.2$ sql /nolog SQLcl: Release 4.1.0 Release Candidate on Tue May 9 21:32:12 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> Now following the instructions we just need to tell SQLcl about the cloudconfig: SQL> set cloudconfig client_credentials.zip ***** JCE NOT INSTALLED **** ***** CAN NOT CONNECT TO PDB Service without it **** Current Java: /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home/jre Follow instructions on http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html SQL> 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- /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home/jre/lib/security 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 client_credentials.zip Using temp directory:/var/folders/2l/lnw1_vds343f1byjvzlr70y80000gn/T/oracle_cloud_config5160868892063171139 SQL> 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 /@



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

2017-06-04T23:04:49Z

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 – https://-.db..oraclecloudapps.com/console/ For for […]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 – https://-.db..oraclecloudapps.com/console/ For for example if you created – Instance; test01 Identity domain: foobarcorp Data Center Location: EMEA then you might get a URL like: https://test01-foobarcorp.db.em2.oraclecloudapps.com/console/ 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 5.1.1.00.08 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 Authenticati[...]



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

2017-06-04T22:26:34Z

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 […]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)

2017-06-04T21:49:55Z

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 […]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 wait[...]



Oracle Exadata Express – Now Available in Europe!

2017-06-04T20:13:44Z

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 […](image)

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!


(image) (image)