SnowConvert - Translation Spec
  • For Teradata
  • Translation Reference
    • Teradata
      • Scripts to Snowflake SQL Translation Reference
        • COMMON STATEMENTS
          • ERROR HANDLING
          • EXIT or QUIT
          • GOTO
          • IF... THEN...
        • BTEQ
        • MLOAD
          • Import
      • Scripts To Python Translation Reference
        • BTEQ
          • REPEAT
          • USING REQUEST MODIFIER
        • FLOAD
          • BEGIN LOADING
        • MLOAD
          • BEGIN MLOAD
        • TPT
        • SnowConvert Scripts Helpers
          • Technical Documentation
      • SQL Translation Reference
        • Built-in Functions
          • CURRENT_TIMESTAMP
          • Cast to DATE using { }
          • Cast to INTERVAL datatype
          • COALESCE
          • DAYNUMBER_OF_MONTH
          • FROM_BYTES
          • GETQUERYBANDVALUE
          • JSON_CHECK
          • JSON_EXTRACT
          • JSON_TABLE
          • NEW JSON
          • NVP
          • OVERLAPS
          • PIVOT
          • P_INTERSECT
          • RANK
          • Regex functions
          • STRTOK_SPLIT_TO_TABLE
          • SUBSTRING
          • TD_UNPIVOT
          • TO_CHAR
          • XMLAGG
        • Database DBC
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • Expand On Clause
            • Normalize
            • Reset When
          • Set Operators
          • Update Statement
          • With Modifier
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
      • Teradata to JavaScript Translation Reference
        • GET DIAGNOSTICS EXCEPTION
        • Macros
        • Procedures
      • Teradata to Snowflake Scripting Translation Reference
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CREATE MACRO
        • CREATE PROCEDURE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DML and DDL Objects
        • EXCEPTION HANDLERS
        • EXECUTE IMMEDIATE
        • EXECUTE/EXEC
        • FUNCTION OPTIONS OR DATA ACCESS
        • GET DIAGNOSTICS EXCEPTION
        • IF
        • LOCKING FOR ACCESS
        • LOOP
        • OUTPUT PARAMETERS
        • PREPARE
        • REPEAT
        • SET
        • SYSTEM_DEFINED
        • WHILE
    • Oracle
      • Sample data
      • Basic Elements of Oracle SQL
        • Data Types
          • Oracle Built-in Data Types
            • Character Data Types
              • CHAR Data type
              • NCHAR Data Type
              • VARCHAR2 Data Type
              • VARCHAR Data Type
              • NVARCHAR2 Data Type
            • Numeric Data Types
              • NUMBER Data Type
              • FLOAT Data Type
              • Floating-Point Numbers
                • BINARY_FLOAT
                • BINARY_DOUBLE
            • LONG Data Type
            • Datetime and Interval Data Types
              • DATE Data Type
              • TIMESTAMP Data Type
              • TIMESTAMP WITH TIME ZONE Data Type
              • TIMESTAMP WITH LOCAL TIME ZONE Data Type
              • INTERVAL YEAR TO MONTH Data Type
              • INTERVAL DAY TO SECOND Data Type
              • Datetime Arithmetic
                • Interval UDFs vs Snowflake native interval operation
            • LOB Data Types
              • BFILE Data Type
              • BLOB Data Type
              • CLOB Data Type
              • NCLOB Data type
            • JSON Data Type
            • Extended Data Types
            • RAW and LONG RAW Data types
            • PL SQL Data Types
              • PLS_INTEGER Data Type
              • BINARY_INTEGER Data Type
          • Rowid Data Type
            • ROWID DataType
            • UROWID Data Type
          • ANSI Data Types
          • User-Defined Types
            • REF Data Types
          • Any Types
            • ANYTYPE
            • ANYDATA
            • ANYDATASET
          • XML Types
            • XMLType
            • URI Data Types
              • HTTPURIType
              • XDBURIType
              • DBURIType
            • URIFactory Package
          • Spatial Types
            • SDO_GEOMETRY
            • SDO_TOPO_GEOMETRY
            • SDO_GEORASTER
        • Literals
          • Interval Literal
          • Interval Type and Date Type
          • Text literals
      • Pseudocolumns
        • ROWID
        • ROWNUM
      • Built-in functions
        • SnowConvert Custom UDFs
          • BFILENAME UDF
          • DATE_TO_JULIANDAYS_UDF
          • DATEADD UDF
          • DATEDIFF UDF
          • INTERVAL UDFs
            • DATEADD UDF INTERVAL
            • DATEDIFF UDF INTERVAL
          • CAST_DATE UDF
          • JSON_VALUE UDF
          • JULIAN TO GREGORIAN DATE UDF
          • MONTHS BETWEEN UDF [DEPRECATED]
          • REGEXP LIKE UDF
          • TIMESTAMP DIFF UDF
          • TRUNC (date) UDF
          • TRUNC (number) UDF
        • TO_NUMBER
        • NLSSORT
      • Built-In packages
        • DBMS_LOB
          • SUBSTR Function
        • DBMS_RANDOM
          • VALUE functions
        • DBMS_OUTPUT
          • PUT_LINE procedure
        • UTL_FILE
          • FOPEN procedure
          • PUT_LINE procedure
          • FCLOSE procedure
      • SQL Queries and Subqueries
        • Select
          • Select Flashback Query
        • Joins
          • Equijoin
          • Band Join
          • Self Join
          • Cartesian Products
          • Inner Join
          • Outer Join
          • Antijoin
          • Semijoin
      • SQL Statements
        • Alter Session
        • Alter Table
        • Create Materialized Views
        • Create Database Link
        • Create Index
        • Create Sequence
        • Create Synonym
        • Create Table
        • Create Type
          • Object Type Definition
          • Subtype Definition
          • Array Type Definition
          • Nested Table Type Definition
          • Member Function Definitions
        • Create View
        • Drop Table
      • PL/SQL to Snowflake Scripting
        • ASSIGNMENT STATEMENT
        • CALL
        • CASE
        • COLLECTIONS AND RECORDS
          • Associative Array Type Definition
          • Varray Type Definition
          • Nested Table Array Type Definition
          • Collection Methods
          • Collection Bulk Operations
            • WITH, SELECT, and BULK COLLECT INTO statements
          • Record Type Definition
        • COMPOUND STATEMENTS
        • CONTINUE
        • CREATE PROCEDURE
        • CURSOR
          • PARAMETRIZED CURSOR
          • CURSOR DECLARATION
          • Workaround for cursors using parameters or procedure variables
          • Cursor Variables
        • DECLARE
        • DEFAULT PARAMETERS
        • DML STATEMENTS
          • INSERT Statement Extension
          • MERGE Statement
          • SELECT INTO Statement
          • Work around to simulate the use of Records
        • EXIT
        • EXPRESSIONS
        • EXECUTE IMMEDIATE
        • FORALL
        • FOR LOOP
        • HELPERS
          • Bulk Cursor Helpers
        • IF
        • IS EMPTY
        • LOCK TABLE
        • LOG ERROR
        • LOOP
        • OUTPUT PARAMETERS
        • PACKAGES
          • DECLARATION
          • BODY
          • VARIABLES
          • Constants
        • PROCEDURE CALL
        • RAISE
        • RAISE_APPICATION_ERROR
        • UDF CALL
        • WHILE
      • PL/SQL to Javascript
        • Procedures
        • User defined functions
        • Packages
        • Helpers
          • EXEC Helper
          • Cursor Helper
          • Raise Helper
          • ROWTYPE Helper
          • Between operator helper
          • Like operator Helper
          • IS NULL Helper
          • Concat Value Helper
          • Package variables helper
          • Implicit Cursor attribute helper
        • Declarations
        • Control Statements
        • Conditional Compilation
        • Collections & Records
        • DDL - DML Statements
        • SQL Language Elements
        • Expressions and operators
        • Synonyms
        • Triggers
        • TYPE attribute
      • SQL*Plus
        • Archive Log
        • Attribute
        • Break
        • Btitle
        • Change
        • Column
        • Define
        • Host
        • Prompt
        • Remark
        • Set
        • Spool
        • Start
        • Whenever oserror
        • Whenever sqlerror
        • Show
        • Append
        • Accept
      • Wrapped objects
    • SQLServer
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
        • Index
        • Views
        • Procedures
        • Functions
      • DMLs
        • Set Operators
        • Between
        • Update
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • Drop
        • Bulk Insert
        • Common Table Expression (CTE)
        • Drops
      • Data Types
      • Statements
        • ALTER
          • TABLE
            • ADD
              • COLUMN DEFINITION
                • COLUMN CONSTRAINT
                  • FOREIGN KEY
                  • PRIMARY KEY / UNIQUE
                  • CHECK
              • TABLE CONSTRAINT
                • FOREIGN KEY
                • PRIMARY KEY
                • CHECK CONSTRAINT
                • CHECK
                • CONNECTION
                • DEFAULT
                • ON PARTITION
        • CREATE
          • FUNCTION
            • SCALAR
            • INLINE TABLE-VALUED
            • MULTI-STATEMENT TABLE-VALUED
      • Built-in functions
        • SnowConvert custom UDFs
          • OPENXML UDF
          • STR UDF
          • SWITCHOFFSET_UDF
        • Aggregate functions
          • COUNT
          • COUNT_BIG
          • SUM
        • Analytic Functions
          • LAG
        • Conversion functions
          • CONVERT
          • TRY_CONVERT
        • Data Type functions
          • DATALENGTH
        • Date & Time functions
          • AT TIME ZONE
          • SWITCHOFFSET
          • DATEADD
          • DATEDIFF
          • DATEPART
          • DATEFROMPARTS
          • DATENAME
          • DAY
          • EOMONTH
          • GETDATE
          • MONTH
          • SYSDATETIME
          • SYSUTCDATETIME
          • YEAR
        • Logical functions
          • IIF
        • Mathematical functions
          • ABS
          • ACOS
            • ACOS in JS
          • ASIN
            • ASIN in JS
          • ATAN
            • ATAN in JS
          • ATN2
            • ATAN2 in JS
          • AVG
          • CEILING
          • COS
            • COS in JS
          • COT
            • COT in JS
          • DEGREES
            • DEGREES in JS
          • EXP
            • EXP in JS
          • FLOOR
          • LOG
            • LOG in JS
          • LOG10
            • LOG10 in JS
          • PI
            • PI in JS
          • POWER
            • POW in JS
          • SQUARE
          • STDEV
          • STDEVP
          • VAR
          • POWER
          • RADIANS
            • RADIANS in JS
          • ROUND
          • SQRT
        • Metadata functions
          • DB_NAME
          • OBJECT_ID
        • Ranking functions
          • DENSE_RANK
          • RANK
          • ROW_NUMBER
        • String functions
          • ASCII
            • ASCII in JS
          • CHAR
          • CHARINDEX
          • COALESCE
          • CONCAT
          • CONCAT_WS
            • Join in JS
          • DIFFERENCE
            • DIFFERENCE in JS
          • FORMAT
            • FORMAT in JS
          • LEFT
          • LEN
          • LOWER
          • LTRIM
            • LTRIM in JS
          • NCHAR
          • PATINDEX
            • search in JS
          • QUOTENAME
            • QUOTENAME in JS
          • REPLACE
          • REPLICATE
          • REVERSE
            • reverse in JS
          • RIGHT
          • RTRIM
          • SOUNDEX
            • SOUNDEX in JS
          • SPACE
          • STR
            • STR in JS
          • STRING_ESCAPE
            • stringify in JS
          • SUBSTRING
          • TRIM
            • trim in JS
          • UPPER
        • System functions
          • FORMATMESSAGE
            • FORMATMESSAGE_UDF
          • ISNULL
          • NEWID
          • NULLIF
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • DMLs
        • CALL
        • WHILE
        • DROPs
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • SELECT
        • TEXTIMAGE_ON
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • TOP
Powered by GitBook
On this page
  • SELECT
  • SELECT INTO
  • INSERT and INSERT INTO SELECT
  • DELETE
  • UPDATE
  • MERGE
  • EWIs related
  1. Translation Reference
  2. Oracle
  3. PL/SQL to Javascript

