PL/SQL Objects for JSON in Oracle 12cR2

I’ve been playing around with some more of the new JSON features in Oracle Database 12c Release 2 (12.2).

The first thing I tried was the new PL/SQL support for the JSON functions and conditions that were introduced for SQL in 12.1. That was all pretty obvious.

Next I moved on to the new PL/SQL objects for JSON. These are essentially a replacement for APEX_JSON as far as generation and parsing of JSON data are concerned. If I’m honest I was kind-of confused by this stuff at first for a couple of reasons.

  • If you are coming to it with an APEX_JSON mindset it’s easy to miss the point. Once you “empty your cup” it’s pretty straight forward.
  • The documentation is pretty terrible at the moment. There are lots of mistakes. I tweeted about this the other day and some folks from the Oracle documentation team got back to me about it. I gave them some examples of the problems, so hopefully it will get cleaned up soon!

I was originally intending to write a single article covering both these JSON new features, but it got clumsy, so I separated them.

The second one isn’t much more than a glorified links page at the moment, but as I cover the remaining functionality it will either expand or contain more links depending on the nature of the new material. Big stuff will go in a separate article. Small stuff will be included in this one.

I also added a new section to this recent ORDS article, giving an example of handling the JSON payload using the new object types.

I’ve only scratched the surface of this stuff, so I’ll probably revisit the articles several times as I become more confident with it.

Cheers

Tim…

PS. Remember, you can practice a lot of this 12.2 stuff for free at https://livesql.oracle.com .

Oracle Database 12c on Fedora 25

I was having a play around with Fedora 25 last weekend and I just noticed there was no associated blog post about it, so here goes.

First the warning I always have to include. Do not install Oracle on Fedora before reading this! With that out of the way, let’s continue.

Since Fedora is the proving ground for future releases of Red Hat Enterprise Linux (RHEL) and I’m a fan of running Oracle on a binary clone of RHEL called Oracle Linux (OL), I am curious about any new release.

Not surprisingly, Oracle database 12c installs really easily on Fedora 25, just as it did on previous releases.

I did have the usual problem with the Perl stuff, as I’m running the VM on a relatively modern MacBook Pro, which has a CPU that freaks out the version of Perl shipped with Oracle, but the article includes a link about how to fix that. I’ve repeated the installation under VirtualBox on systems using older chips (my work PC and an old server at home) and it works without complaint. No drama.

So all seems well in the world of Oracle 12c on Fedora 25. Now you know how to do it, please don’t. Use Oracle Linux. 🙂

Cheers

Tim…

Multitenant : It’s easy to screw up!

multitenantSome things happened to me yesterday which I thought I would pass on, in case it helps others. This is not necessarily a criticism of the Multitenant architecture itself, but more about my misuse of it. 🙂

I’ve complained numerous times about the inclusion of the shared APEX installation used with the Multitenant option. I’m sure it is great for some companies that provide APEX as a service, but it’s not the right option for me. I would typically recommend removing it before you create a PDB, as described here. So what did I do yesterday? I banged out a script as a variant of a non-CDB build and totally forgot about the shared installation. When everything was finished I patted myself on the back, then noticed my mistake. 🙁 I had to remove the PDBs from 3 instances, remove the shared APEX installations, the recreate the PDBs. Luckily it was early on in the process, so it wasn’t a great drama. It would have been significantly more annoying if I hadn’t noticed for a few days!

Next up I nearly cloned a PDB, which would have left me with 2 PDBs in the instance, and potentially a big bill for the Multitenant option. I was literally about to hit the return key when I realised what I was doing. 🙁 Needless to say I immediately added a trigger to all my instances to prevent any future stupidity on my part. You can see what I did here. As I say in the article, I’m not sure if there is some hidden long term tracking of the maximum number of PDBs in a CDB, but I’m a paranoid type… 🙂

As an aside, I also noticed that when I add a new PDB to a CDB it takes Cloud Control 13c a long time to notice the PDB is there. I did try to give it a nudge by doing the following, but it didn’t seem to notice.

  • Highlight the CDB in the database target list.
  • Click the “Configure” button.
  • Scroll down and click the “Sync Pluggable Databases” button.

It didn’t recognise the change, even though it was clear in the V$PDBS and CDB_PDBS views. Not sure if this is normal or if I just got unlucky. Everything looks OK now… 🙂

Cheers

Tim…

Data Guard Broker : 11g and 12c

vault-154023_640I’ve been using standby databases, on and off, since Oracle 8i. I first wrote about Data Guard for Oracle 9i. I’ve had an article on 11gR2 Data Guard for ages, but up until recently I’ve always used the manual setup.

We’ve got a project coming up that *may* use Data Guard and *may* be installed by a 3rd party, so I figured I better get up to speed with the Data Guard Broker, in case they go that route. It’s been on my list of things to look at since 10g, but I’ve never got round to it until now. 🙂

At this point, I still don’t know if the project will use 11g or 12c, so I had a play with both, which resulted in a couple of overview articles.

