SnowConvert AI - Oracle - SQL*Plus¶

Oracle Syntax¶

ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]

Snowflake does not have a direct equivalent to this command. To emulate this functionality, the SnowCLI!system command will be used by taking advantage of the system resources for the input operations.

1. Accept command¶

ACCEPT variable_name CHAR PROMPT 'Enter the variable value >'

!print Enter the value
!system read aux && echo '!define variable_name='"$aux" > sc_aux_file.sql 
!load sc_aux_file.sql
!system rm sc_aux_file.sql

Oracle Syntax¶

A[PPEND] text

Snowflake does not have a direct equivalent to this command. The Snowflake !edit command can be used to edit the last query using a predefined text editor. Whenever this approach does not cover all the APPEND functionality but it is an alternative.

1. Append command¶

APPEND SOME TEXT

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'APPEND STATEMENT' NODE ***/!!!
APPEND SOME TEXT;

Oracle Syntax¶

ARCHIVE LOG LIST

Snowflake does not have a direct equivalent to this command. The Snowflake !optionscommand can be used to display the location path of some log files, however, it does not fully comply with the behavior expected by the ARCHIVE LOG command. At transformation time, an EWI will be added.

1. Archive Log command¶

ARCHIVE LOG LIST

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ARCHIVE LOG STATEMENT' NODE ***/!!!
ARCHIVE LOG LIST;

Oracle Syntax¶

ATTR[IBUTE] [type_name.attribute_name [option ...]]

Snowflake does not have a direct equivalent to this command.

1. Attribute command¶

ATTRIBUTE Address.street_address FORMAT A10

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ATTRIBUTE STATEMENT' NODE ***/!!!
ATTRIBUTE Address.street_address FORMAT A10;

Oracle Syntax¶

BRE[AK] [ON report_element [action [action]]] ...

report_element := {column|expr|ROW|REPORT}

action := [SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]

Snowflake does not support the use of this command and does not have any that might resemble its functionality. At the time of transformation, an EWI will be added.

1. BREAK command¶

BREAK ON customer_age SKIP 5 DUPLICATES;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BREAK STATEMENT' NODE ***/!!!
BREAK ON customer_age SKIP 5 DUPLICATES;

Oracle Syntax¶

BTI[TLE] [printspec [text | variable] ...] | [ON | OFF]

Snowflake does not have a direct equivalent to this command.

1. Btitle command¶

BTITLE BOLD 'This is the banner title'

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTITLE STATEMENT' NODE ***/!!!
BTITLE BOLD 'This is the banner title';

Oracle Syntax¶

C[HANGE] sepchar old [sepchar [new [sepchar]]]

Snowflake does not have a direct equivalent to this command. The Snowflake !edit command can be used to edit the last query using a predefined text editor. Whenever this approach does not cover all the CHANGE functionality but it is an alternative.

1. Change command¶

CHANGE /old/new/

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CHANGE STATEMENT' NODE ***/!!!
CHANGE /old/new/;

Oracle Syntax¶

COL[UMN] [{column | expr} [option ...]]

Snowflake does not support the use of this command and does not have any that might resemble its functionality. At the time of transformation, an EWI will be added.

1. Column command¶

The COLUMN command with no clauses to list all current column display attributes.

COLUMN column_id ALIAS col_id NOPRINT

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'COLUMN STATEMENT' NODE ***/!!!
COLUMN column_id ALIAS col_id NOPRINT;

Oracle Syntax¶

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

!define [variable] | [variable=text]

1. Define with simple variable assignment¶

The DEFINE command is replaced by the !define command.

DEFINE column_id = test

DEFINE column_id = &column_reference

!define column_id = test

!define column_id = &column_reference

2. Define without variable assignments¶

DEFINE column_id

!define column_id

select '&column_id';

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.

Oracle Syntax¶

HO[ST] [command]

!system <command>

1. Set with simple variable assignment¶

The HOST command is replaced by the !system command.

HOST dir *.sql

!system dir *.sql

Oracle Syntax¶

PRO[MPT] [text]

!print [text]

1. Simple print¶

The PROMPT command is replaced by the !print command.

PROMPT

PROMPT text

PROMPT db_link_name = "&1"

!print

!print text

!print db_link_name = "&1"

Oracle Syntax¶

REM[ARK] comment

Snowflake does not have a direct equivalent for this command. However, some of its functionalities can be emulated.

1. Remark after the first line¶

When the REMARK command is not at the beginning of a script you can use the standard SQL comment markers and double hyphens.

SELECT 'hello world' FROM dual;
REMARK and now exit the session
EXIT;

select 'hello world';
-- and now exit the session
!exit

2. Remark on the first line¶

When the REMARK command is at the beginning of a script, scenarios could appear such as:

Case 1: The next line is a query, in which case the conversion to Snowflake of the REMARK command succeeds.

Case 2: The next line is another SQL*Plus command, in which case the conversion cannot be performed since Snowflake is not capable of executing either of the two statements (This also applies to the scenario where there is only one statement in the script statement that corresponds to the REMARK command).

Below are some examples, where the first two could not be translated correctly.

REMARK single line

REMARK first line
HOST dir *.sql

REMARK first line
SELECT 'hello world' FROM dual;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'REMARK STATEMENT' NODE ***/!!!
REMARK single line;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'REMARK STATEMENT' NODE ***/!!!
REMARK first line;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'HOST STATEMENT' NODE ***/!!!
HOST dir *.sql;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'REMARK STATEMENT' NODE ***/!!!
REMARK first line;
SELECT 'hello world' FROM dual;

Oracle Syntax¶

SET system_variable value

!set <option>=<value>

1. Set with simple variable assignment¶

The SET command is replaced by the !set command.

SET wrap on

!set wrap=true

2. Define without variable assignments¶

Oracle allows bypassing the key-value rule for assigning values to system variables with a numeric domain, assigning the value of 0 by default in such cases. In Snowflake this is not allowed, so an alternative is to set the value of 0 to a said variable explicitly.

SET pagesize

!set rowset_size=0

Oracle Syntax¶

SHO[W] system_variable  ALL BTI[TLE]  CON_ID  CON_NAME EDITION  ERR[ORS] [ {ANALYTIC VIEW | ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]HISTORY  LNO  LOBPREF[ETCH]  PARAMETER[S] [parameter_name]  PDBS PNO  RECYC[LEBIN] [original_name]  REL[EASE]  REPF[OOTER]  REPH[EADER]  ROWPREF[ETCH] SGA SPOO[L]  SPPARAMETER[S] [parameter_name]  SQLCODE STATEMENTC[ACHE] TTI[TLE] USER XQUERY

Snowflake does not have a direct equivalent for this command. However, some of its functionalities can be emulated.

1. Show ERRORS¶

Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). After you use the CREATE command to create a stored procedure, a message is displayed if the stored procedure has any compilation errors.

In Snowflake, performing an extra statement to display all the compilation errors is unnecessary. The compilation errors are displayed immediately when executing the CREATE statement.

CREATE OR REPLACE PROCEDURE RANCOM_PROC
AS
BEGIN
  INSERT INTO NE_TABLE SELECT 1 FROM DUAL;
END;

SHOW ERRORS

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
4/10     PL/SQL: ORA-00925: missing INTO keyword

CREATE OR REPLACE PROCEDURE RANCOM_PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    INSERT INTO NE_TABLE
    SELECT 1 FROM DUAL;
  END;
$$;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SHOW STATEMENT' NODE ***/!!!

SHOW ERRORS;

001003 (42000): SQL compilation error:
syntax error line 3 at position 7 unexpected 'INT'.
syntax error line 3 at position 11 unexpected 'PUBLIC'.

Show ALL¶

Lists the settings of all SHOW options, except ERRORS and SGA, in alphabetical order.

To display all the possible options in SnowCLI you can run the !options command.

show all;

appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colinvisible OFF
coljson OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
errorlogging is OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON for 6 or more rows SQL_ID OFF
flagger OFF
flush ON
fullcolname OFF
heading ON
headsep "|" (hex 7c)
history is OFF
instance "local"
jsonprint NORMAL
linesize 80
lno 5
loboffset 1
lobprefetch 0
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
markup CSV OFF DELIMITER , QUOTE ON
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 2103000000
repfooter OFF and is NULL
repheader OFF and is NULL
rowlimit OFF
rowprefetch 1
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 21.0.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
statementcache is 0
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SYSTEM"
verify ON
wrap : lines will be wrapped
xmloptimizationcheck OFF

!options

Oracle Syntax¶

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

!spool [<file_name>] | [off]

1. Spool without options¶

When the SPOOL command is not accompanied by any option, by default it creates a new file with the specified name and extension. The SPOOL command is replaced by the !spool command.

SPOOL temp
SPOOL temp.txt

!spool temp
!spool temp.txt

2. Spool with write options¶

Oracle allows 3 types of options when writing to a file through the SPOOL command, the CREATE and APPEND options create a file for writing from scratch and concatenate text to the end of an existing file (or create a new one if it doesn’t exist) respectively. Snowflake does not support these options, however, its default behavior is to create a file and if it exists, concatenate the text in it. The REPLACE option, on the other hand, writes to the specific file replacing the existing content. To simulate this behavior in Snowflake it is recommended to delete the file where you want to write and start writing again, as shown in the following code

SPOOL temp.txt CREATE
SPOOL temp.txt APPEND
SPOOL temp.txt REPLACE

!spool temp.txt
!spool temp.txt

!system del temp.txt
!spool temp.txt

3. Spool turn off¶

Oracle has two options to turn off results spooling, OFF and OUT. both are meant to stop rolling, with the difference that the second also sends the file to the computer’s standard (default) printer. This option is not available on some operating systems. Snowflake only has the option to turn off results spooling

SPOOL OFF
SPOOL OUT

!spool off
!spool off

Oracle Syntax¶

STA[RT] {url | file_name[.ext] } [arg...]

!(load | source) {url | file_name[.ext] }

1. Simple start¶

The START command is replaced by the !load command.

START C:\Users\My_User\Desktop\My\Path\insert_script.sql

!load C:\Users\My_User\Desktop\My\Path\insert_script.sql

2. Start with arguments¶

START C:\Users\My_User\Desktop\My\Path\insert_script.sql 123 456 789

!load C:\Users\My_User\Desktop\My\Path\insert_script.sql

Oracle Syntax¶

WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable]  [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

Snowflake does not support the use of this command and does not have any that might resemble its functionality. At the time of transformation, an EWI will be added.

1. Whenever oserror command¶

WHENEVER OSERROR EXIT

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'WHENEVER ERROR STATEMENT' NODE ***/!!!
WHENEVER OSERROR EXIT;

Oracle Syntax¶

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable  | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

Snowflake does not support the use of this command and does not have any that might resemble its functionality. At the time of transformation, an EWI will be added.

1. Whenever sqlerror command¶

WHENEVER SQLERROR EXIT

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'WHENEVER ERROR STATEMENT' NODE ***/!!!
WHENEVER SQLERROR EXIT;