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. |
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
|
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. |
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.
|
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.
|
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. |
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.
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. |
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. |
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. | |
Tools and features for debugging stored programs. Show/Hide the PL/SQL Debugger in the Toolbox from the Code Editor toolbar. |
|
TIP:
|
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center