How-To Document

 

How to Run or Create Reports using SQL Developer

Updated: Nov 2009 for SQL Developer 2.1

After completing this How-To, you should be able to understand:

  • How to run supplied reports to query the data dictionary.
  • How to create and run your own user defined reports.

 

Table of Contents

  1. Introduction
  2. Software Requirements
  3. Using Available Reports
  4. Adding User Defined Reports
  5. Additional Resources

 

1. Introduction

Oracle SQL Developer provides a set of reports that you can run, and also the ability to create your own specific records. In this How-To we only look at a few reports, to introduce you to what is available.

2. Software Requirements

3. Using Available Reports

SQL Developer offers a number of reports with the product. In this section you'll look at how to run these reports.

1.

Switch to the Reports tab in SQL Developer and expand the Data Dictionary Reports node.

 

2.

You can learn basic details about your database by selecting the About your Database node and expanding that.

Click Version Banner and you will be prompted with a Select Connection dialog, select a connection from the drop list.

 

3.

To complete this exercise, you can select any report and run it. As an example, there is a report which lists all the primary keys for the tables in your schema.

Expand the Table -> Constraints node and select Primary Key Constraints

 

4.

On selecting the node, a window pops up. This window allows you to restrict the query to a certain criteria. In this case you can only restrict the query on Table Name. By immediately clicking Apply, you select all records.

To restrict your report, type e into the field for Table Name.

Click Apply.

 

5.

The resulting report shows all the tables, for the schema HR, that have Primary Keys.

 

4. Adding User Defined Reports

SQL Developer offers the ability to create, save and run your own reports. You just need the SQL! Your reports can be simple or complex. In this exercise, you start with a very basic report.

1.

Collapse the Data Dictionary Reports node and use the context menu to add a new report.

 

2.

You can add any query here. As an example use the following query:

select object_name, object_type
from user_objects;

You can give the report a name and a description. An example is provided:

Click Apply to create the report.

 

3.

Select the newly created report. SQL Developer prompts you for a connection to the database. You can use HR, as you been throughout the exercise, or pick another connection, if you have one.

Click OK.

Notice that you can identify which connection you used, when you ran the report.

 

4.

Finally, you can take the query and use it in the SQL Worksheet by clicking the Run Report in SQL Worksheet button.

 

5. Additional Resources