Define

Transformation for this command is pending

Description

The DEFINE command specifies a user or predefined variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables. (Oracle SQL Plus User's Guide and Reference)

Oracle Syntax

DEF[INE] [variable] | [variable = text]

SnowSQL (CLI Client) !define

!define [variable] | [variable=text]

Snowflake recommends not adding whitespace in the variable value assignment statement.

1. Define with simple variable assignment

This case is functionally equivalent.

The DEFINE command is replaced by the !define command.

Oracle

Command

DEFINE column_id = test

DEFINE column_id = &column_reference

SnowSQL (CLI Client)

Command

!define column_id = test

!define column_id = &column_reference

For referring to a previously defined variable, & is preceded by the name of the variable, if the variable does not exist, Oracle allows its execution time assignment, however, Snowflake would throw an error indicating the non-existence of said variable

2. Define without variable assignments

This case is not functionally equivalent.

Oracle

Command

DEFINE column_id

SnowSQL (CLI Client)

Command

!define column_id

The DEFINE command used without the assignment statement is used in Oracle to show the definition of the variable, on the other hand in Snowflake this way of using the DEFINE command would reset the assignment of the variable, so a way to simulate the behavior presented in Oracle it is by using the SELECT command.

This solution would be something like this:

Command

select '&column_id';

Known Issues

1. Enabling variable substitution

To enable SnowSQL CLI to substitute values for the variables, you must set the variable_substitution configuration option to true. This process can be done at installation, when starting a database instance, or by running the following command:

Command

!set variable_substitution=true

2. Predefined variables

There are nine predefined variables during SQL*Plus installation. These variables can be used later by the user. The SnowSQL CLI client only has two predefined variables __ROWCOUNT and __SFQID.