Learning to Query in Oracle XE

Querying the database is one of the most common tasks that you may have to carry out routinely. This tutorial shows you the options for running SQL queries and how to run the queries against the database in the Oracle Express Edition, Oracle XE for short.

Introduction

In order to get information about logging in and out of Oracle XE, please review my first article which gives you an overview of Oracle XE.

You can access the login page by going from the shortcut Go To Database Home Page… shown in the next picture.

This opens up the login page shown in the next picture. It is assumed that you can log in to the HR database, which has a couple of tables. If you have not reviewed the previous article, you logged in as System with the password you gave yourself and then unlocked the HR database; otherwise you will not have permission to access the HR database. Please review the previous article. 

This takes you to the HR database where the user hr has logged in as shown here, showing all the available objects. Since you will be looking at SQL,  click on the icon SQL. If you click on the drop-down you will get a menu from which you may have to choose.

 

Clicking on the icon  opens three more icons, all related to SQL. They are
SQL Command, SQL Scripts, and Query Builder.

In this tutorial you will look at using the SQL Commands. Click on the icon SQL Commands, or on the drop down.

 

When you click on the icon you get the next screen as shown. In this window you will be typing in the SQL and displaying the results of your SQL Query. In the upper pane you type in your SQL statement and in the bottom, tabbed pane you get the results. The Results tab is the default. Once you get the results you can look at several related facts, as shown in this picture, by tabbing the various items.

{mospagebreak title=Running SQL Queries}

Example 1

In the HR database there are a couple of tables, which include DEPARTMENTS and Employees. Here is the table design of the Employees table as seen in the Object Browser (review the first article).

Select query against the Employees table

Let’s just run a query to return everything in the Employees table with this simple query Select * from employees. This query can be saved or executed by clicking the Run button. The results are seen in this next picture. Since we have chosen to display 10 rows of results, the Results pane shows just the 10 rows. If you want you may show more rows by clicking on the drop-down, Display. As you can see from the design view there are more columns in this table than shown in this screen. You could scroll to the right, and see the rest of the columns.  Once the query is run you may find other details. The other details are accessed by clicking on the tabs in the bottom, tabbed window.

Explain Tab

Click on the Explain tab in the bottom pane. This brings up the next screen which shows the Query Plan, the indexes on columns, and details of the table columns. This is really nice without having to move to a new window.

Describe Tab

Next you click on the Describe tab, and you will get a message which asks you to type the PL/SQL statement “Describe.” “Describe” (or just Desc) is a keyword in Oracle’s SQL with the following Oracle syntax:

DESC[RIBE] {[schema.]object[@db_link]}

Lists the column definitions for the specified table, 
view or synonym, or the specifications for the 
specified function or procedure.

Typing in the statement Describe Employees produces the result in the next screen. You will get complete design information on the table. From this tab you cannot go back to the Explain tab because the context has changed. You need to run the query once again to go back to what you interrupted by typing in the “Describe Employees” statement. Of course you could have saved the query before running the “Describe employees” query, which allows you to come back to any of your saved queries, as you will see shortly.

Saving the Query

Let’s run the query “Select * from Employees” again and save the query this time, after reviewing the results. Unless you Save the query, the Saved SQL tab will show “No Saved SQL Found.” Now click on the Save button to save the query and you will be asked to provide a Name a Description to the query you want to save, as shown in the next picture.

The next time you click on Saved SQL you will see the following screen. It shows the owner, name, description it was saved with, and other details. You may bring up the query by clicking on the hyperlink on the Name (“AllEmpl” in this screen) of the query and run again to get some fresh results.

History Tab

The last tab, History, shows the chronological query-related information. This is very useful information if you want to backtrack your work. In this screen you can search for saved queries by a chosen owner, and display the results. After placing a check mark for the query, you can even delete it from the saved list. After going back in history, you can bring up an old query and the statement gets copied to the SQL Pane; you can now run it if you like. It is a great way to bring up old queries and refine them as per the situation. The only complaint I have against this UI is that both the panes should have had adjustable scroll bars (or is it a limitation of my display device?). When all the results are shown the UI occupies the entire screen height.

{mospagebreak title=An Example from PL/SQL}

SQL is just a query language. However, you need more than SQL to take care of  business. Every database vendor has an alternative language set that works well with SQL, but can handle data more flexibly by introducing logic and transactional capabilities to the handled data. PL/SQL for Oracle provides the sophisticated data processing capability. PL/SQL stored procedures can store your business logic, PL/SQL packages can do summarizing data and other chores on a schedule, and PL/SQL can set up triggers to work with the business logic and much more. The object of this example is to show that PL/SQL can be run from SQL commands in the OracleXE interface.

PL/SQL is a block-based programming interface. The PL/SQL block is built from three sections:

declare

execution{between BEGIN and END}

optional exception.

Example 2

The example shown calculates the man-hours utilized by a job which takes the number of men working multiplied by the number of hours worked. The query is shown in the next picture with the results. The query also shows the PL/SQL’s way of printing results using the DBMS_output_PUT_LINE() method. In the code, the number of men is an integer, but the hours_worked is a decimal. The number of man hours is again in integers. If the number of hours is changed to, say, 8.3, the total will come out to 50 instead of 51 man-hours because man_hours is declared to be integer.

Exception Messages

Exceptions are handled in the Exception block. Let’s look at how exception messages are displayed. The next picture shows the display when an exception is thrown. The man_hours variable is declared without the data type. Discussion of trouble shooting is outside the scope of this tutorial.

{mospagebreak title=Example 3: Dual Tables}

Another of Oracle’s very useful constructs is the Dual table. This is a table with just one column into which you can stuff your own value. Here is what you will get when you describe the Dual table.

 If you were to use a select query, Select ‘hello’ from dual, it would return ‘Hello’ in the Dummy column.

An example of using Dual

You could run a math function to be evaluated into dummy as shown in the next picture.

Summary

The SQL Command window of Oracle XE has is an extremely intuitive and user-friendly interface to run SQL, PL/SQL queries. The results of running a query against the database are provided in a single, tabbed window. The SavedSQL and history features provide links that will help refining and back tracking previously run queries.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye