DDL - DML Statements

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]);