Chat now with support
Chat with Support

SQL Navigator for Oracle 7.6 - User Guide

Quick Overview Working With SQL Navigator Navigation Oracle Logon Code Editor Visual Object Editors Team Coding and Version Control Systems Modules, Windows And Dialogs
Analyze Tool Auto Describe Tool Benchmark Factory Bookmarks Dialog Capture Web Output Change Logon Password Dialog Code Analysis Code Assistant Code Road Map Code Templates Code Test Database Source Code Search Dialog DB Navigator Describe Difference Viewer Edit Data ER Diagram Explain Plan Tool Export Table Find and Replace Dialog Find objects Dialog Find Recycle Bin Objects Dialog Formatting Options HTML Viewer Import HTML as PL/SQL Import Table Java Manager Job Scheduler Locate In Tree Output Window PL/SQL Profiler Profile Manager Project Manager Publish Java to PL/SQL Quick Browse Rename Object Search Knowledge Xpert Select DB Object Dialog Server Output Server Side Installation Wizard Session Browser Source Preview SQL Modeler SQL Optimizer Task Manager Web Support Configuration Dialog Wrap Code
View | Preferences About Us

Code Editor PL/SQL

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

Icon Tool Tip Description
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:

  1. Open the second script in a new editing pane of the Code Editor.
  2. Return to the split panes. In the pane to load the second script right-click and select Split/Compare | Second Source.
  3. Select the second source from the list of all scripts currently open in the Code Editor.

 

PL/SQL Specific Functions

Icon Tool Tip Description
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.

Icon Tool Tip Description

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

Icon Tool Tip Description
SQL Optimizer SQL Optimizer
Explain Plan Explain Plan Tool

PL/SQL Formatter

Format PL/SQL, SQL*Forms, Oracle Forms, and SQL*Plus source code.

Menu Icon Menu Name More Information

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

Edit, Compile And Execute

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).

Features Description

Standard Editing Functions

All standard editing functions are available.

See:

Control the contents of the Code Editor window Toolbars for Code Editor SQL or Code Editor PL/SQL as appropriate.
Manage objects in schemas Main Menu | Object Menu
Handle text-and code Main Menu | Edit Menu
Search for code or objects Main Menu | Search Menu
Access to various SQL Navigator windows and tools Main Menu | View Menu
Manage database sessions Main Menu | Session Menu
Access add-ons, integrated applications and additional tools Main Menu | Tools Menu

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:

  1. Right click on the object in the editing pane and select Go to Definition.
  2. The result is shown in the Output Window.

Describe the object at the current cursor location:

  1. Press CTRL and click the object's name.
  2. 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.

  1. Place the cursor in the editor window where you want the code to be inserted
  2. Press Ctrl+J. The template names display in a drop-down list.
  3. Select the name of the template you want to insert from the drop-down list.
  4. 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
Code Description

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.

Icon Description
Enabled breakpoint. For more on debug see PL/SQL Debugger.
Disabled breakpoint. For more on debug see PL/SQL Debugger.
Current execution line
Invalid breakpoint. For more on debug see PL/SQL Debugger.

This statement executed with errors.

Information about the error is displayed. Double click on this information to open the Oracle Error Information dialog.

This SELECT statement produced results.

TIP:

  • Double click on the icon to jump to the associated results tab (Press CTRL+F11).
  • There can be multiple results displays, one per statement executed. Each result set is displayed in a separate tab.
  • See SQL Query Results Data Grid
  • When a result tab is selected, the corresponding statement will be focused.
This non-select statement executed successfully.
This statement was executed with warnings.

Look and Feel
Feature Description

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.

SQL*Plus command support

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;

/

Auto Code Completion

Scenario: Use Point-And-Click to insert column names for a database object into your code.

  1. Ensure Auto Code Completion is turned on.
  2. Place the cursor in the editor window where you want the column names to be inserted.
  3. Type the name of the object followed by a period mark (.).
  4. Select the name of the column you want to insert from the drop-down list.
  5. Press Enter.

Scenario: Show a parameters list for a procedure or function.

  1. Ensure Auto Code Completion is turned on.

  2. Type in the name of the procedure or function followed by an open bracket '('.

  3. Automatic code completion brings up a list of parameters (including alternative lists for overloaded procs/funcs).

Scenario: Dot lookup for record members.

  1. Ensure Auto Code Completion is turned on.

  2. Type in the name of the variable followed by a full stop.

  3. 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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating