5.3 Using Query Builder
Use Query Builder to build queries graphically by adding tables to a panel and selecting the columns to return.
Query Builder's graphical user interface enables database developers to build SQL queries without the need for manual SQL coding.
Using Query Builder, you can search and filter database objects, select objects and columns, create relationships between objects, view formatted query results, and save queries with little or no SQL knowledge.
Tip:
SQL commands created in the Query Builder can be accessed from SQL Commands.
- Query Builder Home Page
Learn about Query Builder home page. - Accessing Query Builder
Learn how to access Query Builder. - Understanding the Query Building Process
Learn how to build a query in Query Builder. - Using the Object Selection Pane
Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema as well as select objects. - Using the Design Pane
Learn about how to use the Design pane. - Specifying Query Conditions
As you select columns within an object, you can specify conditions on the Conditions tab. - Creating Relationships Between Objects
You can create relationships between objects by creating a join. - Working with Saved Queries
You can create queries and save the query for later use. - Viewing Generated SQL
The SQL view presents a read-only, formatted representation of the SQL generated by Query Builder. You can copy the SQL code that appears in the SQL View for use in other tools such as SQL Command Processor or App Builder. - Viewing Query Results
Execute a query, once you select objects and determine what columns to include in your query.
See Also:
Parent topic: Using SQL Workshop Utilities
5.3.1 Query Builder Home Page
Learn about Query Builder home page.
The Query Builder page is divided into three sections:
-
Object Selection pane displays on the left side of the page and contains a list objects from which you can build queries. Only objects in the current schema display.
-
Design pane displays to the right of the Object Selection pane and above the Conditions, SQL, Results, and Saved SQL tabs. When you select an object from the Object Selection pane, it appears in the Design pane.
-
Output pane displays below the Design pane. Once you select objects and columns, you can create conditions, view the generated SQL, or view query results.
See Also:
Parent topic: Using Query Builder
5.3.2 Accessing Query Builder
Learn how to access Query Builder.
To access Query Builder:
- On the Workspace home page, click SQL Workshop.
- Click Utilities.
- Click Query Builder.
The Query Builder home page appears.
Tip:
You can also access Query Builder from a code editor in many part of Oracle APEX by clicking the Query Builder button when editing a SQL attribute.
Parent topic: Using Query Builder
5.3.3 Understanding the Query Building Process
Learn how to build a query in Query Builder.
To build a query in Query Builder, you perform the following steps:
- Select objects from the Object Selection pane. See Using the Object Selection Pane.
- Add objects to the Design pane and select columns. See Selecting Objects.
- Optional: Establish relationships between objects. See Creating Relationships Between Objects.
- Optional: Create query conditions. See Specifying Query Conditions.
- Execute the query and view results. See Viewing Query Results.
Parent topic: Using Query Builder
5.3.4 Using the Object Selection Pane
Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema as well as select objects.
- Searching and Filtering Objects
Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema. - Hiding and Restoring the Object Selection Pane
You can hide the Object Selection pane by selecting the Collapse control. - Selecting Objects
Use the Object Selection pane to select objects.
Parent topic: Using Query Builder
5.3.4.1 Searching and Filtering Objects
Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema.
To search or filter objects:
Parent topic: Using the Object Selection Pane
5.3.4.2 Hiding and Restoring the Object Selection Pane
You can hide the Object Selection pane by selecting the Collapse control.
The Collapse control displays on the right side of the Object Selection pane. If the Object Selection pane displays, selecting this control hides it. Similarly, if the Object Selection pane is hidden, selecting this control causes the pane restores it. Select the Left Splitter (click above or below the Collapse control to manually resize the Object Selection pane.
Parent topic: Using the Object Selection Pane
5.3.4.3 Selecting Objects
Use the Object Selection pane to select objects.
The Design pane displays to the right of the Object Selection pane. When you select an object from the Object Selection pane, it appears in the Design pane. You use the Object Selection pane to select objects (that is, tables, views, and materialized views) and the Design pane to identify how those selected objects are used in a query.
Parent topic: Using the Object Selection Pane
5.3.5 Using the Design Pane
Learn about how to use the Design pane.
Information on how to use how to use the Design pane includes the following topics:
- About Supported Column Types
Columns of all types available in Oracle Database 10g Release (10.2) or later display as objects in the Design pane. - Adding an Object to the Design Pane
You add an object to the Design pane by selecting it from the Object Selection pane. - Resizing the Design and Results Panes
As you select objects, you can resize Design and Results panes by selecting the Bottom Splitter in the center of the page. - Removing or Hiding Objects in the Design Pane
You remove or hide objects in the Design pane by selecting controls at the top of the object.
Parent topic: Using Query Builder
5.3.5.1 About Supported Column Types
Columns of all types available in Oracle Database 10g Release (10.2) or later display as objects in the Design pane.
Note the following column restrictions:
-
You may only select a maximum of 60 columns for each query.
-
The following column types are not selectable and cannot be included in a generated query:
-
BLOB
-
NCLOB
-
RAW
-
LONG
-
LONG RAW
-
XMLType
-
Any other nonscalar column types
-
Parent topic: Using the Design Pane
5.3.5.2 Adding an Object to the Design Pane
You add an object to the Design pane by selecting it from the Object Selection pane.
To add an object to the Design pane:
See Also:
Parent topic: Using the Design Pane
5.3.5.3 Resizing the Design and Results Panes
As you select objects, you can resize Design and Results panes by selecting the Bottom Splitter in the center of the page.
The Bottom Splitter resembles a gray horizontal rule in the center of the page. Move the Bottom Splitter up and down to shrink and expand the Design pane.
Parent topic: Using the Design Pane
5.3.5.4 Removing or Hiding Objects in the Design Pane
You remove or hide objects in the Design pane by selecting controls at the top of the object.
To remove an object, select the Remove icon in the upper right corner. To temporarily hide the columns within an object, click the Show/Hide Columns icon.
Parent topic: Using the Design Pane
5.3.6 Specifying Query Conditions
As you select columns within an object, you can specify conditions on the Conditions tab.
Conditions enable you to filter and identify the data you want to work with. You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions.
To specify query conditions:
See Also:
Parent topic: Using Query Builder
5.3.7 Creating Relationships Between Objects
You can create relationships between objects by creating a join.
A join identifies a relationship between two or more tables, views, or materialized views.
- About Join Conditions
A join condition determines how the rows from one object combine with the rows from another object. - Joining Objects Manually
You can create a join manually by selecting the Join column in the Design pane. - Joining Objects Automatically
Learn how to join objects automatically.
Parent topic: Using Query Builder
5.3.7.1 About Join Conditions
A join condition determines how the rows from one object combine with the rows from another object.
When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition.
Query Builder supports inner, outer, left, and right joins. An inner join (also called a simple join) returns the rows that satisfy the join condition. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
See Also:
Oracle Database SQL Language Reference for information about join conditions
Parent topic: Creating Relationships Between Objects
5.3.7.2 Joining Objects Manually
You can create a join manually by selecting the Join column in the Design pane.
To join two objects manually:
Parent topic: Creating Relationships Between Objects
5.3.7.3 Joining Objects Automatically
Learn how to join objects automatically.
When you join objects automatically, the Query Builder suggests logical, existing parent and child relationships between existing columns.
To join objects automatically:
Parent topic: Creating Relationships Between Objects
5.3.8 Working with Saved Queries
You can create queries and save the query for later use.
As you create queries, you can save them by clicking the Save button in the Design pane. Once you save a query, you can access it later in the Saved SQL view.
Tip:
The Saved SQL view only displays queries saved in Query Builder. In SQL Commands you can view queries saved in both SQL Commands and Query Builder.
- Saving a Query
Learn how to save a query. - Editing a Saved Query
Once you save a query, you can access it in the Saved SQL view and edit. - Deleting a Saved Query
Learn how to delete a saved SQL query.
Parent topic: Using Query Builder
5.3.8.1 Saving a Query
Learn how to save a query.
To save a query:
-
Build a query:
-
On the Workspace home page, click SQL Workshop and then Query Builder.
Query Builder appears.
-
Select objects from the Object Selection pane.
-
Add objects to the Design pane and select columns.
-
Execute the query.
-
-
Click Save.
-
Enter a name and description and click Save.
The saved query displays in the Saved SQL view.
Note that Query Builder does not support duplicate query names. If you open an existing query, keep the existing name, and save it again, Query Builder over-writes the existing query. If you change the name of an existing query and save it again, Query Builder saves the query again under the new name.
Parent topic: Working with Saved Queries
5.3.8.2 Editing a Saved Query
Once you save a query, you can access it in the Saved SQL view and edit.
To edit a Saved SQL query:
Parent topic: Working with Saved Queries
5.3.8.3 Deleting a Saved Query
Learn how to delete a saved SQL query.
To delete a Saved SQL query:
Parent topic: Working with Saved Queries
5.3.9 Viewing Generated SQL
The SQL view presents a read-only, formatted representation of the SQL generated by Query Builder. You can copy the SQL code that appears in the SQL View for use in other tools such as SQL Command Processor or App Builder.
To access the SQL view:
See Also:
Parent topic: Using Query Builder
5.3.10 Viewing Query Results
Execute a query, once you select objects and determine what columns to include in your query.
Execute a query by:
-
Clicking the Run button (or pressing Ctrl+Enter)
-
Selecting the Results tab
The Results view appears, displaying formatted query results. To export the report as a comma-delimited file (.csv) file, click the Download link at the bottom of the page.
Parent topic: Using Query Builder