DDL - DML Statements

PreviousCollections & RecordsNextSQL Language Elements

Last updated 1 year ago

Some parts in the output code are omitted for clarity reasons.

All statements use the

SELECT

Oracle

IN -> Oracle_01.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1 (param1 VARCHAR)
IS
    VAR1 NUMBER := 789;
BEGIN
    SELECT * FROM TABLE01;
    SELECT DISTINCT COL1 FROM TABLE01;
    SELECT * FROM TABLE01 WHERE COL1 = VAR1;
    SELECT * FROM TABLE01 WHERE COL1 = PARAM1;
    SELECT * FROM TABLE01 WHERE COL1 = PARAM1 AND COL2 = VAR1;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them

OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE PROC1 (param1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.
    
    let VAR1 = 789;
    EXEC(`SELECT * FROM
       TABLE01`);
    EXEC(`SELECT DISTINCT COL1 FROM
       TABLE01`);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`SELECT * FROM
       TABLE01
    WHERE COL1 = ?`,[VAR1]);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`SELECT * FROM
       TABLE01
    WHERE COL1 = ?`,[PARAM1]);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`SELECT * FROM
       TABLE01
    WHERE COL1 = ?
       AND COL2 = ?`,[PARAM1,VAR1]);
$$;

SELECT INTO

