The toolbar appropriate to PL/SQL development opens when you create / open a stored object in the Code Editor. Each of the toolbar icons is described below. In addition, all standard editing functions are available. For more information, see Edit, Compile And Execute.
General Code Editor Functions
 |
Back |
Navigate between hyperlinked database objects (in the editing pane) and their dependent objects and components. |
 |
Forward |
 |
New SQL |
Write a single SQL statement or a series of SQL statements in a new editing pane. The toolbar will open for SQL development. |
| |
New Stored Object |
Create a stored object. Open the New Stored Object Dialog. |
 |
Open File |
Open an external file in the Code Editor.
An alternative way to open file is to drag and drop a file from Windows Explorer to the SQL Navigator window. |
 |
Save to File |
Save the contents of the current Code Editor pane to an external file. |
 |
Open Object |
Locate a stored object using the Select DB Object Dialog and open the object in the editing pane. |
 |
Auto Code Completion |
Turn On/Off Auto Code Completion. When turned on, the Code Editor matches variables, parameters, procedures and types as you type. |
 |
Vertical Split |
Adjust the layout of the editing pane.
Select from the options to split the editing pane in half either horizontally or vertically. The content of the editing pane will be visible in both panes. You can scroll the panes independent of each other.
|
|
TIP: To open a different script in one of the panes:
- Open the second script in a new editing pane of the Code Editor.
- Return to the split panes. In the pane to load the second script right-click and select Split/Compare | Second Source.
- Select the second source from the list of all scripts currently open in the Code Editor.
| |
PL/SQL Specific Functions
 |
Open/Create Package Body |
Navigate to a function/procedure inside the package body. |
| |
Entry |
Move the cursor to the function / procedure definition in the code. |
 |
Undo all changes |
Undo all changes since the last save. |
 |
Generate DDL script |
Generate a DDL script of the procedure / function / package. Switch between the DDL script and procedure / function / package using the tabs at the bottom of the screen. While the DDL script is on view the Code Editor toolbar adjusts to editing SQL code. |
 |
Save to Database |
Save changes. Submit the PL/SQL to the database, compile, and report errors |
 |
Save to Database As (Clone) |
Save (clone) the object. The new stored program will have a definition identical to the stored program currently open in the editor. Optionally select a new schema and name for the object. |
 |
Execute Procedure / Function |
Open the PL/SQL Execution Console from editing mode.
If the PL/SQL Execution Console is already open then execute the code.
|
|
TIP: Once you have opened the PL/SQL Execution Console, toggle between execution and editing mode via the tabs at the bottom of the screen. | |
 |
Stop procedure execution |
Use if required to stop execution of the procedure before it finishes. |
 |
Toggle Breakpoint |
Add / Remove breakpoint on the selected line of code. For more on debug see PL/SQL Debugger. |
 |
PL/SQL Debugger |
You can watch the result values during runtime. You can run stored programs in parallel by opening additional sessions within SQL Navigator.
Show/Hide the Toolbox PL/SQL Debugger |
 |
Abort Debug Session |
For more on debug see PL/SQL Debugger. |
Team Coding Version Control
To enable Team Coding see Administer Team Coding.
 |
Get Latest Revision |
Get the latest version of an object or script as it is held in the Version Control repository.
The Get Latest Revision command overwrites the version of the object or script in the database, replacing it with the latest version held in the VCS repository. You can use the View Differences Dialog to compare versions before overwriting the object. |
 |
Check Out |
Check out or check in the current object or script.
Open: |
 |
Check In |
 |
Undo Check Out |
Cancel the check-out. You are prompted to confirm that you want to discard any changes you have made and restore the database version of the item as it was prior to check-out.
| Confirm Yes |
You have made and saved changes to the object and you want to discard those changes. |
| Confirm No |
You have made and saved changes to the object and you want those changes to be retained in the database. As a result the version saved in the third party version control repository will be different from the version saved in the database. | |
Tools and Applications
 |
SQL Optimizer |
SQL Optimizer |
 |
Explain Plan |
Explain Plan Tool |
 |
PL/SQL Formatter |
Format PL/SQL, SQL*Forms, Oracle Forms, and SQL*Plus source code.
 |
Format Text |
Format the entire source currently in the editor.
To format just a selection, select the text you want to format.
Output is displayed in the Output Window. |
 |
Syntax Check |
Check the syntax. Output is displayed in the Output Window.
If syntax errors are detected, the text stays unchanged. The errors are displayed in the Output Window. |
 |
Profile Code |
Create a summary of the code statistics. You can copy to clipboard or save to file. |
 |
Multi-File Formatting |
Open the Multi-File Formatting Selection dialog.
- Select Folder and enter the folder that directly contains the files you want to format. Or
- Select Files and enter the files you want to format.
Select Backup files to folder to create a backup copy of the files you are about to format. |
 |
Format Options |
Define how the Formatter Tool formats code.
Formatting Options | |
 |
Knowledge Xpert |
Search Knowledge Xpert |
 |
