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

Scan Defines/Substitutions

The Code Editor lets you use substitution variables in SQL statements—similar to the way SQL*Plus handles them.

Note: Turn on Scan Defines/Substitutions when using variables or text that contain the characters &, &&, or = :[bind variable]. Otherwise, the statements containing the variables will generate an error.

 

Specifying substitution variables in SQL statements

Use substitution variables for flexible SQL statements. Flexible SQL statements are a powerful way to improve productivity.

Feature Example Description

Use the & symbol followed by a variable name to specify a substitution variable.

&EMPNUM

You can use &EMPNUM as a valid substitution variable name.

An example of a SQL statement demonstrating the use of a substitution variable

SELECT *

FROM EMP

WHERE EMPNUM = ‘&EMPNUM’;

When you execute this SQL statement, the Code Editor prompts you to enter the value for the employee name. This allows you to create generic SQL statements that can be reused.

You can use substitution variables in any part of the SQL statement.

SELECT &COL1, &COL2

FROM &TAB;

When you execute this SQL statement, the Code Editor prompts you to enter the column names, as well as the table name.

You can use this concept to create other types of generic SQL statements or scripts. One practical application of this concept is creating a generic script for creating user codes at your site.

CREATE USER &&UNAME IDENTIFIED BY &PASS;

GRANT ALL ON EMP TO &&UNAME;

 

You can use the double ampersand the same way as the single ampersand with some differences.

&&UNAME

When you execute this SQL statement

  • The first time the Code Editor encounters the && variable, it looks up the variable to determine whether it has already been defined in either a DEFINE statement or in a previous && variable.
  • If the variable is defined, Code Editor substitutes the value in the SQL statement.
  • If the variable is undefined, the Code Editor prompts you to enter the value of the variable, defines the variable for future look-ups, and substitutes the value in the SQL statement.
  • Once a && variable is defined, you are no longer prompted to enter its value in the same session until you UNDEFINE the variable.

 

DEFINE and UNDEFINE

You can use the terms DEFINE and UNDEFINE to define and undefine numeric and character variables in SQL scripts.

Statement Example Description

DEFINE

DEFINE EMP_NAME=‘SCOTT’

DEFINE EMP_NUM=4467

Define a substitution variable.

A character/varchar substitution variable definition uses single quotes.

UNDEFINE

UNDEFINE EMP_NAME

Undefine a previously defined substitution variable.

TIP: Alternatively, right click in the editing pane and select SQL Script Options | Substitutions to open the Substitutions dialog.

New Stored Object Dialog

Select the object type:

Procedure

A procedure is a sequence of executable statements that performs a particular action. Procedures can be stored in the database (where they are also executed) and reused; they are then referred to as stored procedures. Stored procedures cannot be embedded in a SQL statement.

Function

A function is a block that returns a value. Functions can be stored in the database and reused. Stored functions can be called from within a SQL statement.

Package+Body

A package is an encapsulated collection of related schema objects, including modules and other constructs, such as cursors, variables, exceptions, and records. Packages allow procedures, functions, variables, and cursors that share common or related functions to be compiled and stored as a single schema object.

  • Packages allow encapsulation of internal subroutines and variables.
  • With packages, you can specify which code is publicly available to programmers and which data should be hidden. In addition, you can implement global variables, data structures, and values; these persist for the duration of a user session.
  • Packages have both a specification and a body. The package specification declares procedures, functions, cursors, and variables.
  • The package body contains the implementation of the public procedures and functions, together with internal and private programs and variables.

Type+Body

Object types are user-defined data types, equivalent to "classes" in object-oriented languages, that may consist of composite data types or collections such as repeating groups or complex record types. Object types may be associated with member functions and procedures that are implemented in PL/SQL. These modules implement the methods of the object type.

Like packages, object types have both a specification and a body.

  • The specification lists the object's attributes and member functions.
  • The body contains the actual code for the methods.

Trigger

A trigger is a named PL/SQL unit that is stored in the database and executed in response to a specified event that occurs in the database.

TIP: For each object type, SQL Navigator provides a ready made template or "shell" to make coding easier. You can modify these templates. The template name and location is defined in the opening comments when the new object is created.

SQL Statement CRUD Matrix Dialog

Insert a CRUD (Create/Update/Delete) worksheet into the code editor.

The CRUD matrix is inserted as commented text at the current cursor position. This can be a convenient way of documenting and analyzing your procedures.

SQL Query Results

SQL Development

The Code Editor opens ready to edit SQL code.

More Information Brief Description
Code Editor SQL The Code Editor toolbar in SQL development.
Edit, Compile And Execute Write SQL code. Compile the code.
SQL Query Results Data Grid Browse the results of executed SQL queries.
SQL Query Log (The Spool Tab) View a log of executed SQL statements. Retrieve executed SQL statements.

PL/SQL Development

The Code Editor layout for PL/SQL development is used when a stored object is opened or is being created.

More Information Brief Description
Code Editor PL/SQL The Code Editor toolbar in PL/SQL development.
Edit, Compile And Execute Write PL/SQL code. Compile the code.
PL/SQL Execution Console Set input parameters. Run the PL/SQL program.

Toolbox

Icon More Information Brief Description
Code Explorer Show a hierarchical view the code.
Outline Show the syntax tree of the current source.
DB Explorer Find and open database objects.
Describe Show the data structure for tables, indexes, views and synonyms.
History Show the most recent successfully executed SELECT, UPDATE, DELETE commands and PL/SQL blocks in the current session.
Dependencies Show the Dependants and Depends On objects of the current script.
Columns Show/hide columns of the retrieved table in the data grid.

PL/SQL Debugger

Tools and features for debugging stored programs.

Show/Hide the PL/SQL Debugger in the Toolbox from the Code Editor toolbar.

TIP:

  • Align the Toolbox left or right of the Code Editor (Right Click on the Toolbox).
  • Pin/Unpin the Toolbox to allow more editing space.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating