OBE Home > 10gR2 Single > Application Development

Managing Your Database Using Oracle Project Raptor

Purpose

This tutorial describes how to use Oracle Project Raptor to manage your database objects.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Creating a Database Connection
Adding a New Table Using the Table Dialog Box
Changing a Table Definition
Adding Table Constraints
Adding Data to a Table
Accessing Data
Creating Reports
Debugging and Executing PL/SQL
Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle Project Raptor is a new, free graphical tool that enhances productivity and simplifies database development tasks. With Oracle Project Raptor, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own.

An Early Adopter release is now available for download for Windows and Linux. Oracle Project Raptor can connect to any Oracle Database version 9.2.0.1 and later.

Architecture
Oracle Project Raptor was developed in Java leveraging the Oracle JDeveloper IDE framework. Default connectivity to the database is through the JDBC Thin driver (no Oracle Home required); the JDBC Type 2 driver (OCI client side driver) is also supported. Raptor is bundled with JRE 1.5 (with an additional tools.jar to support debugging). Installation is performed simply by unzipping the downloaded file. Oracle supports the Windows and Linux platforms.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial. Note: you can also perform this tutorial connecting to a non-windows database.

2.

Download Oracle Project Raptor from OTN and unzip it into any directory on your Windows machine.

3. Download and unzip raptor.zip into your working directory (i.e. d:\wkdir)
4.

Make sure the DEPENDENTS table doesn't exist. Open SQL*Plus and execute the following commands:

connect hr/hr
drop table dependents;

Back to Topic List

Creating a Database Connection

The first step to managing database objects using Oracle Project Raptor is to create a database connection. Perform the following steps:

1.

Open Windows Explorer and double-click <your_path>\raptor\raptor.exe.

Note: you can also execute raptor in a non-windows environment.

 
3.

In the Connections tab, right-click Connections and select New Database Connection.

 
4.

Enter <your_db_sid_name> for the Connection Name (or any other name that identifies your connection), hr for the Username and Password, specify your <hostname> for the Hostname and enter <your_db_sid> for the SID. Then click Test.

 
5.

The status of the connection was tested successfully. The connection was not saved however. To save the connection, click Connect.

 
6.

The connection was saved and you see the database in the list. Expand ORCL.

Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

 
7.

Expand Tables.

 
8.

Select the Table EMPLOYEES to view the table definition.

 
9.

To see the data, click the Data tab.

 
10.

The data is shown. In the next topic, you create a new table and populate the table with data.

 

Back to Topic List

Adding a New Table Using the Table Dialog Box

You create a new table called DEPENDENTS which has a foreign key with the EMPLOYEES table. Perform the following steps:

1.

Right-click Tables and select Create TABLE.

 
2.

Enter DEPENDENTS for the Table Name and click Show Advanced Options check box.

 
3.

Enter ID for the Name, select NUMBER for the Data type and enter 6 for the Precision. Select the Cannot be NULL check box. Then click the Add Column icon.

 
4.

Enter FIRST_NAME for the Name, and enter 20 for the Size. Then click the Add Column icon.

 
5.

Enter LAST_NAME for the Name, enter 25 for the Size. Select the Cannot be NULL check box. Then click the Add Column icon.

 
6.

Enter BIRTHDATE for the Name, select DATE for the Data type. Then click the Add Column icon.

 
7.

Enter RELATION for the Name, and enter 25 for the Size. Then click the Add Column icon.

 
8.

Enter GENDER for the Name, and enter 1 for the Size. Then click the Add Column icon.

 
9.

Enter RELATIVE_ID for the Name, select NUMBER for the Data type, and enter 6 for the Precision. Select the Cannot be NULL check box. Then click OK to create the table.

 
10.

Your new table appears in the list of tables. Select DEPENDENTS from the list.

 

Back to Topic List

Changing a Table Definition

Oracle Project Raptor makes it very easy to make changes to database objects. In this topic, you delete a column in the DEPENDENTS table you just created. Perform the following steps:

1.

Click the Actions... button.

 
2.

Select Column then Drop.

 
3.

Select the Column Name GENDER and click Apply.

 
4.

The column has been dropped. Click OK.

 
5.

Right-click the ORCL Database Connection and click Refresh to refresh the Database Connection.

 
6.

Expand ORCL > Tables. Then select the DEPENDENTS table.

 

Back to Topic List

Adding Table Constraints

In this topic, you create the Primary and Foreign Key Constraints for the DEPENDENTS table. Perform the following steps:

1.

Right-click DEPENDENTS table and select Edit.

 
2.

Click the Primary Key tab.

 
3.

Select the ID column and click > the click the Foreign Key tab.

 
4.

Click Add.

 
5.

Select EMPLOYEES for the Referenced Table and select RELATIVE_ID for the Local Column and click OK.

 

Back to Topic List

Adding Data to a Table

You can add data to a table by performing the following steps:

1.

Then click the Insert Row icon.

 
2.

