Working with Oracle from PL/SQL: connect, login & execute code

In this lesson, you learn how to connect and log in to Oracle, how to issue PL/SQL SQL statements, and how to create and populate the example tables that we’ll be using throughout this book.

Creating a Working Environment

Now that you have access to Oracle and client software to use with it, the next step is to create a working environment. Database servers, like your Oracle server, are usually used by lots of different users and applications. Imagine what would happen if a user created a table called customers to store customer data, and another user tried to create a table of the same name. Users could overwrite each other’s data; they could access incorrect information—you get the idea. In multi-user environments, and DBMSs are designed to be exactly that, this type of contention is a real concern. And so when working in client-server databases, it’s important for each user to have a private safe workspace. Back to our example, by having this workspace, one user’s customers table doesn’t interfere with another user’s table of the same name.

Many different ways exist to create safe, isolated work environments. If you are using an existing Oracle server, perhaps a corporate database, then the database administrator will likely give you your own login and workspace, and when you log in to Oracle, you’ll be in the right safe workspace automatically. If this is the case, you can jump ahead to the later section, “Making the Connection.”

If, however, you are using your own Oracle server, then you’ll need to do this for yourself.

Caution: Not Required, But Highly Recommended

Creating a Dedicated Oracle Instance

The best way to create a safe work environment is to create a dedicated instance of Oracle for yourself. You can think of it as Oracle allowing multiple copies of itself to be run on a single server, each one isolated from another copy. Each server is referred to as an instance, and each instance has a unique name.

Note: Are You Using Oracle Express Edition?

To create a dedicated Oracle instance for use with this book, do the following:

1. Run the Oracle installed application named Database Configur-ation Assistant; this is used to create (as well as update and delete) database instances.
2. When the application launches, select the first option, Create a Database, and then click the Next button.
3. You may be asked to select a Database Template. If so, select General Purpose and then click the Next button.
4. Every database must be uniquely named. In production environments, database names are carefully managed and are usually in the form organization.domain.database. However, to keep things simple, enter crashcourse as the Global Database Name and as the SID (the System Identifier), and then click the Next button.
5. If you are asked about Enterprise Manager, leave the default settings, and click the Next button.
6. You’ll then be asked for passwords for important management accounts. You can enter a unique password for each account, or, as this is a non-mission-critical database instance, check the Use the Same Administrative Password option and provide a password. Remember this password; without it, you cannot access your new database instance. Click Next.
7. When asked about database storage locations and templates, leave the default settings, and click the Next button.
8. When asked about recovery options, once again, leave the default settings, and click the Next button.
9. Oracle can install sample tables and data in your new instance. We do not need this because we are going to use our own example data. So, when asked about Sample Schemas, make sure the box is not checked, and then click the Next button.
10. When asked about memory, sizing, character sets, and connections, leave the default settings, and click the Next button.
11. Eventually you’ll be asked whether you want to create a database, create a database template, or generate database creation scripts. The only option you want checked is Create Database.
12. Click the Next button, and when prompted for confirmation, click the OK button.

The Oracle Database Configuration Assistant now creates your new crashcourse database instance.

Note: If You See Warning Messages

After your database instance has been created, you can jump to the later section, “Making the Connection.”

Creating a Custom Workspace

If you are using Oracle Express Edition, you won’t be able to create your own database instance. So instead, create a user-specific workspace within the existing database instance.

Here’s what you need to do:

1. Oracle Express Edition is managed via an embedded web server. The Oracle Express Edition installer created a link named Get Started with Oracle Express Edition. Click the link, and a web browser opens displaying a web page with options to manage Storage, Sessions, and more. If you are prompted to log in, use the login name SYSTEM and the password you provided at installation time.
2. Click on the red Application Express button. You’ll be presented with a web page that can be used to create an application workspace.
3. Make sure Create New is selected.
4. For Database Username, type crashcourse.
5. For Application Express Username, type crashcourse (or use your own name).
6. Enter a password of your choice and confirm it.
7. Click the red Create Workspace button.
8. You should see a prompt telling you that the workspace was successfully created, and allowing you to log in. Click on the login option. If you don’t see the prompt, just click on the Application Express button again, and this time click on the Already have an account? Login Here button.
9. When prompted for Workspace, enter crashcourse; for Username, enter the username from step 5, and enter the Password you selected.
10. Click the Login button.

You should see a new screen with options for Application Builder, SQL Workshop, and more. If this is the case, you’re ready to proceed.

Tip: You Can Use Application Express

After your workspace has been created, you can jump to the following “Making the Connection” section.

Making the Connection

Oracle, like all client-server DBMSs, requires that you login to the DBMS before being able to issue commands. Login names might not be the same as your network login name (assuming that you are using a network); Oracle maintains its own list of users internally, and associates rights with each. For a database client, like Oracle SQL Developer, to connect to the Oracle server (even a local Oracle server), you must tell it where to find the server, and how to log in.

To connect Oracle SQL Developer to the Oracle server, follow these steps:

1. Run Oracle SQL Developer.
2. When the application loads, you see a screen split into three sections, with menus and an icon toolbar on top. The top-left window is the Connections window, and it lists any defined Oracle servers (of which there will currently be none).
3. At the top of the Connections window, click on the green + button to add a new database connection.
4. For Connection Name, enter crashcourse. (The name does not have to match the database, workspace, login, or anything else; this is just the name that Oracle SQL Developer uses to identify each defined connection.)
5. For Username and Password, enter what you specified when you created the database instance or workspace previously.
6. Check the Save Password checkbox so that you don’t have to keep entering the password.
7. For Hostname, enter localhost if you are using your own local Oracle server. If you are using a remote or hosted server, enter the hostname given to you by that server’s administrator.
8. SID is the database instance id. If you are using a local Oracle server and followed the preceding steps, then this is crashcourse if you have a dedicated Oracle instance, or xe if you are using your own workspace with the single Oracle Express Edition instance.
9. Click the Test button.
10. At the bottom left of the dialog box, you should see Status : Success. If that is displayed, click the Save button to save this new connection. If an error message is displayed, check the form fields and correct the error before proceeding.

After you save the new crashcourse connection, you should see it listed in the Connections window on the top right. You can now close the dialog box.

Note: Using Other Clients

A Quick Introduction to Oracle SQL Developer

You’ll be using Oracle SQL Developer extensively as you learn PL/SQL, so it’s worth taking a moment to familiarize yourself with this tool:

Note: Just the Basics

Click the + next to your new crashcourse connection to expand it. This shows you all tables, views, and much more. You can further expand into each menu option, and can right-click on each to edit and more.

The most important part of the screen is the large area on the right. This is where you enter your PL/SQL statements and display results (if there are any).

When you open a connection, a worksheet should automatically open for you to start entering SQL. If it does not, or if you want multiple worksheets open, you can click on the SQL Worksheet button in the application toolbar (it’s the one with a green icon and the word SQL in black on white in front of it). Click OK and you’ll have another worksheet to use.

Let’s give it a try. Enter the following PL/SQL in the Worksheet screen. (Don’t worry if the code doesn’t make sense; it will within the next few lessons.)

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM DUAL;

Click the Run Script button (it’s the one with a green arrow on top of a document; it should be the second from the left in the toolbar above the Worksheet window) to execute the SQL statement. You should see the system date and time displayed in a Query Result screen below.

Lastly, when the screens get cluttered, click on the Clear button (the one with the picture of a pencil eraser) above either screen to clear the contents.

Execute

SQL developers often use the terms execute and run interchangeably; both mean actually running the SQL.

Note: Run Statement vs. Run Script

Tip: Using Multiple Worksheets

With that, you’re ready to run some important SQL scripts—the ones used to create and populate the example tables that you’ll be using in future lessons.

Creating and Populating the Example Tables

The tables used throughout this book are part of an order entry system used by an imaginary distributor of paraphernalia that might be needed by your favorite cartoon characters (yes, cartoon characters; no one said that learning Oracle had to be boring). The tables are used to perform several tasks, including:

• Manage vendors
• Manage product catalogs
• Manage customer lists
• Enter customer orders

Making this all work requires six tables that are closely interconnected as part of a relational database design. Here are the tables we’ll be using:

• The customers table contains your customers.
• The orders table contains one row per order placed.
• The orderitems table contains the details for each item in an order (the orders in the orders table).
• The products table lists all available products from all vendors.
• The productnotes table contains notes pertaining to products.
• The vendors table contains product vendors.

These six tables contain multiple columns, and are all connected using foreign keys.

Note: Simplified Examples

Obtaining the Same Table Scripts

To follow along with the examples, you need a set of populated tables. Everything you need to get up and running can be found on this this link.

The web page contains a ZIP file that you should download. Inside it are two SQL script files:

• create.sql contains the PL/SQL statements to create the six database tables (including defining all primary keys and foreign key constraints).
• populate.sql contains the PL/SQL INSERT statements used to populate these tables with sample data.

Note: For Oracle Only

After you have downloaded the scripts, you can use them to create and populate the tables needed to follow along with the lessons in this book.

Note: Create, Then Populate

Create the Tables

Database tables are created using the SQL statement CREATE TABLE, but rather than have you type that all out, you can use the create.sql file that you downloaded.

1. Make sure Oracle SQL Developer is open and the crashcourse connection is open.
2. Use the Open button (it has a picture of a yellow folder) or choose File, Open to open create.sql. The contents of create.sql appear in a new worksheet.
3. Because you could be working with multiple database connections, you need to tell Oracle SQL Developer to use the crashcourse connection. From the drop-down box at the top right above the Worksheet screen, select crashcourse.
4. Click the Run Script button (once again, it’s the one above the Worksheet screen; a green arrow over a document). You should then see the following output:

Output

table CUSTOMERS created.
table ORDERITEMS created.
table ORDERS created.
table PRODUCTS created.
table VENDORS created.
table PRODUCTNOTES created.
table CUSTOMERS altered.
table ORDERITEMS altered.
table ORDERS altered.
table PRODUCTS altered.
table VENDORS altered.
table PRODUCTNOTES altered.
table ORDERITEMS altered.
table ORDERITEMS altered.
table ORDERS altered.
table PRODUCTS altered.
table PRODUCTNOTES altered.

The preceding tells you that six tables were created, and that they were then also altered (we do this to add primary and foreign keys). Now that you have tables, let’s populate them.

Populate the Tables

Data is inserted into a table using the INSERT statement. Once again, rather than typing hundreds of lines of SQL, we’ll use the downloaded file instead.

1. Use the Open button (or choose File, Open) to open populate.sql. The contents of populate.sql appear in a new worksheet.
2. Make sure that crashcourse is selected in the drop-down box at the top right above the Worksheet screen.
3. Click the Run Script button (once again, it’s the one above the Worksheet screen; a green arrow over a document). You should then see the following message appear 55 times (once for each row inserted):

Output

1 rows inserted.

You now have the tables and data you need to proceed.

One More Look at Oracle SQL Developer

Before finishing this lesson, I want to point out one more invaluable feature of Oracle SQL Developer. Now that you have created and populated the example tables, try the following:

1. Locate the crashcourse database connection in the Connections screen, and click + to expand it.
2. The first item displayed is b; click + to expand that.
3. Scroll through the list of tables to find one that we just created. The first one you’ll see is customers, but any of our tables will do.
4. Click the + to expand the table, and see the table column names.

In addition, Oracle SQL Developer opens a new tab in the main Worksheet area and the table columns (showing details about type, nullable, and more). Above the data are other tabs that you can click on - Data shows the contents of the table, Constraints lists primary and foreign keys and any other defined constraints, Details lists all sorts of information about the table and how it is being used, SQL lists SQL code that could be used to create the table, and so on. Feel free to browse around; this data and view are useful for working with Oracle.

Summary

In this lesson, you learned how to connect and log in to Oracle, and how to enter and execute SQL statements. You also created and populated the example tables. Armed with this knowledge, you can now dig in to the all-important SELECT statement.

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog... 1741 views Андрей Волков Wed, 12 Sep 2018, 15:23:39

PL/SQL package: Collecting Rel... 1389 views sepia Sat, 01 Dec 2018, 10:54:57

Why Oracle DBAs learn PL/SQL a... 1656 views Андрей Волков Wed, 12 Sep 2018, 14:43:12

A Brief History of SQL 5401 views Александров Попков Wed, 17 Oct 2018, 15:04:29