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.

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
   1. Oracle SQL Developer
   2. Oracle Database 9.2.0.1 or later
   3. Access to the Sample User Account HR
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
   1. There are many SQL examples in the Database Documentation on OTN
   2. Discuss this how-to in the OTN SQL Developer Forum.