SQL Navigator for Oracle 8.0

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.

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

Code Editor > Edit, Compile And Execute > 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

Code Editor > Edit, Compile And Execute > 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

Code Editor

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.