Oracle

IN -> Oracle_02.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1 (param1 VARCHAR, param2 VARCHAR)
IS
    VAR1 NUMBER;
    VAR2 NUMBER;
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE01;
    SELECT COL1 INTO VAR1 FROM TABLE01 WHERE COL2 = PARAM1;
    SELECT COL1 INTO VAR1, VAR2 FROM TABLE01;
    SELECT COL1 INTO VAR1, VAR2 FROM TABLE01
        WHERE COL2 = param1 AND COL3 = param1;
END

Snowflake

OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE PROC1 (param1 STRING, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.
    
    let VAR1;
    let VAR2;
    [VAR1] = EXEC(`SELECT
   COL1
FROM
   TABLE01`);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    [VAR1] = EXEC(`SELECT
   COL1
FROM
   TABLE01
WHERE COL2 = ?`,[PARAM1]);
    [VAR1,VAR2] = EXEC(`SELECT
   COL1
FROM
   TABLE01`);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    [VAR1,VAR2] = EXEC(`SELECT
   COL1
FROM
   TABLE01
       WHERE COL2 = ?
   AND COL3 = ?`,[PARAM1,PARAM1]);
$$;

INSERT and INSERT INTO SELECT

Oracle

IN -> Oracle_03.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1 (param1 VARCHAR)
IS
    var1 NUMBER := 789;
BEGIN
    INSERT INTO TABLE01 VALUES('name', 123);
    INSERT INTO TABLE01 VALUES(param1, 456);
    INSERT INTO TABLE01 VALUES(param1, var1);
    INSERT INTO TABLE01 (col1, col2)
    SELECT col1, col2 FROM TABLE02 tb2
    WHERE tb2.col1 = 'myName';
END;

Snowflake

OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE PROC1 (param1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.
    
    let VAR1 = 789;
    EXEC(`INSERT INTO TABLE01
    VALUES('name', 123)`);
    EXEC(`INSERT INTO TABLE01
    VALUES(?, 456)`,[PARAM1]);
    EXEC(`INSERT INTO TABLE01
    VALUES(?, ?)`,[PARAM1,VAR1]);
    EXEC(`INSERT INTO TABLE01(col1, col2)
    SELECT col1, col2 FROM
       TABLE02 tb2
    WHERE tb2.col1 = 'myName'`);
$$;

DELETE

Oracle

IN -> Oracle_04.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1 (PARAM1 VARCHAR)
IS
    VAR1 NUMBER := 0;
BEGIN
    DELETE FROM TABLE1 WHERE COL2 = 1;
    DELETE FROM TABLE1 WHERE COL2 = VAR1;
    DELETE FROM TABLE1 WHERE COL1 = PARAM1;
END;

Snowflake

OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE PROC1 (PARAM1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // SnowConvert Helpers Code section is omitted.
   
    let VAR1 = 0;
    EXEC(`DELETE FROM
       TABLE1
    WHERE COL2 = 1`);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`DELETE FROM
       TABLE1
    WHERE COL2 = ?`,[VAR1]);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`DELETE FROM
       TABLE1
    WHERE COL1 = ?`,[PARAM1]);
$$;

UPDATE

Oracle

IN -> Oracle_05.sql
--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE PROC1(PARAM1 VARCHAR)
IS
    VAR1 NUMBER := 3;
BEGIN
    UPDATE TABLE1 SET COL2 = 1 where COL2 = 0;
    UPDATE TABLE1 SET COL1 = VAR1 where COL1 = 0;
    UPDATE TABLE1 SET COL1 = 'name' where COL1 = PARAM11;
    UPDATE TABLE1 SET COL2 = VAR1 where COL1 = PARAM1;
END;

Snowflake

OUT -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE PROC1 (PARAM1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // SnowConvert Helpers Code section is omitted.

    let VAR1 = 3;
    EXEC(`UPDATE TABLE1
       SET COL2 = 1 where COL2 = 0`);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`UPDATE TABLE1
       SET COL1 = ?
       where COL1 = 0`,[VAR1]);
    EXEC(`UPDATE TABLE1
       SET COL1 = 'name' where COL1 = PARAM11`);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`UPDATE TABLE1
       SET COL2 = ?
       where COL1 = ?`,[VAR1,PARAM1]);
$$;

MERGE

Oracle

IN -> Oracle_06.sql
--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
	MERGE INTO TABLE01 t01
	USING TABLE02 t02
		ON (t01.col2 = t02.col2)
	WHEN MATCHED THEN 
		UPDATE SET t01.col1 = t02.col2;
END;

Snowflake

OUT -> Oracle_06.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// SnowConvert Helpers Code section is omitted.
	
	EXEC(`MERGE INTO TABLE01 t01
	USING TABLE02 t02
		ON (t01.col2 = t02.col2)
		WHEN MATCHED THEN
		   UPDATE SET t01.col1 = t02.col2`);
$$;

EWIs related

  1. SSC-EWI-0022: ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND.

SnowConvert helpers Code removed from the example. You can find them

SnowConvert helpers Code removed from the example. You can find them

SnowConvert helpers Code removed from the example. You can find them

SnowConvert helpers Code removed from the example. You can find them

SnowConvert helpers Code removed from the example. You can find them

EXEC helper.
here.
here.
here.
here.
here.
here.