Most of the time you interact with a database by writing and executing SQL queries, statements,
stored procedures and scripts. Therefore, it is clear that a good (or rather perfect) Oracle PL/SQL Code editor
is a must tool for any developer.
dbForge Studio for Oracle supplies SQL reporting tool that allows creating ad
hoc data reports, sending them to their recipients, and automating the whole process.
In this article we would like to familiarize our customers with SQL editor and its basic features.
You can launch SQL editor in several ways. For example:
The image below illustrates how the window of Oracle PL/SQL editor editor looks like:
Now let's take a brief look at the features of SQL editor, integrated into
dbForge Studio for Oracle.
- The statements are colorized so you can easily read them
Context-sensitive code completion
SQL editor extracts the context-sensitive features from the code and their relations to other
code elements and auto-completes the current code with the proper elements according to the chosen
Code collapsing/expanding with support of user-defined regions
Whenever you have a "block" of code (BEGIN....END, etc) SQL editor will put a little plus-sign
in the margin and allow you to "collapse" that block of code so you only see the BEGIN, not stuff
underneath. If you're working with a really large set of code, that's helpful.
SQL formatting with rich options
Improve readability, consistency and standardization with SQL formatting. The feature enables to define
and share SQL coding standards among multiple developers easily. Based on your needs, you can use 3 types of formatting:
Automatic code formatting significantly saves your time. By default, when you finish a statement
with a delimiter, it is automatically formatted.
Manual. In case you want to format SQL code manually, decide whether you want to format the whole
code in your script or only a statement.
- Using SQL formatter wizard - to format multiple SQL files at once.
SQL snippets library with a browser window and editor
Code snippets are meant to help you type SQL code quickly. You can use predefined snippets provided in the
application and create your own to insert them in SQL scripts and queries. Each snippet represents a
statement or a group of statements fulfilling the same task such as creating a new user or describing
a package. You can insert the required code snippet into your source code with a few mouse clicks.
Triggers in the SQL query editor
A trigger is a special kind of stored procedure that goes into effect when you modify data in a
specified table using one or more data modification operations: UPDATE, INSERT, or DELETE.
Triggers can query other tables and can include complex SQL statements. They are primarily useful
for enforcing complex business rules or requirements. For example, you could control whether to allow
an order to be inserted based on a customer's current account status.
Triggers are also useful for enforcing referential integrity, which preserves the defined relationships
between tables when you add, update, or delete the rows in those tables. However, the best way to
enforce referential integrity is to define primary key and foreign key constraints in the related
tables. If you use database diagrams, you can create a relationship between tables to automatically
create a foreign key constraint.
Document Outline window for a quick navigation through large scripts
This window provides quick and easy navigation through the report elements. It can be used while building
a report to access all report's elements and to see the whole report's structure.
Execution of scripts, selected SQL fragments, and statements
You can have the complete control upon scripts and statements right from the editor create, edit,
execute and terminate scripts in a wink! Oracle SQL editor provides you with a rich editing and debugging
experience when you are working with scripts. The editor supports IntelliSense as well as color coding
of syntax to improve the readability of complex statements. It also supports find and replace, bulk
commenting, custom fonts and colors, and line numbering. You can also change the database which the
script in the editor will be executed against.
One-step access to a schema object editor from code (go to definition)
This option allows you to open Visual Object Editor and explore DDL objects with their properties.
If the definition is outside of the current script, an object editor or a corresponding DDL script
will open in another window. You will appreciate this feature even more while working with SQL
scripts in a database project. A project can contain SQL scripts and query files located in different
folders and on different discs, but thanks to Go to definition option you still can navigate to the
definitions of database objects from the scripts where these objects are mentioned.
Parameter information for stored procedures and functions
The feature will help you quickly see the names and types of parameters,
which are used in a function or stored procedure script. All information is neatly displayed in the
Parameter Info tooltip. The parameter in bold is the next parameter that is required for the function
or stored procedure.
Quick access to favorite templates from SQL editor
Code completion is significantly simplified with a wide set of templates, which are called in one click.
Quick information about schema objects
The Quick Info feature is a tooltip with brief information about database objects, parameters,
and variables. The tooltip appears when you place the mouse pointer on the required item in the
SQL script or press the shortcut Ctrl+K and then I. In case of database objects, the tooltip shows
their location and types. You will find this option useful, while working with large scripts.
Executed SQL history window for the document
SQL statement history is designed to ease recalling and re-executing statements you have previously executed.
Choose View SQL history from the SQL menu to bring up a dialog where you can choose which SQL statement
must be inserted into the editor instead of current one. Point with the mouse on a statement in a history
to view its full text in a hint.