How to Download, Install, and Use Oracle
Since knowing Oracle SQL is a prerequisite for learning PL/SQL, I could simply assume that you already have resource-level access to Oracle (you can create objects like tables, views, and so on), already have access to SQL*Plus, and already know SQL. But that might not be the case.
Just in case you don’t have access to Oracle, I’m going to give you some advice on the following subjects:
- How to download Oracle Database software
- How to install Oracle Database software
- How to use Oracle SQL*Plus
This should get you started in the right direction. Keep in mind that I have no idea what the Apress and Oracle web sites or the Oracle software will look like by the time you read this. Nothing may have changed, but I kind of doubt that. It’s more likely that everything will have changed. So my directions will probably no longer be correct, but I think some direction is better than none.
How to Download Oracle Database Software
At the time of this writing, you can download a trial copy of Oracle Database software from Oracle’s web site. You can use this trial software for learning purposes only. If you decide to do something commercial with it, like write software that you can then sell, then you’ll need to pay for a license. Keep that in mind. Play for free, but earn a fee and you must pay. You should read the license agreement so you know exactly what you can do.
The URL you use to download trial software at Oracle changes all the time. Try www.oracle.com/technetwork/index.html. When you get to that web page, as in the example in Figure A-1, look for a menu item that says Downloads.
Figure A-1. An example of an Oracle Technology Network web page
When you find the Downloads link, click it. When you do, a web page like the one in Figure A-2 may appear. I say “may appear” because who knows how this web site will be organized in the future.
Figure A-2. An example of an Oracle Downloads menu
On the Oracle Software Downloads menu, click the Oracle Database option. When you do, a web page like the one in Figure A-3 may appear.
Figure A-3. An example of an Oracle Database Software Downloads web page
On the Oracle Database Software Downloads page, first click the Accept License Agreement radio button. Next, find the latest version of the database software (currently Oracle Database 12c) and click the link for File 1. A file download dialog like the one in Figure A-4 may appear.
Figure A-4. An example of a Firefox File Download dialog
Write down the name of the archive so you know it when you go to install the software. Click the OK button in the File Download dialog box. When you do, the Oracle Single Sign On web page will appear, as in Figure A-5.
Figure A-5. An example of Oracle’s Single Sign On web page
At this point, if you already have an account, sign in, and then Firefox will save the file in your Downloads directory. If you don’t have an Oracle Technet account, you’ll need to create one. Just click the Create Account button, and then return to this page to sign in and continue the download.
Once the download is complete, return to the Oracle Database Downloads web page and repeat the process for File2, as shown in Figure A-6.
Figure A-6. An example of a Firefox file download dialog for file 2
Once again, write down the name of the archive so you know it when you go to install the software. Click the OK button in the File Download dialog box.
After both files have been downloaded, you can move on to the next step, which is to install the Oracle Database software. But first, I suggest you go back to the Downloads link web page and download the installation instructions. Read them before you start the installation.
How to Install Oracle Database Software
I’m going to show you an overview of installing the Oracle database software on a Windows machine. On a computer running the Windows OS, here are the steps.
- Unzip the archives in the Downloads directory.
- Navigate to the directory where you unzipped the files with Windows Explorer, and then run setup.exe in the database directory.
- Follow the instructions on the installation screens, answering any prompts as required.
The following screen shots are from an installation I made on one of my desktop PCs called windows8-pc. It’s important that you name your machine (in the OS) before you install Oracle because this is the name that will be used to specify the machine’s network address throughout the installation process.
When you execute setup.exe, the Oracle Universal Installer will start and display the Configure Security Updates screen, as shown in Figure A-7. Specify your email address and uncheck “I wish to receive security updates via My Oracle Support.”
Figure A-7. An example of the first Oracle Universal Installer screen
Click Next and the Installation Option screen will appear, as shown in Figure A-8. Here, I suggest you specify “Create and conFigure 1 database.”
Figure A-8. An example of the second Oracle Universal Installer screen
Click Next and the System Class screen may appear, as shown in Figure A-9. Select Desktop class.
Figure A-9. An example of the Oracle Univeral Installer’s Prerequisites screen
Click Next and the Oracle Home User Selection screen may appear, as in Figure A-10. Select Use Windows Built-in Account.
Figure A-10. An example of the Oracle Universal Installer’s Oracle Home User Selection screen
Click Next and the Typical Installation Configuration screen may appear, as in Figure A-11. Here I suggest you specify the following:
- An Oracle base of C:oracle
- A Character set of Unicode (AL32UTF8)
- A password that will be used by the SYSTEM account
- Uncheck the Create as Container Database box
Figure A-11. An example of the Oracle Universal Installer’s Typical Installation Configuration screen
Click Next and the Summary screen may appear, as in Figure A-12.
Figure A-12. An example of the Oracle Universal Installer’s Summary screen
Assuming that the summary is correct, you should click the Install button. Clicking the Install button may display the Install screen, as shown in Figure A-13. This is where you can get up and get a cup of coffee (joe).
Figure A-13. An example of the Oracle Universal Installer’s Install Product screen
After some annoying period of time, the Database Configuration Assistant screen may display, as shown in Figure A-14.
Figure A-14. An example of the Oracle Universal Installer’s Configuration Assitant screen
Write down the URLs on this screen. You will need them!
You’re on the home stretch now! After you click the OK button to dismiss the summary screen, the Finish screen may appear, as shown in Figure A-15.
Figure A-15. An example of the Oracle Universal Installer’s Finish screen
Click Exit to complete the installation. At this point, Oracle should be installed, and up and running on your computer. Congratulations!
To manage Oracle from this point forward, access the Enterprice Manager from your browser using the URL displayed on Figure A-14 of your installation, which is typically https://localhost:5500/em.
Now you need to open SQL*Plus to create a resource-enabled account.
How to Use SQL*Plus
You are going to be using SQL*Plus as your primary user interface to the Oracle database. With it, you will do the following:
- Create two usernames to use when doing the assignments in this book: rps and ops.
- Execute Data Definition Language (DDL) in order to create tables, indexes, views, types, and stored procedures.
- Execute Data Manipulation Language (DML) in order to insert into, update, delete from, and select data from tables in the database.
To get started, create a directory named ps on your file system. You’ll download this book’s source code and solutions to that directory.
How to Download This Book’s Source Code
To download the source code for this book, go to the Apress web site at www.apress.com, which may look something like Figure A-16. Click the Source Code menu item, and then search for and download the source code for this book.
Figure A-16. An example of the Apress web site’s home page
Next, decompress the archive (unzip it) into a ops directory.
Now that you have the book’s source code in place, you can create a shortcut to SQL*Plus in directory opsAppendix.
- Navigate to directory Appendix using Windows Explorer.
- Right-click any empty part of the directory’s listing window.
- Select New ~TRA Shortcut.
- Specify the location of the SQL*Plus executable, sqlplus.exe, and then click the Next button. The SQL*Plus executable should be in the “Oracle Home” BIN directory, such as c:oracleproduct12.1.0dbhome_1BINsqlplus.exe.
- Specify the name for the shortcut. I name the shortcut after the database, so I suggest orcl. Click the Finish button.
- Right-click your new shortcut and select Properties.
- Delete the entry for the Start-in directory, then type in one space character, as in Figure A-17, and then click the OK button. This will make SQL*Plus use the directory where the shortcut exists as the default source directory when running a script, and that will make your life a lot easier.
Figure A-17. An example of a shortcut’s Properties dialog box for SQL*Plus
At this point, you should have a SQL*Plus shortcut icon in directory opsAppendix. Double-click the icon to start SQL*Plus. When you do, SQL*Plus will display a Login dialog box, where you specify the following:
- The username SYSTEM
- The password you decided to use
- Host string orcl, or whatever you decided to name the database
After you specify the login information, SQL*Plus should log you in to the database as user SYSTEM. If you see a screen like the one shown in Figure A-18, you have successfully logged in to your Oracle database.
Figure A-18. An example of a SQL*Plus screen and the window’s Properties screen
The reason you create the shortcut in the same directory that contains the example source code files is to give yourself easy access to those files. When you invoke SQL*Plus using the shortcut that you’ve just created, the source code directory becomes the default directory that SQL*Plus will search when you invoke a SQL script.
If you click the icon in the upper left hand corner, the window’s Properties screen will appear, as in Figure A-18. Here I suggest you specify the following:
- A screen buffer size width of 132
- A screen buffer size height of 9999
- A window size width of 132
- A window size height of at least 50
The click OK to save your new shortcut’s windows property settings. The next time you open SQL*Plus the window will be larger by default. This will also make your life easier.
How to Create a New Username
I suggest you create two usernames, ops and rps, just for working with the examples and doing the exercises in this book. To do that, type the following at the SQL*Plus prompt (SQL>), and then press the Enter key:
SQL> @create_users.sql
This will execute SQL*Plus script create_users.sql in directory opsAppendix, which will create two new usernames (ops with password ops, and rps with password rps) that you can use while learning from this book. The next time your log in to your database, use username rps.
How to Write a SQL*Plus Script
You may type a command directly into SQL*Plus. However, I don’t recommend this if you’re programming. I think you should write a SQL*Plus script so you can make corrections to your code as needed, and then execute the code again without needing to retype it. Having your code in a script file will allow you to do just that.
You may use any text editor you like—Notepad++, WordPad, TextPad, SQLDeveloper, and so on—but you can’t use Word. (I prefer to use TextPad when I edit my SQL or PL/SQL scripts. You can search for TextPad on the Internet, and download a trial copy.)
To create a SQL*Plus script, follow these steps.
- Open a new text file.
- Type one or more SQL or PL/SQL commands using the correct syntax.
- Save the script to a directory that is accessible by SQL*Plus.
Once you’ve created a script, you can run it whenever you like, and your commands will not be lost. They will forever reside in your script file. So how do you execute a SQL*Plus script?
How to Execute a SQL*Plus Script
I had you execute a SQL*Plus script earlier in order to create usernames ops and rps for this book. To execute a SQL*Plus script, you type an at sign character (@) followed by the name of the script to execute. SQL*Plus expects this script to exist in the SQL*Plus startup directory.
If the script isn’t in the startup directory, you must specify the entire path and filename. If the path or filename has space characters in it, you need to enclose the path and filename in double quote characters ("). Here’s an example:
SQL> @"c:Program FilesNot a convenient locationmy script.sql"
So I suggest you be lazy and create a shortcut with a blank (space) “start in” directory in every directory where you have scripts. This way, you can start a SQL*Plus session with the default directory set to where your script files exist.
How to Describe Your Tables and Stored Procedures
While you’re in SQL*Plus, you can use the describe command to display the definition of a table or stored procedure. For example, you can type the following to get a definition for view SYS.USER_OBJECTS:
SQL> desc SYS.USER_OBJECTS
SQL*Plus will respond with the following:
Name Null? Type
----------------------------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
However, SQL*Plus has this annoying feature: it aligns the right portion of a table description to the right side of the line width, and that usually prevents you from seeing the Type, so I use a script, desc.sql, to describe a table or stored procedure instead. For example, to describe utility package SYS.DBMS_OUTPUT, I type the following at the SQL*Plus prompt (SQL>):
SQL> @desc SYS.DBMS_OUTPUT
SQL*Plus will respond with the following:
SYS.DBMS_OUTPUT
SQL> @desc SYS.DBMS_OUTPUT
SYS.DBMS_OUTPUT
PROCEDURE DISABLE
PROCEDURE ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE GET_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE GET_LINES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINES TABLE OF VARCHAR2(32767) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE GET_LINES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINES DBMSOUTPUT_LINESARRAY OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
This description looks different from the first one, not because I used @desc.sql, but because the described object is a stored procedure, not a view. All that @desc.sql does for you is set the SQL*Plus linesize property to 80, use the describe command, and then return the linesize to its prior length.
At this point, you should know how to do the following:
- Download Oracle
- Install Oracle
- Create usernames ops and rps
- Write a SQL*Plus script
- Execute a SQL*Plus script
- Describe a table or stored procedure
So now it’s time for you to get started with Chapter 1. I wish you good skill on your journey. Feel free to contact me if you have any questions about the book.