From the overview perspective, the usage is pretty much the same. I really only did the 11g one in case that’s the route this project goes. I didn’t bother putting the 11g one on the front page of the website, because I consider it a “backfill” article. 🙂

After having a play with the broker, I actually quite like it. It definitely feels like a simpler and neater solution than doing all the configuration manually.

Remember, this Data Guard stuff is for EE installations. If you use SE, you might want to take a look at Dbvisit, who have a product that allows you to manage standby databases for Oracle SE.

Cheers

Tim…

PS. I’m not sponsored by Dbvisit and I have no business links with them. I just think they are a great bunch of people and I like what they do.

Fedora 24 and Oracle 12c

fedoraFedora 24 was released a few days ago. As usual I had a play around with it and tried installing Oracle on it.

First comes the warnings, because people keep misunderstand why I do this. Do not install Oracle on Fedora before reading this!

With that out of the way, here’s what I did.

No real drama here. It was pretty much the same as Fedora 23 in that respect.

It’s kind-of hard to get excited about a new version of Fedora since I switched my desktop from Fedora to Mac. One thing that was interesting is the change to the upgrade process. In previous releases I used “fedup” to do it. Now it’s pretty much done using DNF (YUM). If you are interested, you can read about it here.

Cheers

Tim…

Video : Indexing JSON Data in Oracle Database 12c

Following on from last week’s post, today’s video is about indexing JSON data in Oracle Database 12c.

If videos aren’t your thing, you might want to read these articles, which the videos are based on.

The cameo in this video comes courtesy of Bertrand Drouvot, who was a silent extra in the previous video too. 🙂

Cheers

Tim…

Video : JSON Support in Oracle Database 12c

Today’s video is a sprint through some of the JSON support in Oracle Database 12c.

If videos aren’t your thing, you might want to read these instead.

The cameo in this video comes courtesy of Yves Colin, who I’ll see again in a couple of weeks at the Paris Province Oracle Meetup. A couple of extras (Bertrand Drouvot and Osama Mustafa) wanted to get in on the act too. 🙂

Cheers

Tim…

TABLE Operator with Locally Defined Types

postit-missed-itIn my OUG Ireland 2016 – Summary post I mentioned the Oren Nakdimon session called “Write Less (Code) with More (Oracle 12c New Features)”. One of the things he mentioned was the removal of restrictions associated with the use of the TABLE operator on local table types. If I had read about this or seen it before, it had certainly slipped my mind, so I made a note to write something about it and add a link to it from my PL/SQL new features article. So here it is.

It’s a neat little feature.

Cheers

Tim…

 

Oracle XE 12c?

There was a post on Oracle-L asking about Oracle Express Edition (XE) 12c. I started to write a reply, but thought a blog post may be more appropriate.

Oracle XE 12c doesn’t exist yet, but people at OpenWorld 2015 confirmed they “plan” to have one. As always, no promises. So when will it arrive? Typically the XE version is put together based on the the first major patchset of release 2 of a version. So the kind of thing you might expect is,

  • 12.2.0.1 : Released mid 2016 maybe.
  • 12.2.0.2 : Released mid 2017 maybe.
  • XE team brought together: Some time after 12.2.0.2 release.
  • XE Released: When it is built and stable.

Things to consider, based on stuff I’ve heard over the last few years.

  • There is no XE team. People are taken from their normal jobs to put this together and test it. There is no revenue generation directly associated with this product, so it’s not at the top of the priority list.
  • There are no patches for XE, so they wait until they have a stable release they can rely on for the 3-6 years before the next release 2 DB version.
  • Previous versions of XE have had bits of functionality missing/disabled, so it’s not just rolling out SE with some restrictions.
  • Other products in the Oracle stack are moving to more regular release cycles (3 months to cloud, with a yearly on-premise release). Comments from the database team suggest this is not the case for the database. As it gets more complicated, the testing takes longer, so the release cycles are getting longer. They originally said they wanted an 18 month release cycle for the database. 12cR1 took about 3 years to arrive. It looks like 12cR2 will arrive about 3 years after 12cR1. If that cycle continues, it would mean about a 6 year wait between XE releases, unless they change tack.

Of course, this is all just me thinking out loud. No facts have been presented here! 🙂

I think Oracle XE is a really important product for Oracle, even though it doesn’t directly make them money. Think of it as a gateway drug. XE makes it easy and cheap for people to try stuff with Oracle. If those projects grow, that could be additional licensing of SE2, EE or cloud subscriptions in the future. Without it, people will look elsewhere for their cheap starting point and may never make a move to Oracle later!

Cheers

Tim…

Fedora 23 and Oracle 11gR2/12cR1

A few months ago I mentioned doing some Fedora 22 installations. At the time I did some pre-emptive installations on the Alpha release of Fedora 23 also.

Now the final release of Fedora 23 is out, I’ve run through the articles again to make sure things are all ship-shape.

It’s pretty much as it was before, with the nice bonus that the “MATE Desktop” package group has been fixed. Happy days! 🙂

As always, installations of Oracle server products on Fedora are not a great idea, as explained here.

If you do like playing with this stuff, knock yourself out… 🙂

Cheers

Tim…