SnowConvert AI - Oracle - PL/SQLからJavascriptへ¶

CREATE OR REPLACE PROCEDURE PROCEDURE_DEMO ()
   AS
   BEGIN
      SELECT 2 FROM DUAL;
      $IF $$debug_flag
      $THEN
         SELECT 1 FROM DUAL;
      $END
   END PROCEDURE_DEMO;

CREATE OR REPLACE PROCEDURE PROCEDURE_DEMO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT 2 FROM DUAL;
      !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'DOLLAR IF STATEMENT' NODE ***/!!!
      $IF $$debug_flag
      $THEN
         SELECT 1 FROM DUAL;
      $END
   END;
$$;

CREATE OR REPLACE PROCEDURE CONCAT_TEST
IS
NUM1 INTEGER := 123;
NUM2 INTEGER := 321;
VAR1 VARCHAR(10) := 'value';
concat_var VARCHAR(100);
sql_stmt VARCHAR(100);
BEGIN
    concat_var := NUM1 || NUM2 || VAR1 || 'literal';   
    sql_stmt := 'INSERT INTO t1 VALUES (''' || concat_var || ''')';    
    EXECUTE IMMEDIATE sql_stmt;
END;

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

    let NUM1 = 123;
    let NUM2 = 321;
    let VAR1 = `value`;
    let CONCAT_VAR;
    let SQL_STMT;
    CONCAT_VAR = `${concatValue(NUM1)}${concatValue(NUM2)}${concatValue(VAR1)}literal`;
    SQL_STMT = `INSERT INTO t1
VALUES ('${concatValue(CONCAT_VAR)}')`;
    EXEC(SQL_STMT);
$$;

CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name VARCHAR2, b_value  BOOLEAN)
IS
BOOL1 BOOLEAN := FALSE;
x NUMBER := 5;
y NUMBER := NULL;
BEGIN

  IF b_value IS NULL THEN
    null;
  ELSIF b_value = TRUE THEN
    null;
  ELSIF b_value = TRUE AND b_value = BOOL1  OR b_value = BOOL1 THEN
    null;
  ELSIF x > y THEN
    null;
  ELSIF x != y AND x <> y THEN
    null;
  ELSE
    null;
  END IF;
END;

CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name STRING, b_value BOOLEAN)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  
  // SnowConvert AI Helpers Code section is omitted.
  
  let BOOL1 = false;
  let X = 5;
  let Y = undefined;
  if (IS_NULL(B_VALUE)) {
    null;
  } else if (B_VALUE == true) {
    null;
  } else if (B_VALUE == true && B_VALUE == BOOL1 || B_VALUE == BOOL1) {
    null;
  } else if (X > Y) {
    null;
  } else if (X != Y && X != Y) {
    null;
  } else {
    null;
  }
$$;

CREATE OR REPLACE PROCEDURE NULL_TEST
IS
NUM1 INTEGER := 789;
BEGIN
    IF NUM1 IS NOT NULL THEN
        NULL;
    END IF;
    
    NUM1 := NULL;
    
    IF NUM1 IS NULL THEN
        NULL;
    END IF;
END;

CREATE OR REPLACE PROCEDURE NULL_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    // SnowConvert AI Helpers Code section is omitted.

    let NUM1 = 789;
    if (!IS_NULL(NUM1)) {
        null;
    }
    NUM1 = undefined;
    if (IS_NULL(NUM1)) {
        null;
    }
$$;

CREATE OR REPLACE PROCEDURE BOOLEAN_TEST
IS
done BOOLEAN;
BEGIN
  -- These WHILE loops are equivalent  
  done := FALSE;
  WHILE done = FALSE
    LOOP
      done := TRUE;
    END LOOP;    
 
  done := FALSE;
  WHILE NOT (done = TRUE)
    LOOP
      done := TRUE;
    END LOOP; 

  done := FALSE;
  WHILE NOT done
    LOOP
      done := TRUE;
    END LOOP;
END;

CREATE OR REPLACE PROCEDURE BOOLEAN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert AI Helpers Code section is omitted.
  
  let DONE;
  // These WHILE loops are equivalent  
  DONE = false;
  while ( DONE == false ) {
    DONE = true;
  }
  DONE = false;
  while ( !(DONE == true) ) {
    DONE = true;
  }
  DONE = false;
  while ( !DONE ) {
    DONE = true;
  }
$$;

CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST(DATEPARAM DATE)
IS
	STRING_VALUE VARCHAR(20) := 'HELLO';
BEGIN
	STRING_VALUE := TO_CHAR(123);
	STRING_VALUE := TO_CHAR(DATEPARAM, 'dd-mm-yyyy', 'NLS_DATE_LANGUAGE = language');
END;

CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST (DATEPARAM TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// SnowConvert AI Helpers Code section is omitted.

	let STRING_VALUE = `HELLO`;
	STRING_VALUE = (EXEC(`SELECT
   TO_CHAR(123)`))[0];
	STRING_VALUE = (EXEC(`SELECT
   !!!RESOLVE EWI!!! /*** SSC-EWI-OR0013 - NLS PARAMETER 'NLS_DATE_LANGUAGE = language' NOT SUPPORTED ***/!!!
   TO_CHAR(PUBLIC.CAST_DATE_UDF(?), 'dd-mm-yyyy')`,[DATEPARAM]))[0];
$$;

CREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)
RETURN VARCHAR
IS
    VAR1 VARCHAR(20);
    VAR2 VARCHAR(20);
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = 1;
    VAR2 := PAR1 || VAR1;
    RETURN VAR2 ;
END;

CREATE OR REPLACE FUNCTION FUN1 (PAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/13/2024",  "domain": "test" }}'
AS
$$
    WITH declaration_variables_cte1 AS
    (
        SELECT
            (
            SELECT COL1
            FROM
                TABLE1
            where col1 = 1) AS VAR1,
            NVL(PAR1 :: STRING, '') || NVL(VAR1 :: STRING, '') AS
            VAR2
    )
    SELECT
        VAR2
    FROM
        declaration_variables_cte1
$$;

CREATE OR REPLACE PACKAGE BODY pkg1 AS
FUNCTION f1(PAR1 VARCHAR) RETURN VARCHAR IS
    VAR1 VARCHAR(20);
    VAR2 VARCHAR(20);
  BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = 1;
    VAR2 := PAR1 || VAR1;
    RETURN VAR2 ;
  END f1;
END pkg1;

CREATE OR REPLACE FUNCTION pkg1.f1(PAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/13/2024",  "domain": "test" }}'
AS
$$
  WITH declaration_variables_cte1 AS
  (
    SELECT
      (
      SELECT COL1
      FROM
        TABLE1
      where col1 = 1) AS VAR1,
      NVL(PAR1 :: STRING, '') || NVL(VAR1 :: STRING, '') AS
      VAR2
  )
  SELECT
    VAR2
  FROM
    declaration_variables_cte1
$$;

CREATE VIEW VIEW1 AS SELECT FUN1(COL2) FROM TABLE1;
CREATE VIEW VIEW2 AS SELECT PKG1.F1(COL1) FROM TABLE1;

CREATE OR REPLACE VIEW VIEW1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
SELECT FUN1(COL2) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN1' NODE ***/!!! FROM
TABLE1;

CREATE OR REPLACE VIEW VIEW2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
SELECT PKG1.F1(COL1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PKG1.F1' NODE ***/!!! FROM
TABLE1;

CREATE OR REPLACE FUNCTION FUN1(x NUMBER) RETURN NUMBER IS
  VAR1 NUMBER;
  BEGIN
    -- FUN2 is another UDF
    VAR1 := FUN2(pkg1.f1(X, FUN2(10)));
    RETURN VAR1;
  END f1;

CREATE OR REPLACE FUNCTION FUN1 (x NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/13/2024",  "domain": "test" }}'
AS
$$
  WITH declaration_variables_cte1 AS
  (
    SELECT
      FUN2(pkg1.f1(X, FUN2(10) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'pkg1.f1' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!! AS
      -- FUN2 is another UDF
      VAR1
  )
  SELECT
    VAR1
  FROM
    declaration_variables_cte1
$$;

CREATE OR REPLACE FUNCTION FUN1(x NUMBER) RETURN NUMBER IS
  VAR1 NUMBER;
  BEGIN
    -- FUN2 is another UDF
    VAR1 := FUN2(X);
    RETURN VAR1;
  END f1;

CREATE OR REPLACE FUNCTION FUN1 (x NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/13/2024",  "domain": "test" }}'
AS
$$
  WITH declaration_variables_cte1 AS
  (
    SELECT
      FUN2(X) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!! AS
      -- FUN2 is another UDF
      VAR1
  )
  SELECT
    VAR1
  FROM
    declaration_variables_cte1
$$;

CREATE OR REPLACE FUNCTION FUN1(x NUMBER)
RETURN NUMBER IS
VAR1 NUMBER;
BEGIN
    VAR1 := VAR1 + 1;
    INSERT INTO TABLE1(col1, col2) VALUES(X, VAR1);
    UPDATE TABLE2 SET COL1 = VAR1 WHERE ID = X;
    RETURN VAR1;
END FUN1;

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert AI Helpers Code section is omitted.

    let VAR1;
    VAR1 = VAR1 + 1;
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`INSERT INTO TABLE1(col1, col2) VALUES(?, ?)`,[X,VAR1]);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`UPDATE TABLE2
       SET COL1 = ?
       WHERE ID = ?`,[VAR1,X]);
    return VAR1;
$$;

CREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)
RETURN VARCHAR
IS
    VAR1 VARCHAR(20);
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = PAR1;
    RETURN VAR1;
END;

CREATE OR REPLACE FUNCTION FUN1 (PAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/13/2024",  "domain": "test" }}'
AS
$$
    WITH declaration_variables_cte1 AS
    (
        SELECT
            (
            SELECT COL1
            FROM
                TABLE1
            where col1 = PAR1) AS VAR1
    )
    SELECT
        VAR1
    FROM
        declaration_variables_cte1
$$;

CREATE OR REPLACE FUNCTION FUN1(x NUMBER)
RETURN NUMBER IS
VAR1 NUMBER;
BEGIN
    IF x < 5 THEN
        VAR1 := 1;   
    ELSE
        VAR1 := 0;
    END IF;
    RETURN VAR1;
END FUNC01;

CREATE OR REPLACE FUNCTION FUN1 (x NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/13/2024",  "domain": "test" }}'
AS
$$
    WITH declaration_variables_cte1 AS
    (
        SELECT
            CASE
                WHEN x < 5
                    THEN 1
                ELSE 0
            END AS VAR1
    )
    SELECT
        VAR1
    FROM
        declaration_variables_cte1
$$;

CREATE OR REPLACE FUNCTION FUN1(x NUMBER)
RETURN NUMBER IS
VAR1 NUMBER;
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 WHERE ID = X;
    IF VAR1 < 5 THEN
        VAR1 := 1;   
    ELSE
        VAR1 := 0;
    END IF;
    UPDATE TABLE1 SET COL1 = VAR1 WHERE ID = X;
    RETURN VAR1;
END FUN1;

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert AI Helpers Code section is omitted.

    let VAR1;
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    [VAR1] = EXEC(`SELECT
   COL1
FROM
   TABLE1
WHERE ID = ?`,[X]);
    if (VAR1 < 5) {
        VAR1 = 1;
    } else {
        VAR1 = 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 ID = ?`,[VAR1,X]);
    return VAR1;
$$;

CREATE OR REPLACE FUNCTION FUN1(x FLOAT)
RETURN NUMBER IS
VAR1 NUMBER;
BEGIN
    IF TRUNC(X) < 5 THEN
        VAR1 := 1;   
    ELSE
        VAR1 := 0;
    END IF;
    RETURN VAR1;
END FUNC01;