PL/SQL Context Prompt - Code More Efficiently!

dbForge Studio for Oracle and its embedded PL/SQL prompts give you a chance to implement code routines rationally. PL/SQL context prompt accelerates the writing of SQL code by offering a list of database objects and PL/SQL statements to be inserted, based on the context of what you type in the SQL editor.

Table of Contents

Quick selection of the column list

There is a possibility to use the Column Picker in the SELECT statement to simplify required columns selection.

Context Prompt in Oracle PL/SQL

Table expressions prompt

While writing the FROM statement, the table objects (such as tables or views) are listed in the context-dependent suggestion list. The objects related to the current schema are located on the top of the list.

Table Expressions Prompt

Phrase completion

The application suggests not only separate keywords and object names, but entire code phrases. For instance, INNER JOIN is prompted in the SELECT query. For a complete list of supported phrases, see product documentation.

Phrase Completion

Automatic generation of JOIN statements

While combining tables you can use automatically generated JOIN statements based on the foreign keys. In case the JOIN operation requires custom conditions, you can select required table objects manually.

Automatic Generation of JOIN Statements

JOIN conditions prompt

In case a foreign key joins two tables, there is a possibility to select automatically generated join condition. Optionally you can set this condition based on the columns used in the query. Object prompts that return the result (e.g. custom functions) are also available.

JOIN Conditions Prompt

WHERE conditions prompt

WHERE section displays the list of columns grouped by aliases. In addition the list contains objects that return results (e.g. custom functions) and user-defined custom columns.

WHERE Conditions Prompt

Columns prompt

While creating queries the application analyzes current location in order to display columns aliases correctly.

Columns Prompt

Columns prompt inside functions

While calling an object that returns the result, the Parameter Info tooltip shows the list of available parameters. In case the object is a system function, a short summary is additionally displayed.

Columns Prompt Inside Functions

DELETE FROM prompt

The prompt of available values in any DML prompt is based on the current code block. For instance, the top-priority in WHERE block (while deleting an entry) will be the column list of the initial table.

DELETE FROM Prompt

Prompt on selected columns

When writing the INSERT statement, you can select the columns to insert the data in. To perform the query automatically, only specify the required columns.

Prompt on Selected Columns

UPDATE prompt

Since update queries are mostly related to tables, the suggestion list displays tables first and then views.

UPDATE Prompt

Selection for a different schema

The suggestion list displays objects from the default schema. To access objects from other schemas, simply select a required schema name in the list.

Selection for a Different Schema

Cursors prompt

The suggestion list shows cursor variables and objects that store cursor variables (for example, packages).

Prompt on Cursors

Prompting cursors with parameters

Specify a cursor parameter value to display the prompt.

Prompt on Cursors with Parameters

RECORD fields prompt

If a variable is used inside a cursor, it is implicitly considered as the RECORD type variable. Here you can use the prompt for such variables.

Prompt on RECORD fields

%ROWTYPE attribute for the RECORD prompt

You can use prompts for variables that are declared via the % attribute. You can refer to a current script object or to an object specified inside another object.

%ROWTYPE Attribute for the RECORD Prompt

Prompting RECORD type variables

There is a possibility to declare the RECORD data type and use prompts for var fields.

Prompt on RECORD Type Variables

REF-CURSOR variables prompt

The prompt allows you to specify a variable type declared in another object. Even if you do not create an object in the database but describe one in the current script, the application will also show the prompt on objects.

REF-CURSOR Variables Prompt

SELECT INTO variables prompt

You can initialize a variable using the SELECT INTO statement.

Prompt on Variables inside SELECT INTO

Assign a value to a variable

You can assign a value to a variable in a number of ways, however the best practice is to use an assignment operator. In this case you can use prompts for all objects that return result.

Assign a Value to a Variable

Variable initialization prompt

You can specify an initial value when declaring a variable. The prompt displays objects that can return results. If the current anonymous block is enclosed, the suggestion list displays the cursor variables.

Variable Initialization Prompt

Function parameters prompt

When describing a user-defined function, the application suggests the corresponding function parameters. The prompt also shows all valid objects that include functions.

Prompt on Function Parameters

Stored Procedures parameters prompt

Like a function, a procedure supports the prompt on its parameters. When analyzing the parameters, you can divide them into input and output ones.

Prompt on Stored Procedures Parameters

Recursive calls prompt

Similar objects are listed as script objects, while calling a function or a procedure recursively.

Recursive Calls Prompt

Prompting procedures declared in packages

When a statement waits for a call that does not return a result, the prompt on procedures described in the call is supported for packages.

Prompt on Procedures Declared in Packages

Prompting package functions and variables

Prompting for internal functions and variables is available for packages.

Prompt on Package Functions and Variables

SEQUENCE built-in functions prompt

Prompt on all built-in SEQUENCE functions is supported. Select one of them, and the context prompt will be displayed automatically.

SEQUENCE Built-in Functions

Prompting built-in methods of the table type

When working with the table types, the prompt on built-in functions is supported. The prompt for each built-in function is integrated.

Prompt on Built-in methods of Table Type

Prompting built-in array methods

In spite of the user type declaration, the prompt on built-in functions is suggested for efficient work with an array.

Prompt on Built-in Array Methods

SELF-type variables prompt

When using the SELF-type variable, the application displays the context prompt on object variables and methods.

Prompt on SELF-type variables

Prompting objects inside FETCH INTO

While processing the cursor records, try out the variables prompt and transfer the current row value to a variable.

Prompt on Objects inside FETCH INTO

User-defined types prompt

The prompts on built-in and user-defined variables are similar. When displaying variables, their types are specified, even if a type and a variable are specified in the same block.

Prompt on User-Defined Types

Prompting objects inside FOR IN

While iterating records in the FOR IN statement, you can specify lower and upper limits. The prompt for all objects that can return the results is available.

Prompt on Objects inside FOR IN

Variables prompt in the VALUES block

The VALUES statement displays the suggestion list of variables with the RECORD type priority on the top of the list.

Variables Prompt in the VALUES Block

Objects prompt in the user-defined package

User-defined packages support a complete prompt on objects. Due to supported work with an object script description, it is not necessary to create an object in a database.

Objects Prompt in the User-Defined Package

CREATE TABLE prompt

The CREATE TABLE statement is completely supported. All possible objects are prompted.

Prompt inside CREATE TABLE

CREATE VIEW prompt

The context objects prompt depends on a block where the user creates a script object.

Prompt inside CREATE VIEW

Prompting objects in the DROP statement

Commonly the DROP statements support the prompt list on objects and schemas names. The types of deleting objects define the items order in the suggestion list.

Prompting Objects in the DROP statement

Prompting NEW/OLD variables columns

When you create a trigger in a table, the prompt on NEW/OLD variables columns is shown.

Prompting NEW/OLD Variables Columns

Triggers prompt

When coding a trigger body, the suggestion list displays objects and trigger variables of the corresponding statement.

Prompt on Triggers

Objects prompt inside the ALTER statement

To display the objects suggestion list, specify a type of the ALTER statement.

Objects Prompt inside the ALTER Statement

Clauses prompt of the MERGE statement

The prompt suggests clauses of the MERGE statement, that determine whether to update or insert into the target table or view.

Clauses prompt of the MERGE statement