Enter the following data and click the Commit Changes icon.

ID 209
FIRST_NAME Sue
LAST_NAME Littlefield
BIRTHDATE 1997-17-01
RELATION Daughter
RELATIVE_ID 110

 
3.

Notice that you received an error on the date data you entered. Change the date to 1997-01-17 and then click the Commit Changes icon again.

 
4.

The row you just added was committed to the database.

 
5.

You can also load multiple rows at one time using a script. Click the SQL Worksheet ORCL tab.

 
6.

Click the Open File icon.

 
7.

Navigate to your working directory (i.e. c:\wkdir) and select the load_dep.sql file and click Open.

 
8.

The SQL from the script is shown. Click the Run Script icon.

 

9.

The data was inserted. To view the data, click the DEPENDENTS tab.

 
10.

Click Refresh to show all the data.

 
11.

All the data in the table are listed.

 
12.

You can export the data so it can be used in another tool, for example, Excel. Right-click on one of the values in any column, select Export and then one of the file types.

 
13.

Enter the name of the file and click Save.

 

Back to Topic List

Accessing Data

One way to access DEPENDENTS data is to generate a SELECT statement on the DEPENDENTS table and add a WHERE clause. Perform the following steps:

1.

Click the ORCL SQL Worksheet tab.

 
2.

Click the clear (pencil) icon to remove the contents of the SQL Statement window.

 
3.

Drag and Drop the DEPENDENTS table from the list of database objects to the SQL statement area.

 
4.

A SELECT statement is shown with all the columns contained in the DEPENDENTS table. Scroll right to the end of the SELECT statement.

 
5.

Add the WHERE clause where relative_id > 110 to the end of the SELECT statement BEFORE the ';'. Then click the Execute Statement icon.

 
6.

The Results are shown.

 

Back to Topic List

Creating Reports

Since the SQL you just ran in the previous topic needs to be executed frequently, you can create a custom report based on the SQL. In addition, you can run a report of your database data dictionary using bind variables. Perform the following steps:

1.

Select the SQL in the ORCL SQL Worksheet you just executed. Then click the Edit pulldown and select Copy.

 
2.

Click the Reports tab.

 
3.

Expand Reports.

 
4.

Right-click User Defined Reports and select Add Folder.

 
5.

Enter the Folder Name DEPENDENTS RPTS and click Apply.

 
6.

Expand User Defined Reports.

 
7.

Right-click DEPENDENT RPTS and select Add Report.

 
8.

Enter a Name for the report and click ctrl+v in the SQL area to paste the SQL you copied from the ORCL SQL Worksheet. Then click Apply.

 
9.

Expand DEPENDENTS RPTS.

 
10.

Select the Report you just created.

 
11.

Click OK to connect to your database.

 
12.

The results of your report are shown.

 
13.

You can also run a Data Dictionary report. Expand Data Dictionary Reports > Data Dictionary. Then select Dictionary Views.

 
14.

Deselect the NULL check box.

 
15.

Enter col for the Value and click Apply.

 
16.

All the Data Dictionary views that contain 'col' in its name are displayed.

 

Back to Topic List

Debugging and Executing PL/SQL

Oracle Project Raptor contains extensive PL/SQL debugging capabilities. In this topic, you create a Package Spec and Package Body that adjusts an employees salary. Perform the following steps:

1.

Click the ORCL SQL Worksheet tab.

 
2.

Click the Connections tab on the left.

 
3.

From the ORCL database connection tab, select the Open File icon.

 
4.

From your working directory (c.\wkdir) select createHRpack.sql and click Open.

 
5.

Click the Run Script icon.

 
6.

The script ran successfully and the package and package body were created. Right-click the Packages node and select Refresh.

 
7.

Expand ORCL > Packages > HR_PACK and select HR_PACK to view the package definition.

 
8.

Select HR_PACK BODY to view the package body definition.

 
9.

To make any changes to the Package Body, click the Edit icon.

 
10.

Click on any one of the + to collapse the code or press - to expand the code.

 
11.

If your line numbers do not appear, you can right-click in the line number area and click Toggle Line Numbers to turn them on. This is useful for debugging purposes.

 
14.

Click the Runicon.

 
15.

A parameter window appears. You need to set the input parameters here.

Set the P_ID to 102 and P_INCREMENT to 1.2. What this means is that the Employee who has the ID 102, their salaryis increased by 20%. The current SALARY for EMPLOYEE_ID 102 is 17000. Click OK.

 
16.

The value returned is 20400. To test the Exception Handling, click the Execute icon again.

 
17.

This time, change the P_INCREMENT value to 5 and click OK.

 
18.

In this case, an exception was raised with "Invalid increment value" because the P_INCREMENT value was greater than 1.5.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

Create a database connection
Add a new table using the Table Dialog Box
Change a table definition
Add data to a table
Access data
Generate a report
Debug and execute PL/SQL

Back to Topic List

Move your mouse over this icon to hide all screenshots.