Learning A Little Oracle

As part of my job, I’ve been tasked with doing some of my work in Oracle, so I’m learning Oracle. Allow me to share a little of my pain as I explore a space I’ve only ever dabbled in.

Getting Started in Oracle

Back in the day, in order to get started with Oracle, you begin at the Oracle web site. There, you download an installation, after getting a license (or while, whatever). In our modern era, this is the hard way to get stuff done.

The first place I went was Azure. There is excellent Oracle support on the Azure platform and, best of all for me, it’s really easy to fire up an Oracle VM. I had a server up and running in no time. Win!

Another way to accomplish this task is using containers. I have access to a service (man, I wish I could share more about it, but it’s still under development, so…) where I can spin up an Oracle container in a matter of minutes. I’ve been using this extensively. There are other ways to get Oracle containers up & running in Docker. Although, to be fair, there’s a lot of licensing stuff you have to hop through to make this work.

While normally an Oracle install is a somewhat convoluted and difficult proposition. With modern tooling, it’s a dream. No, the hard part wasn’t getting an Oracle server or a database. The hard part was finding a way to easily run PL/SQL commands. Emphasis on “easy”.

A Client for PL/SQL

Here is where I struggled, a lot. My goals were simple. I want a minimal installation. I don’t want to get into lots of crazy stuff. I’m not looking for a full blown GUI. I just need to be able to easily edit my PL/SQL, save it to files, and then have a way to execute the scripts. If the tool could also integrate with Git, all the better. Also, because I’m horribly uneducated in Oracle, I did want some of the heavier lifting to be done for me, meaning, yeah, a little bit of a GUI is desired.

So, I first went to the source and got a copy of Oracle’s SQL Developer. It worked flawlessly right out of the gate. Story over. What are you whinging about Grant?

Well, it worked, for a time. Then it stopped working. First thing I loved about the Oracle SQL Developer tool was, I didn’t have to run an install. After registering with Oracle Corp., I downloaded a copy. It simply executes from where it is, no install needed. Fantastic… until it stopped working. No, I don’t know why. However, I do know that I downloaded a new copy and that one worked great… until it too stopped working, completely different errors. Now, it just fails to load. I’m finished with that tool. I want to get stuff done, not troubleshoot buggy software.

Yes, there’s Toad. Quest Software. That’s all I have to say about that.

Feeling a little lost, I finally looked to a tool that I’m using pretty much all the time these days, Visual Studio Code. Sure enough, there’s an Oracle extension. In fact, there are several different Oracle extensions, but I focused on the one from Oracle Corp.

Ta-Da!

OK. Not perfectly ta-da. A little behavioral issue gets me regularly. Let’s say I connect up to a database in Oracle. Great. You get a little explorer window so you can look at the objects. It works fine. Right click on a database, select “New SQL Query” and you’ve got a PL/SQL window (with some built-in Intellisense, so, win). However, it’s not automatically connected to the database, so you have to get in the habit of using your handy catch-all shortcut key, CTL-SHIFT-P. Then you can find “Oracle:Connect” to make the connection. Not only does this now work, but, best of all, it outputs to a separate tab. This is a great way to run queries if you have to demo lots of stuff (the only reason I’m in Oracle).

Right. I’m off and running… well, walking carefully because there’s one other, much smaller, struggle: PL/SQL

Learning Oracle SQL

Here’s the good news. PL/SQL is, at it’s root, SQL. This means, the work I’ve put in learning T-SQL and PostgreSQL and MySQL applies pretty much directly. CREATE TABLE works mostly the same. Right down to naming constraints and creating sequences, it’s easy:

CREATE TABLE radios
(
radioid number(10) GENERATED ALWAYS AS IDENTITY NOT NULL,
radioname varchar2(50) NOT NULL,
CONSTRAINT radiospk PRIMARY KEY(radioid)
);

Nah, the trick was, and remains, I still haven’t solved it, figuring out how to group some commands. For example, I wanted to have a script that added a column and then made a foreign key to that column. Something like this:

ALTER TABLE RADIOS 
ADD microphoneid NUMBER(10);        

ALTER TABLE RADIOS 
ADD CONSTRAINT radiosfkmicrophones FOREIGN KEY (microphoneid)
        REFERENCES microphones(microphoneid);

If I did the same thing with several CREATE TABLE statements, it just worked. This, always generates an error. I’m still doping out why.

However, that’s the minor pain you’d encounter moving from any database system you know to one you don’t. I have confidence I’ll solve this shortly (or, no doubt several of you will provide me with an answer).

Conclusion

The simple fact is, doing really simple things across database systems is, in fact, simple. Did I use the word enough in one sentence? Seriously though, the pain involved is fairly light. Modern tooling makes everything so much easier. This is especially true if we’re just experimenting or learning. Yes, if I wanted to actually teach Oracle, as opposed to teaching my tools using an Oracle back end, I need, at minimum, months more learning. I want bare bones functionality. I achieve that in pretty short order thanks to our modern tools.

No. This is not becoming an Oracle blog. No. I’m not planning on learning tons more about Oracle beyond the basics. Yes. I will continue to post my more traditional stuff on Azure, SQL Server, DevOps and AWS.