Category Archives: Uncategorized

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

 

APEX 5.1 Early Adopter 2 Available

2016-11-10_15-14-47.png

Oracle have updated the Application Express Early Adopter instance to EA2, you can try it out here – https://apexea.oracle.com/

There are some fairly significant changes and enhancements in EA2 versus the EA1 release, particularly around the Interactive Grid feature (which every APEX developer can’t wait to get their hands on!). For example, the list below shows some very cool additions.

2016-11-10_15-17-52.png

There seem to have been some improvements to the Jet Charts too, this is shaping up to be the best APEX release ever IMHO.

 

APEX Integration with SQLCl

Oracle SQLcl contains some very useful built in commands that APEX developers might find useful.

For example, lets say I’m connected to a schema and want to know what APEX applications are installed in the workspace linked to that schema, I can simply issue the APEX command and it will list the APEX applications, for example:

SQL> apex
WORKSPACE APPLICATION_ID APPLICATION_NAME BUILD_STATUS LAST_UPDATED_ON
JES_DEMO 105 LDAP Demo Run and Develop 12-JUN-16
JES_DEMO 280 Amazing jQuery Run and Develop 12-JUN-16

Let’s say you want to export an application from the command line, we can use the APEX EXPORT command like this:

SQL> spool f105.sql
SQL> apex export 105

...application spools out

Now I can do cool things like automate exports from my application using cron, how cool is that?

I see a lot of potential in SQLCl and it keeps improving with each release, go and check it out!

APEX 5.1 Early Adopter 1 Available

After a long (but I’m sure well worth it) wait, Oracle just announced APEX 5.1 Early Adopter is available.

Head over to https://apexea.oracle.com/ to sign up and request a Workspace.

The two big items everyone has been waiting for are

  • Interactive Grids
  • Jet Charts

but reading through the “Additional Features in Application Express 5.1” section on the EA homepage it looks like there are lots of other interesting improvements and enhancements.

 

 

 

SQLcl the Future?

I’ve worked with SQLPlus for years and like many people who have used Oracle for a while you come to learn its strengths and weaknesses. However I must say that over the years SQLPlus has always been reliable but hasn’t exactly changed much.

However there’s a new kids on the block, enter SQLCL (or SQL Command Line), which is available here.

Why is SQLcl a contender to the venerable SQLPus? Well this feature alone makes it worthwhile:

  • Command History

that’s right, it’s 2016 and SQLPlus still doesn’t let you recall previous commands easily (there are ways to do it, but nothing as simple and analogous to the BASH history command)

However, it’s some of the other features that really set it apart, how about –

  • Doing a ‘Create Table As Select’ in a single CTAS command
  • Generating the DDL for database objects using the DDL command
  • Being able to ALIAS your own commands (incredibly useful for creating shortcut commands for things you frequently type)

I really encourage you to try out SQLcl, it’s going to be a HUGE productivity gain over SQLPlus.

Oracle Cloud – Glassfish Administration (port 4848 woes)

In the previous post I discussed accessing the DBaaS Monitor application, in this post I’ll show how to access the Glassfish Admin application.

On the home page for your DBaaS Instance, you’ll see a link for ‘Glassfish Administration’

cloud_home.png

However if you click on that link you’ll probably find the browser just hangs and nothing happens. It took me a while to notice but unlike the DBaaS monitor which is accessed via HTTP/HTTPs, the Glassfish Administration is done via port 4848 (you’ll notice 4848 in the URL once your browser times out).

The issue here is that by default port 4848 isn’t open in your network rules for your DBaaS instance, so the browser cannot connect to it.

So you have a couple of options –

  1. Open up port 4848 to the world (or to just specific IP addresses)
  2. Use an SSH Tunnel

I tend to go with option 2, since I’ve found occasionally while travelling and staying in a hotel if you go with option #1 you might be accessing from an IP address that isn’t in your whitelist.

As I blogged previously, we can setup an SSH tunnel to port 4848 pretty easily from the terminal, with a command similar to:

ssh -L 4848:localhost:4848 -i oracle_cloud_rsa opc@<my.remote.ip.here>

So now we should be able to access Glassfish using the URL http://localhost:4848

Why localhost? Remember when you setup an SSH tunnel you connect to your own local machine which then tunnels the traffic to the remote host via SSH over the ports you specify.

Once we’ve done that you should be able to access the Glassfish Administation homepage.

glassfish.png

You should be able to login using the username ‘admin‘ and the same password you specified when you created your DBaaS instance.

glassfish2.png

The first thing I noticed was that this is a pretty old version of Glassfish which is installed by default (version 3.1.2.2 in my case), when Glassfish 4 was already out. So you may wish to check if you’re missing any patches or need some Glassfish 4 features.

This is definitely one downside to going with the pre-bundled installation, you will (by definition) get an image which was created some time ago, so you need to check if there are any patches etc that have been released since the image was created.

I’m not going to go into detail on Glassfish itself, since it’s pretty much a standard (3.1) Glassfish and there are lots of blog posts and documents around that go into more detail. However if you go into the application section you’ll see that it comes pre-bundled with the APEX Listener / ORDS and also DBaaS Monitor which is how you can access them via the Glassfish server.

glassfish_apps.png