In this lesson, you’ll learn what Oracle and PL/SQL are, and what tools you can use to work with them.
What Is Oracle?
In the previous lesson, you learned about databases and SQL. As explained, it is the database software (DBMS or Database Management System) that actually does all the work of storing, retrieving, managing, and manipulating data. Oracle DBMS (or just “Oracle”) is a DBMS; that is, it is database software.
Oracle has been around for a long time. The first version of the DBMS was released in the 1970s, and it has been updated and improved regularly ever since. The current (as of the time this book goes to print) version of Oracle is 12c, which was released in 2013 (the “c” in 12c stands for “cloud”). Oracle is one of the most deployed and used DBMSs, especially within corporate systems and infrastructures.
Client-Server Software
DBMSs fall into two categories: shared file–based and client-server. The former (which include products such as Microsoft Access and File Maker) are designed for desktop use, and are generally not intended for use on higher-end or more critical applications (including websites and web-based applications).
Databases such as Oracle, MySQL (and its offshoot MariaDB), and Microsoft SQL Server are client-server–based databases. Client-server applications are split into two distinct parts. The server portion is a piece of software that is responsible for all data access and manipulation. This software runs on a computer called the database server.
Only the server software interacts with the data files. All requests for data, data additions and deletions, and data updates are funneled through the server software. These requests or changes come from computers running client software. The client is the piece of software with which the user interacts. If you request an alphabetical list of products, for example, the client software submits that request over the network to the server software. The server software processes the request; filters, discards, and sorts data as necessary; and sends the results back to your client software.
Note: How Many Computers Do You Need?
All this action occurs transparently to you, the user. The fact that data is stored elsewhere or that a database server is even performing all this processing for you is hidden. You never need to access the data files directly. In fact, most networks are set up so that users have no access to the data, or even the drives on which it is stored.
Why is this significant? Because to work with Oracle, you need access to both a computer running the Oracle server software and client software with which to issue commands to Oracle:
- The server software is the Oracle DBMS. You can run a locally installed copy, or you can connect to a copy running a remote server to which you have access.
- The client can be Oracle-provided tools, scripting languages (such as Python and Perl), web application development languages (such as PHP, JSP, and ASP), programming languages (such as C, C++, and Java), and more.
Note: Clients? Servers? Why Should I Care?
PL/SQL
As I noted in blog “Understanding SQL,” all SQL implementations are not created equal. This is unfortunate; it would be ideal if you could learn and write SQL for one DBMS and have it run as-is on any other. In early SQL days, this was actually more likely, but over the years DBMS vendors have needed to add features and functionality beyond that supported by standard SQL, and so they created their own variants of the SQL language.
PL/SQL stands for Procedural Language / Structured Query Language, and PL/SQL is Oracle’s implementation of SQL (and has been since Oracle version 7). The SQL you will learn in this blogs is PL/SQL, which means that it is intended for use with Oracle only. Most of what you’ll learn, especially in the earlier lessons, is quite applicable to other DBMSs.
Client Tools
As already explained, Oracle is a client-server database, and to use it, you’ll need client software (the program you use to actually run SQL commands). Lots of options exist in regard to client software, but you should be aware of these two Oracle options specifically:
- All Oracle server installations include a command-line tool called SQL*Plus. This basic client simply displays a SQL> prompt in a text window, allowing you to enter commands and instructions to the Oracle server.
- Oracle also provides a free graphical client called Oracle SQL Developer (it might show up named just “SQL Developer” when you install it on your computer). Oracle SQL Developer lets you interactively connect to and use your Oracle server and is a much better option for daily Oracle use, especially for beginners.
Although you are free to use any client tool you want (the PL/SQL you use will always be the same regardless of client tool), I highly recommend using Oracle SQL Developer as your first tool, and the instructions in this book assume that you are doing just that.
Getting Set Up
As you now know, to start using Oracle, and to follow along with the lessons in this book, you need access to an Oracle DBMS (or “Oracle Server”) and client applications (software used to access the server).
What Software Do You Need?
You do not need your own installed Oracle server, but you do need access to one. You basically have two options:
- Access to an existing Oracle DBMS, perhaps one by your hosting company or place of business or school. To use this server, you will be granted a server account (a login name and password).
- You may download and install your own copy of Oracle for installation on your own computer. Oracle runs on major platforms including Windows and Linux, but no longer on Mac OS. However, that does not mean that Mac users can’t learn and use Oracle PL/SQL.
Note: Important Note for Mac Users
Tip: If You Can, Install a Local Server
If you will be using an existing hosted Oracle server, then you don’t need to worry about what version it is, because just about everything you’ll learn in this book works with all versions (and if version-specific issues exist that you need to be aware of, I point them out along the way).
If you want to install your own server, then you have two choices:
- You can install a complete Oracle server installation. The current version (as of when this book is going to print) is Oracle 12c, and you can install that or any prior version. Oracle server is commercial software, and so although you can download and install it without buying a license, you need to purchase a license for ongoing use. When you install Oracle server, it presents you with lots (and I do mean lots) of configuration options that you can use to control exactly what gets installed and how it is configured.
- You can also download and install Oracle Database Express Edition (also called Oracle Database XE), a free version of Oracle server that has some important limitations, none of which will impact the lessons in this book. Installing Oracle Database Express Edition is quick and painless, and the PL/SQL you’ll learn and use applies to all versions of Oracle server.
Tip: Oracle Database Express Edition Is Recommended
Obtaining the Software
To learn more about Oracle, go to http://oracle.com/.
To download a copy of the server, go to the Oracle website and click on the Download link. Lots of options present themselves, but the ones that you are interested in are the following:
- In the Database section, select Oracle Database for a full Oracle server installation, or the Express Edition.
- In the Developer Tools section, select SQL Developer for the client tool.
- Oracle does require the creation of an Oracle account to download any software, so if you don’t have an existing account, the site prompts you to create one.
Note: Where Is Oracle SQL Developer?
Installing the Software
If you are installing a local Oracle server, do so before installing any other clients or utilities.
Exact installation steps for a full Oracle server are beyond the scope of this book, and if you need help with an installation, you should refer to documentation on the Oracle website.
Installation of Oracle Express Edition involves the following:
- Depending on your operating system, you might need to expand the download file to uncompress it.
- Run the setup program.
- Accept the license agreement.
- You can leave all questions and prompts with their default values.
- The programs asks you to provide a database password; enter one and remember what it is!
- Then just let the installer do its thing.
Regardless of whether or not you install a local server, you’ll want a local copy of Oracle SQL Developer. If one was not installed (if and) when you installed the Oracle server, do the following:
Depending on your operating system, you might need to expand the download file to uncompress it.
- Run the setup program.
- Accept the license agreement.
- You can leave all questions and prompts with their default values.
- Then just let the installer do its thing.
As long as the software installs correctly, you’re ready to move on to Lesson 3, “Working with Oracle.”
Summary
You now know what Oracle is, what PL/SQL is, and what software you need to proceed. You should also have access to an Oracle server (local or remote), and have client software installed and ready to use. In Lesson 3, I’ll show you how to log in and log out of the server, and how to execute commands. The lessons in this book all use real SQL statements and real data, and so I will also walk you through creating and populating the example database tables.
