DDL - DML Statements

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

All statements use the EXEC helper.

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 here.

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

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

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

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

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

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

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

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

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

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

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`);
$$;

Known Issues

No issues were found.

Related EWIs

No related EWIs.

1. SSC-EWI-0022: One or more identifiers in a specific statement are considered parameters by default.