Home arrow Oracle arrow Page 3 - Learning to Query in Oracle XE

An Example from PL/SQL - Oracle

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.

  1. Learning to Query in Oracle XE
  2. Running SQL Queries
  3. An Example from PL/SQL
  4. Example 3: Dual Tables
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 26
June 07, 2006

print this article



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:


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.

>>> More Oracle Articles          >>> More By Jayaram Krishnaswamy

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: