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.

10 thoughts on “Learning A Little Oracle

  • Dave Poole

    I’m finding it’s the similarities that trip me up between DB platforms rather than the obvious differences.
    Take Postgres for example. Roles are at the instance level, there is no concept of a DB specific role.
    Stored procedures don’t have implicit permission to use underlying obects unless you create them as security definer procs.
    Security definer procs need handling with care because Postgres does a “Hold my Beer” to the SQL Injection Attack.
    Stored procs wrap their functionality up in a transaction whether you want one or not

    There’s lots to love about Postgres so these parallel universe things don’t put me off

  • It’s painful to get out of the great environment coming along with SQL Server. Recently had to work with MariaDB and alter a column to document it ?! It may lock the table to add a comment… where is sp_addextendedproperty?

    Did you find something in PL/SQL yet, you thing you’re gonna miss back in SQL Server ?

  • Ah, I envy your versatility and flexibilty. I grew old working with Oracle databases from 1995 – 2018. Now I’m in transition to SQL Server DBA … and the effort to transition from one syntax to another is just murder. It’s a personal problem, I know. But I can sympathize with those who have the reciprocal route.

    Good luck with it!

    • Keep going. I think the biggest complaint for people going from Oracle to SQL Server is that they can’t believe things are as simple as they actually are. Conversely, that we don’t have quite as many knobs for tweaking stuff.

  • Marcus Bacon

    Did you consider Oracle’s always free cloud database? Very easy to set up and comes with SQL Developer Web for doing some of the simple things it sounds like you are trying to do. Everything is in the cloud so no installation at all. It just works. I see nothing wrong with your FK creation scripts if microphones already exist unless microphones.microphoneid is not a primary or unique key. A unique index is not the same as a UK, although a UK uses a unique index.

    There are times I wish I could merge Oracle and SQL Server, taking the best of both. They continue to copy features from each other. I have a lot of years working in Oracle and MS SQL Server and I frequently find that I wish the feature of one was in the other. SQL (as in Structured Query Language) is actually the same most of the time if you stick to the SQL standard. There are some syntactical differences but a quick web search helps figure that out.

    It doesn’t sound like you will be needing PL/SQL

    • I know I’m never going to be a deep expert on this technology. I just want to get the basics working. So far, so good. I even already completed a demo on the tool, and it all worked. Huzzah!

  • Chris

    another tool i can recommend after 10+ years of usage is PL/SQL Developer from All Around Automation. It’s not free like Oracle’s Sql Developer tool but the license is very inexpensive. (No, i dont work there). It does 99% of what i want a GUI to do for Oracle. I cant speak to GIT integration though.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.