Code Analysis |
Code Analysis |
The Code Editor opens ready to edit SQL code. You will see the SQL Toolbar (Code Editor SQL) and a blank canvas to write SQL code. If you create / open objects requiring PL/SQL code you will see the PL/SQL Toolbar (Code Editor PL/SQL).
|
Standard Editing Functions |
All standard editing functions are available.
See:
See also:
Some functions are duplicated on the shortcut menu. Right click in the editing pane to open the shortcut menu. |
|
Working with objects |
Drag and drop objects from the following SQL Navigator modules into the editing pane.
Show the definition of the object at the cursor location:
- Right click on the object in the editing pane and select Go to Definition.
- The result is shown in the Output Window.
Describe the object at the current cursor location:
- Press CTRL and click the object's name.
- This opens Describe for the object.
|
|
TIP:
- To construct SQL statements, drag and drop column names from the Describe tool into the editing pane.
- If the described object is a text object (view, procedure, function, package or packaged procedure/function) and the Source Preview window is open, the object’s source is automatically previewed.
| |
|
Automated Coding Assistance |
|
Auto Code Completion |
As you type an identifier the editor displays a selectable list of matching symbols (variables, parameters, procedures, types) in the current scope.
|
|
TIP: Turn on/off code completion from the Code Editor Toolbar. | |
| Dot-lookup |
Type a dot character after a name of variable. The editor automatically displays a selectable list of members of a PL/SQL record, cursor, package or %ROWTYPE record. |
| Code Explorer |
The Code Explorer displays a hierarchical list of all symbols in the package or procedure, and highlights the procedure the cursor is currently in. Double-click on a symbol to navigate within the program. It dynamically parses and checks syntax. |
| Hyperlinks |
To see the declaration of an identifier, press Ctrl+click. The text cursor automatically jumps to the declaration of the symbol if it's defined within the same program. If it's a name of an external database object, an Auto Describe opens. |
| Syntax tool tips |
Point to a variable, parameter or procedure with the cursor to see a description of it. | |
|
Insert ready made code |
Insert ready made code into the editor.
- Place the cursor in the editor window where you want the code to be inserted
- Press Ctrl+J. The template names display in a drop-down list.
- Select the name of the template you want to insert from the drop-down list.
- Press Enter.
To manage, create and edit the ready made code, see Code Templates. |
|
Drag and drop PL/SQL syntax, SQL functions, column names, and database object names into code using the Code Assistant. |
|
Code with multiple SQL statements
and
PL/SQL blocks |
|
SQL |
If you write multiple SQL statements in the editing pane then ensure each SQL statement ends with either:
- a semicolon (;)
- " / " on the next line.
|
|
TIP: There is a quick way to construct SELECT statements for multiple tables. Highlight the tables in DB Explorer, drag and drop them into the editing pane. This behavior is set in View | Preferences | Code Editor | General | Drag & Drop. | |
|
PL/SQL |
PL/SQL blocks entered into the script must have either
- a forward slash /
- or a period mark .
following the last line of the block.
This is necessary because the PL/SQL blocks can themselves contain blank lines and semicolons.
When you create or execute a PL/SQL anonymous block, the semicolons are required in the SQL statement. For example
BEGIN
Test_procedure;
END; | |
|
Execute the SQL query
or
Compile the PL/SQL code |
Execute the SQL query
See: The toolbar: Code Editor SQL.
Compile the PL/SQL code
See: Code Editor PL/SQL, PL/SQL Debugger, DBMS_OUTPUT.
You can compile a program that is stored in the database. While the program is being edited, use the Save command to compile and store it. Once the program has been modified, you will need to save the program prior to any further usage of the Compile/Rebuild functionality - This is to ensure that the changes in the program have been applied to the database.
SQL Navigator displays all syntax and compiler errors in a separate scrollable pane. Click on the error text to show the source code at the source of the error. Double click on the error text to show the error message description, cause and actions as per the Oracle documentation.
Also watch for feedback in the Output Window.
Auto Reparse
Many features of the Code Editor, including the Code Explorer window, code completion, tool tip display of program arguments, bracket matching, collapse loops/blocks, and others, rely on automatic parsing of the PL/SQL code and internally generating a symbol table. This parsing occurs when the editor first loads the objects, and it also occurs in the background in order to maintain the symbol table as the user edits the code.
You can also manually trigger a full reparsing (updating of the internal symbol table) at any time by right-click and select Auto Reparse from the shortcut menu. However, when loading a really large script having this option on will slow down SQL Navigator. Hence, to avoid wasting CPU resources, you should turn this option off when editing large scripts.
Symbols in the gutter margin
Symbols in the gutter margin provide a visual indication of the statement’s status.
|
|
Look and Feel |
|
Collapse / Expand Statements |
You can collapse/expand a block, procedure, loop or IF statement by clicking on the - or + symbols to hide/show codes.
|
|
TIP: When the script is exceptionally long the collapse/expand codes may slow down the application. You can choose to disable this feature in View | Preferences |Code Editor | General| Enable Code Collapsing. | |
|
Syntax highlighting |
The Code Editor uses colors to highlight PL/SQL and SQL keywords, text and comments.
When you set the cursor at a bracket within an expression, the matching bracket is automatically highlighted. |
|
Bookmarks |
Lines of code can be bookmarked so you can return to them easily.
- To add / list / go to bookmarks see the Edit Menu.
 (0-9) in the gutter margin indicate Bookedmarked lines. |
|
Variable declarations |
To move the cursor to the declaration of a variable (or Auto Describe it if it’s the name of an external database)
Press CTRL and point to the variable with the mouse.
To return to the former position in the text, press ALT+Left Arrow. |
|
Switch between specification and body |
Press CTRL+SHIFT plus the down or up arrow to move the cursor between the specification and the body. |
|
Formatter Tools |
SQL Navigator's Formatter Tools is a unique utility for reformatting existing PL/SQL, SQL*Forms, Oracle Forms, and SQL*Plus source code. See the Code Editor toolbar: Code Editor SQL or Code Editor PL/SQL. |
|
Automatic Indentation |
When you insert multi-line text into the editor, the text is placed at the same indentation level as the current cursor position. For best results, before inserting text, place the cursor at the location and indentation level where you want the inserted text to appear. |
|
Manipulate rectangular blocks of code |
Right click on the code and select Edit | Selection Mode | Block or press ALT+F7.
The block selection is limited to the length of the last line. To overcome this limitation select View | Preferences | Code Editor | General | Allow Caret after EOL. |
|
Show/hide invisible characters |
Right click on the code and select Edit | Show Tabs/Eol/Eof |
|
Switch between tabs and spaces |
Right click on the code and select Edit | Tabs/Spaces and select from the available options.
Requires View | Preferences | Code Editor | General | Use Tab Characters selected. | |
The Code Editor supports the following SQL*Plus commands:
- Comment Delimiters (/*...*/)
- Double Hyphen (- -)
- At Sign (@)
- Double At Sign (@@)
- Forward Slash (/)
- CONNECT
- DESCRIBE
- DISCONNECT
- EXECUTE
- REMARK
|
|
Note: Consult Oracle documentation for details about Oracle's SQL*Plus utility. |
SQL Navigator also allows large scripts or SQL statements to be executed in the background, allowing you to perform other functions on your PC simultaneously.
Executing Scripts Invoked By At Sign (@) or Double At Sign (@@) SQL*Plus Commands
Ensure that all the required scripts are in the same directory and in the correct SQL format.
In the Code Editor, invoke the master script using the @ command.
The output of the executed scripts will be displayed in the appropriate window of SQL Navigator, for example the output of a SELECT statement will appear in the grid (SQL Query Results Data Grid), while the output of a CREATE statement will appear under the appropriate node in DB Navigator.
Connect To A Database Via The Code Editor
To execute a SQL statement or script within the Code Editor, you must first be connected to the relevant database.
It is possible to connect and disconnect databases from within the Code Editor using SQL*Plus commands.
|
Connect |
Open a new Code Editor tab, and then type and execute a connect statement using the following SQL*Plus format
Connect name/password@database |
|
Disconnect |
Open a new Code Editor tab, and then type and execute a disconnect statement using the following SQL*Plus format
Disconnect name/password@database
|
|
Note: If you type and execute the disconnect statement without specifying any database details, the current session you are using will be disconnected. | |
Bind Variables
A bind variable is a variable in a SQL statement that must be replaced with a valid value or address of a value in order for the statement to successfully execute.
Bind variables enable you to use PL/SQL in a SQL*Plus script. They provide a mechanism for returning data from a PL/SQL block so that it can be used in subsequent queries.
The Code Editor supports bind variables; use them the same way as you would use them in SQL*Plus.
Here is an example of how to declare a bind variable:
VARIABLE s_table_name varchar2(30)
To reference a bind variable in a PL/SQL block, preface it with a colon ( : )
BEGIN
:s_table_name := ‘EMPLOYEE’;
END;
/
Scenario: Use Point-And-Click to insert column names for a database object into your code.
- Ensure Auto Code Completion is turned on.
- Place the cursor in the editor window where you want the column names to be inserted.
- Type the name of the object followed by a period mark (.).
- Select the name of the column you want to insert from the drop-down list.
- Press Enter.
Scenario: Show a parameters list for a procedure or function.
- Ensure Auto Code Completion is turned on.
-
Type in the name of the procedure or function followed by an open bracket '('.
-
Automatic code completion brings up a list of parameters (including alternative lists for overloaded procs/funcs).
Scenario: Dot lookup for record members.
- Ensure Auto Code Completion is turned on.
-
Type in the name of the variable followed by a full stop.
-
SQL Navigator displays a list of matching members. Dot-lookup automatically displays a ‘pick’ list of members of a PL/SQL record, cursor, package or %ROWTYPE record.
