SnowConvert AI - Oracle - PL/SQL to Javascript¶

CREATE OR REPLACE PROCEDURE RECORDS_PROC AS
 TYPE DEPTRECTYP IS RECORD (
    DEPT_ID    NUMBER(4) NOT NULL := 10,
    DEPT_NAME  VARCHAR2(30) NOT NULL := 'ADMINISTRATION',
    MGR_ID     NUMBER(6) := 200,
    LOC_ID     NUMBER(4) := 1700
  );

  TYPE NAME_REC IS RECORD (
    FIRST  EMPLOYEES.FIRST_NAME%TYPE,
    LAST   EMPLOYEES.LAST_NAME%TYPE
  );

  TYPE CONTACT IS RECORD (
    NAME  NAME_REC,-- NESTED RECORD
    PHONE EMPLOYEES.PHONE_NUMBER%TYPE
  );

  DEPT1 DEPTRECTYP;
  DEPT_NAME DEPTRECTYP;
  C1 CONTACT;
BEGIN
  DEPT1.DEPT_NAME := 'PURCHASING';
  C1.NAME.FIRST := 'FALVARADO';
  C1.PHONE := '50687818481';
  SELECT * INTO DEPT1 FROM FTABLE46;
  INSERT INTO TABLA1 VALUES (DEPT1.DEPT_NAME);
  INSERT INTO TABLA1 VALUES (DEPT_NAME.DEPT_NAME);
  EXECUTE IMMEDIATE 'SELECT * FROM FTABLE46' INTO DEPT_NAME;
END;

CREATE OR REPLACE PROCEDURE RECORDS_PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
  TYPE DEPTRECTYP IS RECORD (
     DEPT_ID    NUMBER(4) NOT NULL := 10,
     DEPT_NAME  VARCHAR2(30) NOT NULL := 'ADMINISTRATION',
     MGR_ID     NUMBER(6) := 200,
     LOC_ID     NUMBER(4) := 1700
   );
  !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!

   TYPE NAME_REC IS RECORD (
     FIRST  EMPLOYEES.FIRST_NAME%TYPE,
     LAST   EMPLOYEES.LAST_NAME%TYPE
   );
  !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!

   TYPE CONTACT IS RECORD (
     NAME  NAME_REC,-- NESTED RECORD
     PHONE EMPLOYEES.PHONE_NUMBER%TYPE
   );

   DEPT1 OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - DEPTRECTYP DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
   DEPT_NAME OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - DEPTRECTYP DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
   C1 OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - CONTACT DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
 BEGIN
  DEPT1 := OBJECT_INSERT(DEPT1, 'DEPT_NAME', 'PURCHASING', true);
  C1 := OBJECT_INSERT(C1, 'FIRST', 'FALVARADO', true);
  C1 := OBJECT_INSERT(C1, 'PHONE', '50687818481', true);
  SELECT
   OBJECT_CONSTRUCT( *) INTO
   :DEPT1
  FROM
   FTABLE46;
  INSERT INTO TABLA1
  SELECT
   :DEPT1.DEPT_NAME:DEPT_ID,
   :DEPT1.DEPT_NAME:DEPT_NAME,
   :DEPT1.DEPT_NAME:MGR_ID,
   :DEPT1.DEPT_NAME:LOC_ID;
  INSERT INTO TABLA1
  SELECT
   :DEPT_NAME.DEPT_NAME:DEPT_ID,
   :DEPT_NAME.DEPT_NAME:DEPT_NAME,
   :DEPT_NAME.DEPT_NAME:MGR_ID,
   :DEPT_NAME.DEPT_NAME:LOC_ID;
  !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
  EXECUTE IMMEDIATE 'SELECT * FROM
   FTABLE46'
            !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
            INTO DEPT_NAME;
 END;
$$;

$IF conditional_expression $THEN
     statement
     [ statement ]...
[ $ELSIF conditional_expression $THEN
     statement
     [ statement ]... ]...
[ $ELSE
     statement
     [ statement ]... ]
$END;

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 PROC1
IS
    sal_raise NUMBER;
BEGIN
  IF jobid = 'PU_CLERK' THEN sal_raise := .09;
  ELSIF jobid = 'SH_CLERK' THEN sal_raise := .08;
  ELSIF jobid = 'ST_CLERK' THEN sal_raise := .07;
  ELSE sal_raise := 0;
  END IF;
END;

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 AI Helpers Code section is omitted.

  let SAL_RAISE;
  if (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT jobid MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
    JOBID == `PU_CLERK`) {
    SAL_RAISE = 0.09;
  } else if (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT jobid MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
    JOBID == `SH_CLERK`) {
    SAL_RAISE = 0.08;
  } else if (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT jobid MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
    JOBID == `ST_CLERK`) {
    SAL_RAISE = 0.07;
  } else {
    SAL_RAISE = 0;
  }
$$;

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
  LOOP
    i := i + 1;
    j := 0;
    LOOP
      j := j + 1;
      s := s + i * j; -- Sum several products
    END LOOP inner_loop;
  END LOOP outer_loop;
END;

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 AI Helpers Code section is omitted.

  while ( true ) {
    I =
        !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT i MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
        I + 1;
    J = 0;
    while ( true ) {
      J =
          !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT j MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
          J + 1;
      S =
          !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT s MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
          S +
            !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT i MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
            I *
            !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT j MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
            J;
    }
  }
$$;

CREATE OR REPLACE PROCEDURE PROC1
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN
  WHILE I <> J LOOP
    I := I+1;
  END LOOP;
END;

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 AI Helpers Code section is omitted.

  let I = 1;
  let J = 10;
  while ( I != J ) {
    I = I + 1;
  }
$$;

CREATE OR REPLACE PROCEDURE PROC_VARIABLES
IS
  localVar1 NUMBER;
  localVar2 VARCHAR(100);
  localVar3 VARCHAR2 := 'local variable 3';
  localVar4 VARCHAR2 DEFAULT 'local variable 4';
  localVar5 VARCHAR2 NOT NULL := 'local variable 5';
  localVar6 VARCHAR2 NOT NULL DEFAULT 'local variable 6';
  localVar7 NUMBER := NULL;
  localVar8 NUMBER := '';
BEGIN
    localVar1 := 123;
END;

CREATE OR REPLACE PROCEDURE PROC_VARIABLES ()
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 LOCALVAR1;
  let LOCALVAR2;
  let LOCALVAR3 = `local variable 3`;
  let LOCALVAR4 = `local variable 4`;
  let LOCALVAR5 = `local variable 5`;
  let LOCALVAR6 = `local variable 6`;
  let LOCALVAR7 = undefined;
  let LOCALVAR8 = undefined;
  LOCALVAR1 = 123;
$$;

CREATE OR REPLACE PROCEDURE PROC_RECORDS
IS
    TYPE DEPTRECTYP IS RECORD (
    DEPT_ID    NUMBER(4) NOT NULL := 10,
    DEPT_NAME  VARCHAR2(30) NOT NULL := 'ADMINISTRATION',
    MGR_ID     NUMBER(6) := 200,
    LOC_ID     NUMBER(4) := 1700
  );

  TYPE NAME_REC IS RECORD (
    FIRST  EMPLOYEES.FIRST_NAME%TYPE,
    LAST   EMPLOYEES.LAST_NAME%TYPE
  );

  TYPE CONTACT IS RECORD (
    NAME  NAME_REC,-- NESTED RECORD
    PHONE EMPLOYEES.PHONE_NUMBER%TYPE
  );
BEGIN
    null;
END;

CREATE OR REPLACE PROCEDURE PROC_RECORDS ()
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.

  class DEPTRECTYP {
    DEPT_ID = 10
    DEPT_NAME = `ADMINISTRATION`
    MGR_ID = 200
    LOC_ID = 1700
    constructor() {
      [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
    }
  }
  class NAME_REC {
    FIRST
    LAST
    constructor() {
      [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
    }
  }
  class CONTACT {
    NAME = new NAME_REC()
    PHONE
    constructor() {
      [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
    }
  }
  null;
$$;

CREATE OR REPLACE PROCEDURE ROWTYPE_PROC AS
  varname number := 1;
  CURSOR BOOK_CURSOR IS SELECT * FROM BOOK where 1 = varname;

  BOOK_REC BOOK%ROWTYPE;
  BOOK_CUR_REC BOOK_CURSOR%ROWTYPE;
BEGIN
  BOOK_REC.ID     := 10;
  BOOK_REC.TITLE  := 'A STUDY IN SCARLET';
  BOOK_REC.AUTHOR := 'SIR ARTHUR CONAN DOYLE';

  INSERT INTO BOOK VALUES(BOOK_REC.ID, BOOK_REC.TITLE, BOOK_REC.AUTHOR);
  OPEN BOOK_CURSOR;
  FETCH BOOK_CURSOR INTO BOOK_CUR_REC;
  CLOSE BOOK_CURSOR;
END;

CREATE OR REPLACE PROCEDURE ROWTYPE_PROC ()
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 VARNAME = 1;
  let BOOK_CURSOR = new CURSOR(`SELECT * FROM
      BOOK
   where 1 = ?`,() => [VARNAME]);
  let BOOK_REC = ROWTYPE(`BOOK`);
  let BOOK_CUR_REC = BOOK_CURSOR.ROWTYPE();
  BOOK_REC.ID = 10;
  BOOK_REC.TITLE = `A STUDY IN SCARLET`;
  BOOK_REC.AUTHOR = `SIR ARTHUR CONAN DOYLE`;
  EXEC(`INSERT INTO BOOK
  VALUES(
  !!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE  VARIABLE BOOK_REC.ID MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
  ?,
  !!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE  VARIABLE BOOK_REC.TITLE MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
  ?,
  !!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE  VARIABLE BOOK_REC.AUTHOR MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
  ?)`,[BOOK_REC.ID,BOOK_REC.TITLE,BOOK_REC.AUTHOR]);
  BOOK_CURSOR.OPEN();
  BOOK_CURSOR.FETCH(BOOK_CUR_REC) && ([BOOK_CUR_REC] = BOOK_CURSOR.INTO());
  BOOK_CURSOR.CLOSE();
$$;

CREATE OR REPLACE PROCEDURE PROC_CONSTANTS
IS
    MY_VAR1 NUMBER;
    MY_CONST_VAR1 CONSTANT INTEGER(4) := 40;
    MY_CONST_VAR2 CONSTANT INTEGER(4) NOT NULL := MY_CONST_VAR1;
    MY_CONST_VAR3 CONSTANT VARCHAR(20) DEFAULT 'const variable';
    MY_CONST_VAR4 CONSTANT REAL NOT NULL DEFAULT 3.14159;
BEGIN
    MY_VAR1 := MY_CONST_VAR1 + MY_CONST_VAR2 + 1;
END;

CREATE OR REPLACE PROCEDURE PROC_CONSTANTS ()
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 MY_VAR1;
    const MY_CONST_VAR1 = 40;
    const MY_CONST_VAR2 = MY_CONST_VAR1;
    const MY_CONST_VAR3 = `const variable`;
    const MY_CONST_VAR4 = 3.14159;
    const MY_CONST_VAR1 = 40;
    const MY_CONST_VAR2 = MY_CONST_VAR1;
    MY_VAR1 = MY_CONST_VAR1 + MY_CONST_VAR2 + 1;
$$;

CREATE OR REPLACE PROCEDURE PROC_CURSORS
IS
    CURSOR C1 RETURN Table1%ROWTYPE;
    CURSOR C2 RETURN UserDefinedRecordType;
    CURSOR C3 RETURN Table1%ROWTYPE IS
        SELECT * FROM Table1 WHERE ID = 110;
    CURSOR C4 IS
        SELECT * FROM Table1 WHERE ID = 123;
    CURSOR C5 (cursorParam NUMBER ) RETURN Table1%ROWTYPE IS
        SELECT * FROM Table1 WHERE ID = cursorParam;
BEGIN
    null;
END;

CREATE OR REPLACE PROCEDURE PROC_CURSORS ()
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 C1 = new CURSOR();
    let C2 = new CURSOR();
    let C3 = new CURSOR(`SELECT * FROM
           Table1
        WHERE ID = 110`,() => []);
    let C4 = new CURSOR(`SELECT * FROM
           Table1
        WHERE ID = 123`,() => []);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    let C5 = new CURSOR(`SELECT * FROM
           Table1
        WHERE ID = ?`,(CURSORPARAM) => [CURSORPARAM]);
    null;
$$;

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 PROCEDURE_WITH_LIKE AS
BEGIN
	IF 'ABC' LIKE '%A%' THEN
		 null;
	END IF;
  IF 'ABC' LIKE 'A%' THEN
     null;
  END IF;
  IF 'ABC' NOT LIKE 'D_%' THEN
     null;
  END IF;
  IF 'ABC' NOT LIKE 'D/%%' ESCAPE '/' THEN
     null;
  END IF;
END;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_LIKE ()
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.

	if (LIKE(`ABC`,`%A%`)) {
		null;
	}
	if (LIKE(`ABC`,`A%`)) {
		null;
	}
	if (!LIKE(`ABC`,`D_%`)) {
		null;
	}
	if (!LIKE(`ABC`,`D/%%`,`/`)) {
		null;
	}
$$;

CREATE OR REPLACE PROCEDURE BETWEEN_TEST
IS
NUM1 INTEGER := 789;
US INTEGER := 1000;
BEGIN
    IF 800 BETWEEN US AND NUM1 THEN
        NULL;
    END IF;
    IF 'BA' BETWEEN 'B' AND 'CA' THEN
        NULL;
    END IF;

    -- Assign null to the variable num1
    NUM1 := NULL;

    IF (0 BETWEEN NULL AND NUM1) IS NULL THEN
        NULL;
    END IF;
END;

CREATE OR REPLACE PROCEDURE BETWEEN_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;
    let US = 1000;
    if (BetweenFunc(800,US,NUM1)) {
        null;
    }
    if (BetweenFunc(`BA`,`B`,`CA`)) {
        null;
    }

    // Assign null to the variable num1
    NUM1 = undefined;
    if (IS_NULL(BetweenFunc(0,undefined,NUM1))) {
        null;
    }
$$;

CREATE OR REPLACE PROCEDURE IN_PROC
IS
letter VARCHAR2(1) := 'm';
BEGIN
  IF letter IN ('a', 'b', 'c') THEN
    null;
  ELSE
    null;
  END IF;
END;

CREATE OR REPLACE PROCEDURE IN_PROC ()
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 LETTER = `m`;
  if ([`a`,`b`,`c`].includes(LETTER)) {
    null;
  } else {
    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;

CREATE OR REPLACE FUNCTION FUN1 (x FLOAT)
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 TRUNC(X) < 5
                    THEN 1
                ELSE 0
            END AS VAR1
    )
    SELECT
        VAR1
    FROM
        declaration_variables_cte1
$$;

CREATE OR REPLACE FUNCTION FUN1 (flag FLOAT)
RETURN NUMBER IS
BEGIN
  return CASE flag
	WHEN 1 THEN 'one'
	WHEN 2 THEN 'two'
	WHEN 3 THEN 'three'
	WHEN 4 THEN 'four'
	ELSE 'unknown' END;
END FUN1;

CREATE OR REPLACE FUNCTION FUN1 (flag FLOAT)
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
$$
	SELECT
		CASE flag
			WHEN 1 THEN 'one'
			WHEN 2 THEN 'two'
			WHEN 3 THEN 'three'
			WHEN 4 THEN 'four'
			ELSE 'unknown' END
$$;

CREATE OR REPLACE PACKAGE MY_PACKAGE AS
-- Other elements...
END MY_PACKAGE ;

CREATE IF NOT EXISTS SCHEMA MY_PACKAGE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
-- Other elements...

-- Other elements...

CREATE OR REPLACE PACKAGE MY_PACKAGE AS
  PROCEDURE MY_PROCEDURE(PARAM1 VARCHAR2);
  FUNCTION MY_FUNCTION(PARAM1 VARCHAR2) RETURN NUMBER ;
END MY_PACKAGE;

CREATE SCHEMA IF NOT EXISTS MY_PACKAGE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PACKAGE PACKAGE_VARIABLES AS
    VAR1 integer := 333;
    VAR2 INTEGER := VAR1 + 456;
	  VAR3 DATE := CURRENT_DATE;
	  VAR4 VARCHAR(20) := 'HELLO WORLD';
	  VAR5 INTEGER;
END;

CREATE SCHEMA IF NOT EXISTS PACKAGE_VARIABLES
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

SET "PACKAGE_VARIABLES.VAR1" = '' || (333);

SET "PACKAGE_VARIABLES.VAR2" = (SELECT
	'' || (GETVARIABLE('PACKAGE_VARIABLES.VAR1') :: INTEGER + 456));

SET "PACKAGE_VARIABLES.VAR3" = (SELECT
	'' || (CURRENT_DATE()));

SET "PACKAGE_VARIABLES.VAR4" = '' || ('HELLO WORLD');

SET "PACKAGE_VARIABLES.VAR5" = '~';

CREATE OR REPLACE PACKAGE PACKAGE_CONSTANTS
IS
const_name CONSTANT VARCHAR(10) := 'Snow';
PROCEDURE PROCEDURE1;
END PACKAGE_CONSTANTS;

CREATE OR REPLACE PACKAGE BODY PACKAGE_CONSTANTS
IS
PROCEDURE MY_PROCEDURE IS
   BEGIN
      INSERT INTO DBUSER ("USER_NAME")
      VALUES (const_name);
   END;

END PACKAGE_CONSTANTS;

CREATE SCHEMA IF NOT EXISTS PACKAGE_CONSTANTS
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE PACKAGE_CONSTANTS.MY_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      CONST_NAME VARCHAR(10) := 'Snow';
   BEGIN
      INSERT INTO DBUSER("USER_NAME")
      VALUES (:CONST_NAME);
   END;
$$;

CREATE OR REPLACE PACKAGE MY_PACKAGE_EX AS
    an_exception EXCEPTION;
END MY_PACKAGE_EX;

CREATE SCHEMA IF NOT EXISTS MY_PACKAGE_EX
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

!!!RESOLVE EWI!!! /*** SSC-EWI-OR0049 - PACKAGE EXCEPTIONS in stateful package MY_PACKAGE_EX are not supported yet ***/!!!
an_exception EXCEPTION;

CREATE OR REPLACE PACKAGE BODY PACKAGE_PROCEDURE
IS
PROCEDURE MY_PROCEDURE (MY_PARAM VARCHAR) IS
   BEGIN
      null;
   END;

END PACKAGE_PROCEDURE;

CREATE OR REPLACE PROCEDURE PACKAGE_PROCEDURE.MY_PROCEDURE (MY_PARAM STRING)
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.

   null;
$$;

CREATE OR REPLACE PROCEDURE PACKAGE_PROCEDURE_MY_PROCEDURE (MY_PARAM STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert AI Helpers Code
   null;
$$;

CREATE OR REPLACE PACKAGE BODY PACKAGE_FUNCTION
IS
FUNCTION MY_FUNCTION (MY_PARAM VARCHAR) RETURN NUMBER
AS
   BEGIN
      null;
   END;
END PACKAGE_FUNCTION;

CREATE OR REPLACE FUNCTION PACKAGE_FUNCTION.MY_FUNCTION (MY_PARAM STRING)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
   // SnowConvert AI Helpers Code section is omitted.
   null;
$$;

CREATE OR REPLACE FUNCTION PACKAGE_FUNCTION_MY_FUNCTION (MY_PARAM STRING)
RETURNS NUMBER
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
   // REGION SnowConvert AI Helpers Code
   null;
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE02(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
    SCHEMA1.PACKAGE1.PROCEDURE01(param1, param2);
END;

CALL SCHEMA1.PACKAGE1.PROCEDURE01(param1, param2);

CREATE OR REPLACE PROCEDURE PROCEDURE02 (param1 FLOAT, 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 AI Helpers Code section is omitted.

    EXEC(`CALL
SCHEMA1.PACKAGE1.PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;

CALL SCHEMA1.PACKAGE1.PROCEDURE01(param1, param2);

CREATE OR REPLACE PROCEDURE PUBLIC.PROCEDURE02 (param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert AI Helpers Code
   EXEC(`CALL SCHEMA1.PACKAGE1_PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;

CALL SCHEMA1.PACKAGE1_PROCEDURE01(param1, param2);

CREATE OR REPLACE PACKAGE BODY PACKAGE_VARIABLES AS
  PROCEDURE P1 AS
    BEGIN
			VAR1 := VAR1 + 888;
			INSERT INTO TABLE1 values (VAR1);
         INSERT INTO TABLE2 values (VAR4);
    END;
END;

CREATE OR REPLACE PROCEDURE PACKAGE_VARIABLES.P1 ()
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.

	VAR1 =
			!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT VAR1 MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
			VAR1 + 888;
	EXEC(`INSERT INTO TABLE1
			values (VAR1)`);
	EXEC(`INSERT INTO TABLE2
         values (VAR4)`);
$$;

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;

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 AI Helpers Code section is omitted.

    null;
$$;

CREATE OR REPLACE PROCEDURE PROC1
(
  param1 NUMBER
)
IS
  localVar1 NUMBER;
  countRows NUMBER;
  tempSql VARCHAR(100);
  tempResult NUMBER;
  CURSOR MyCursor
    IS
       SELECT COL1 FROM Table1;

BEGIN
    localVar1 := param1;
    countRows := 0;
    tempSql := 'SELECT COUNT(*) FROM Table1 WHERE COL1 =' || localVar1;

    FOR myCursorItem IN MyCursor
        LOOP
            localVar1 := myCursorItem.Col1;
            countRows := countRows + 1;
        END LOOP;
    INSERT INTO Table2 VALUES(countRows, 'ForCursor: Total Row count is: ' || countRows);
    countRows := 0;

    OPEN MyCursor;
    LOOP
        FETCH MyCursor INTO tempResult;
        EXIT WHEN MyCursor%NOTFOUND;
        countRows := countRows + 1;
    END LOOP;
    CLOSE MyCursor;
    INSERT INTO Table2 VALUES(countRows, 'LOOP: Total Row count is: ' || countRows);

    EXECUTE IMMEDIATE tempSql INTO tempResult;
    IF tempResult > 0 THEN
        INSERT INTO Table2 (COL1, COL2) VALUES(tempResult, 'Hi, found value:' || localVar1 || ' in Table1 -- There are ' || tempResult || ' rows');
        COMMIT;
    END IF;
END PROC1;

CREATE OR REPLACE PROCEDURE PROC1
(param1 FLOAT
)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // REGION SnowConvert AI Helpers Code
  var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
  var fixBind = function (arg) {
    arg = arg instanceof Date ? formatDate(arg) : IS_NULL(arg) ? null : arg;
    return arg;
  };
  var SQL = {
    FOUND : false,
    NOTFOUND : false,
    ROWCOUNT : 0,
    ISOPEN : false
  };
  var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
  var getObj = (_rs) => Object.assign(new Object(),_rs);
  var getRow = (_rs) => (values = Object.values(_rs)) && (values = values.splice(-1 * _rs.getColumnCount())) && values;
  var fetch = (_RS,_ROWS,fmode) => _RS.getRowCount() && _ROWS.next() && (fmode ? getObj : getRow)(_ROWS) || (fmode ? new Object() : []);
  var EXEC = function (stmt,binds,opts) {
    try {
      binds = !(arguments[1] instanceof Array) && ((opts = arguments[1]) && []) || (binds || []);
      opts = opts || new Object();
      binds = binds ? binds.map(fixBind) : binds;
      _RS = snowflake.createStatement({
          sqlText : stmt,
          binds : binds
        });
      _ROWS = _RS.execute();
      if (opts.sql !== 0) {
        var isSelect = stmt.toUpperCase().trimStart().startsWith("SELECT");
        var affectedRows = isSelect ? _RS.getRowCount() : _RS.getNumRowsAffected();
        SQL.FOUND = affectedRows != 0;
        SQL.NOTFOUND = affectedRows == 0;
        SQL.ROWCOUNT = affectedRows;
      }
      if (opts.row === 2) {
        return _ROWS;
      }
      var INTO = function (opts) {
        if (opts.vars == 1 && _RS.getColumnCount() == 1 && _ROWS.next()) {
          return _ROWS.getColumnValue(1);
        }
        if (opts.rec instanceof Object && _ROWS.next()) {
          var recordKeys = Object.keys(opts.rec);
          Object.assign(opts.rec,Object.fromEntries(new Map(getRow(_ROWS).map((element,Index) => [recordKeys[Index],element]))))
          return opts.rec;
        }
        return fetch(_RS,_ROWS,opts.row);
      };
      var BULK_INTO_COLLECTION = function (into) {
        for(let i = 0;i < _RS.getRowCount();i++) {
          FETCH_INTO_COLLECTIONS(into,fetch(_RS,_ROWS,opts.row));
        }
        return into;
      };
      if (_ROWS.getRowCount() > 0) {
        return _ROWS.getRowCount() == 1 ? INTO(opts) : BULK_INTO_COLLECTION(opts);
      }
    } catch(error) {
      RAISE(error.code,error.name,error.message)
    }
  };
  var RAISE = function (code,name,message) {
    message === undefined && ([name,message] = [message,name])
    var error = new Error(message);
    error.name = name
    SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
    throw error;
  };
  var FETCH_INTO_COLLECTIONS = function (collections,fetchValues) {
    for(let i = 0;i < collections.length;i++) {
      collections[i].push(fetchValues[i]);
    }
  };
  var IS_NULL = (arg) => !(arg || arg === 0);
  var CURSOR = function (stmt,binds,isRefCursor,isOut) {
    var statementObj, result_set, total_rows, ISOPEN = false, result_set_table = '', self = this, row_count, found;
    this.CURRENT = new Object;
    this.INTO = function () {
        return self.res;
      };
    this.OPEN = function (openParameters) {
        if (ISOPEN && !isRefCursor) RAISE(-6511,"CURSOR_ALREADY_OPEN","cursor already open");
        var finalStmt = openParameters && openParameters.query || stmt;
        var parameters = openParameters && openParameters.binds || [];
        var finalBinds = binds instanceof Function ? binds(...parameters) : binds;
        finalBinds = finalBinds || parameters;
        try {
          if (isOut) {
            if (!temptable_prefix) {
              temptable_prefix = `${procname}_TEMP_${(EXEC(`select current_session() || '_' || to_varchar(current_timestamp, 'yyyymmddhh24missss')`,{
                  sql : 0
                }))[0]}_`;
            }
            if (!result_set_table) {
              result_set_table = temptable_prefix + outCursorResultNumber++;
              EXEC(`CREATE OR REPLACE TEMPORARY TABLE ${result_set_table} AS ${finalStmt}`,{
                sql : 0
              });
            }
            finalStmt = "SELECT * FROM " + result_set_table
          }
          [result_set,statementObj,total_rows] = [EXEC(finalStmt,finalBinds,{
              sql : 0,
              row : 2
            }),_RS,_RS.getColumnCount()]
          ISOPEN = true;
          row_count = 0;
        } catch(error) {
          RAISE(error.code,"error",error.message);
        }
        return this;
      };
    this.NEXT = function () {
        if (total_rows && result_set.next()) {
          this.CURRENT = new Object;
          for(let i = 1;i <= statementObj.getColumnCount();i++) {
            (this.CURRENT)[statementObj.getColumnName(i)] = result_set.getColumnValue(i);
          }
          return true;
        } else return false;
      };
    this.FETCH = function (record) {
        var recordKeys = record ? Object.keys(record) : undefined;
        self.res = [];
        if (!ISOPEN) RAISE(-1001,"INVALID_CURSOR","invalid cursor");
        if (recordKeys && recordKeys.length != statementObj.getColumnCount()) RAISE(-6504,"ROWTYPE_MISMATCH","Return types of Result Set variables or query do not match");
        self.res = fetch(statementObj,result_set);
        if (self.res && self.res.length > 0) {
          found = true;
          row_count++;
          if (recordKeys) {
            for(let i = 0;i < self.res.length;i++) {
              record[recordKeys[i]] = (self.res)[i];
            }
            return false;
          }
          return true;
        } else found = false;
        return false;
      };
    this.CLOSE = function () {
        if (!ISOPEN) RAISE(-1001,"INVALID_CURSOR","invalid cursor");
        found = row_count = result_set_table = total_rows = result_set = statementObj = undefined;
        ISOPEN = false;
      };
    this.FETCH_BULK_COLLECT_INTO = function (variables,limit) {
        if (variables.length != statementObj.getColumnCount()) RAISE(-6504,"ROWTYPE_MISMATCH","Return types of Result Set variables or query do not match");
        if (limit) {
          for(let i = 0;i < limit && this.FETCH();i++)FETCH_INTO_COLLECTIONS(variables,self.res);
        } else {
          while ( this.FETCH() )
            FETCH_INTO_COLLECTIONS(variables,self.res);
        }
      };
    this.FOUND = () => ISOPEN ? typeof(found) == "boolean" ? found : null : RAISE(-1001,"INVALID_CURSOR","invalid cursor");
    this.NOTFOUND = () => ISOPEN ? typeof(found) == "boolean" ? !found : null : RAISE(-1001,"INVALID_CURSOR","invalid cursor");
    this.ROWCOUNT = () => ISOPEN ? row_count : RAISE(-1001,"INVALID_CURSOR","invalid cursor");
    this.ISOPEN = () => ISOPEN;
    this.SAVE_STATE = function () {
        return {
          tempTable : result_set_table,
          position : row_count
        };
      };
    this.RESTORE_STATE = function (tempTable,position) {
        result_set_table = tempTable
        if (result_set_table) {
          isOut = true
          this.OPEN();
          for(let i = 0;i < position;i++)this.FETCH();
        }
      };
    this.ROWTYPE = () => ROWTYPE(stmt,binds());
  };
  var outCursorResultNumber = 0;
  var concatValue = (arg) => IS_NULL(arg) ? "" : arg;
  // END REGION

  let LOCALVAR1;
  let COUNTROWS;
  let TEMPSQL;
  let TEMPRESULT;
  let MYCURSOR = new CURSOR(`SELECT COL1 FROM
          Table1`,() => []);
  LOCALVAR1 = PARAM1;
  COUNTROWS = 0;
  TEMPSQL = `SELECT COUNT(*) FROM
   Table1
WHERE COL1 =${concatValue(LOCALVAR1)}`;
  MYCURSOR.OPEN();
  while ( MYCURSOR.NEXT() ) {
    let MYCURSORITEM = MYCURSOR.CURRENT;
    LOCALVAR1 = MYCURSORITEM.COL1;
    COUNTROWS = COUNTROWS + 1;
  }
  MYCURSOR.CLOSE();
  EXEC(`INSERT INTO Table2
    VALUES(?, 'ForCursor: Total Row count is: ' || NVL(? :: STRING, ''))`,[COUNTROWS,COUNTROWS]);
  COUNTROWS = 0;
  MYCURSOR.OPEN();
  while ( true ) {
    MYCURSOR.FETCH(TEMPRESULT) && ([TEMPRESULT] = MYCURSOR.INTO());
    if (MYCURSOR.NOTFOUND()) {
      break;
    }
    COUNTROWS = COUNTROWS + 1;
  }
  MYCURSOR.CLOSE();
  EXEC(`INSERT INTO Table2
    VALUES(?, 'LOOP: Total Row count is: ' || NVL(? :: STRING, ''))`,[COUNTROWS,COUNTROWS]);
  [TEMPRESULT] = EXEC(TEMPSQL);
  if (TEMPRESULT > 0) {
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`INSERT INTO Table2(COL1, COL2) VALUES(?, 'Hi, found value:' || NVL(? :: STRING, '') || ' in Table1 -- There are ' || NVL(? :: STRING, '') || ' rows')`,[TEMPRESULT,LOCALVAR1,TEMPRESULT]);
    EXEC(`--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT;`);
  }
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE01(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
INSERT INTO TABLE1 VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE02(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
PROCEDURE01(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE01 (param1 FLOAT, 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 AI Helpers Code section is omitted.

	EXEC(`INSERT INTO TABLE1
	VALUES(?, ?)`,[PARAM1,PARAM2]);
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE02 (param1 FLOAT, 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 AI Helpers Code section is omitted.

	EXEC(`CALL
	PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;

CREATE OR REPLACE PROCEDURE PROC1
IS
    MyVariable1 NUMBER;
    MyOtherVariable2 NUMBER := 1;
    CURSOR C1 IS
        SELECT * FROM Table1 WHERE ID = 123;
    CURSOR C2 (paramCursor1 NUMBER) IS
        SELECT COL1 AS C_1 FROM TABLE1 WHERE ID = paramCursor1;
BEGIN
    FOR myCursorRecord IN C1
        LOOP
            MyVariable1 := myCursorRecord.Col1;
        END LOOP;

    FOR myCursorRecord IN (SELECT * FROM Table1 WHERE ID = MyVariable1)
        LOOP
            MyVariable1 := myCursorRecord.Col1;
        END LOOP;

    <<Block1>>
    FOR myCursorRecord IN C2 (MyOtherVariable2)
        LOOP
            MyVariable1 := myCursorRecord.Col1;
        END LOOP Block1;
END;

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 AI Helpers Code section is omitted.

    let MYVARIABLE1;
    let MYOTHERVARIABLE2 = 1;
    let C1 = new CURSOR(`SELECT * FROM
           Table1
        WHERE ID = 123`,() => []);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    let C2 = new CURSOR(`SELECT COL1 AS C_1 FROM
           TABLE1
        WHERE ID = ?`,(PARAMCURSOR1) => [PARAMCURSOR1]);
    C1.OPEN();
    while ( C1.NEXT() ) {
        let MYCURSORRECORD = C1.CURRENT;
        MYVARIABLE1 = MYCURSORRECORD.COL1;
    }
    C1.CLOSE();
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    for(var MYCURSORRECORD_CURSOR = new CURSOR(`(SELECT * FROM
      Table1
   WHERE ID = ?
)`,[MYVARIABLE1]).OPEN();MYCURSORRECORD_CURSOR.NEXT();) {
        let MYCURSORRECORD = MYCURSORRECORD_CURSOR.CURRENT;
        MYVARIABLE1 = MYCURSORRECORD.COL1;
    }
    MYCURSORRECORD_CURSOR.CLOSE();
    C2.OPEN({
        binds : [MYOTHERVARIABLE2]
    });
    while ( C2.NEXT() ) {
        let BLOCK1 = C2.CURRENT;
        MYVARIABLE1 = MYCURSORRECORD.COL1;
    }
    C2.CLOSE();
$$;

CREATE OR REPLACE PROCEDURE PROC2
IS
    col1Value   table1.COL1%TYPE;
    col2Value   table1.COL2%TYPE;
    entireRow   table1%ROWTYPE;
    TYPE MyRowType IS RECORD ( COLUMN1 NUMBER, COLUMN2 NUMBER);
    entireRow_1 MyRowType;
    CURSOR C1 IS  SELECT * FROM table1;
    C2 SYS_REFCURSOR;
    TYPE COLLECTION_TYPE IS TABLE OF TABLE1.COL1%TYPE;
    MY_COLLECTION MY_COLLECTION_TYPE := MY_COLLECTION_TYPE();
    SOME_SELECT VARCHAR(200);
BEGIN
    OPEN C1;
    FETCH C1 INTO col1Value, col2Value;
    CLOSE C1;

    OPEN C1;
    FETCH C1 INTO entireRow;
    CLOSE C1;

    OPEN C1;
    FETCH C1 INTO entireRow_1;
    CLOSE C1;

    OPEN C2 FOR 'SELECT COL1 FROM TABLE1 WHERE COL1 <> :v' USING 123;
    FETCH C2 BULK COLLECT INTO MY_COLLECTION LIMIT 2;
    CLOSE C2;

    OPEN C2 FOR SELECT * FROM TABLE1 WHERE COL1 = NUM1;
    CLOSE C2;
END;

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

    let COL1VALUE;
    let COL2VALUE;
    let ENTIREROW = ROWTYPE(`table1`);
    class MYROWTYPE {
        COLUMN1
        COLUMN2
        constructor() {
            [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
        }
    }
    let ENTIREROW_1 = new MYROWTYPE();
    let C1 = new CURSOR(`SELECT * FROM
   table1`,() => []);
    let C2 = new CURSOR(undefined,undefined,true);
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ***/!!!
    /*     TYPE COLLECTION_TYPE IS TABLE OF TABLE1.COL1%TYPE */
    ;
    let MY_COLLECTION = new MY_COLLECTION_TYPE();
    let SOME_SELECT;
    C1.OPEN();
    C1.FETCH(COL1VALUE,COL2VALUE) && ([COL1VALUE,COL2VALUE] = C1.INTO());
    C1.CLOSE();
    C1.OPEN();
    C1.FETCH(ENTIREROW) && ([ENTIREROW] = C1.INTO());
    C1.CLOSE();
    C1.OPEN();
    C1.FETCH(ENTIREROW_1) && ([ENTIREROW_1] = C1.INTO());
    C1.CLOSE();
    C2.OPEN({
        query : `SELECT COL1 FROM
   TABLE1
WHERE COL1 <> ?`,
        binds : [123]
    });
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
    /*     FETCH C2 BULK COLLECT INTO MY_COLLECTION LIMIT 2 */
    ;
    C2.CLOSE();
    C2.OPEN({
        query : `SELECT * FROM
   TABLE1
WHERE COL1 = NUM1`
    });
    C2.CLOSE();
$$;

CREATE OR REPLACE PROCEDURE SP_IMPLICIT_CURSOR_SAMPLE AUTHID DEFINER IS
  VAR_AUX  NUMBER(3);
  STMT_STAT1  NUMBER(3):= 0;
  STMT_STAT2  NUMBER(3):= 0;
  STMT_STAT3  NUMBER(3):= 0;
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE FTABLE35(COL1 NUMBER(3))';
  IF SQL%FOUND THEN
    STMT_STAT1 := 1;
  END IF;
  IF SQL%NOTFOUND THEN
   STMT_STAT2 := 1;
  END IF;
  IF SQL%ISOPEN THEN
   STMT_STAT3 := 1;
  END IF;
  EXECUTE IMMEDIATE 'INSERT INTO FTABLE33 VALUES(:D1,:D2,:D3,:D4)' USING SQL%ROWCOUNT, STMT_STAT1, STMT_STAT2, STMT_STAT3;
END;

CREATE OR REPLACE PROCEDURE SP_IMPLICIT_CURSOR_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "12/16/2024",  "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
  !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!
  //AUTHID DEFINER
  null
  // SnowConvert AI Helpers Code section is omitted.

  let VAR_AUX;
  let STMT_STAT1 = 0;
  let STMT_STAT2 = 0;
  let STMT_STAT3 = 0;
  EXEC(`CREATE OR REPLACE TABLE FTABLE35 (COL1 NUMBER(3)
)`);
  if (SQL.FOUND) {
    STMT_STAT1 = 1;
  }
  if (SQL.NOTFOUND) {
    STMT_STAT2 = 1;
  }
  if (SQL.ISOPEN) {
    STMT_STAT3 = 1;
  }
  EXEC(`INSERT INTO FTABLE33
VALUES(?, ?, ?, ?)`,[SQL.ROWCOUNT /*** SSC-FDM-OR0009 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/,STMT_STAT1,STMT_STAT2,STMT_STAT3]);
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE1
IS
  i NUMBER := 0;
  j NUMBER := 0;
  k NUMBER := 0;
BEGIN
  <<loop_a>>
  LOOP
    i := i + 1;

    <<loop_b>>
    LOOP
      j := j + 1;

      <<loop_c>>
      LOOP
        k := k + j + i;
        EXIT;
      END LOOP loop_c;

      EXIT loop_b WHEN (j > 3);
    END LOOP loop_b;

    EXIT loop_a WHEN (i > 3);
  END LOOP loop_a;

END;

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

  let I = 0;
  let J = 0;
  let K = 0;
  while ( true ) {
    I = I + 1;
    while ( true ) {
      J = J + 1;
      while ( true ) {
        K = K + J + I;
        break;
      }
      !!!RESOLVE EWI!!! /*** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ***/!!!
      /*
            EXIT loop_b WHEN (j > 3) */
      ;
    }
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ***/!!!
    /*
        EXIT loop_a WHEN (i > 3) */
    ;
  }
$$;

CREATE OR REPLACE PROCEDURE sp_sample5 AS
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   dept_id     NUMBER(2) := 20;
   dept_id2     NUMBER(2) := 12;
   dept_id_upd VARCHAR(14);
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   dept_rec     deptt%ROWTYPE;
   TYPE NumList IS TABLE OF NUMBER;
   sals   NumList;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE dept (id NUMBER, name varchar(14), location varchar2(13))';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM dept WHERE id = :idd';
   EXECUTE IMMEDIATE sql_stmt INTO dept_rec USING dept_id;
   sql_stmt := 'UPDATE dept SET id = 200 WHERE id = :1 RETURNING name INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd;
   sql_stmt := 'delete from dept where id = :1 RETURNING name INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd;
   EXECUTE IMMEDIATE 'INSERT INTO dept VALUES (12, ''NAME1'', ''TEXAS'')';
   EXECUTE IMMEDIATE 'INSERT INTO DEPT VALUES(13, ''' || dept_name || ''', ''LA'')';
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE id = :num' USING dept_id2;
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MM-YYYY''';
   EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals;
END;

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

   let SQL_STMT;
   let PLSQL_BLOCK;
   let EMP_ID = 7566;
   let DEPT_ID = 20;
   let DEPT_ID2 = 12;
   let DEPT_ID_UPD;
   let DEPT_NAME = `PERSONNEL`;
   let LOCATION = `DALLAS`;
   let DEPT_REC = ROWTYPE(`deptt`);
   !!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ***/!!!
   /*    TYPE NumList IS TABLE OF NUMBER */
   ;
   !!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
   /*    sals   NumList */
   ;
   EXEC(`CREATE OR REPLACE TABLE dept (id NUMBER(38, 18),
   name varchar(14),
   location VARCHAR(13))`);
   SQL_STMT = `INSERT INTO dept
VALUES (?, ?, ?)`;
   EXEC(SQL_STMT,[DEPT_ID,DEPT_NAME,LOCATION]);
   SQL_STMT = `SELECT * FROM
   dept
WHERE id = ?`;
   EXEC(SQL_STMT,[DEPT_ID],{
      rec : dept_rec
   });
   SQL_STMT = `UPDATE dept
   SET id = 200 WHERE id = ?
   RETURNING name INTO :2`;
   !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'THIS EXECUTE IMMEDIATE CASE' NODE ***/!!!
   /*    EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd */
   ;
   SQL_STMT = `delete FROM
   dept
where id = ?
RETURNING name INTO :2`;
   !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'THIS EXECUTE IMMEDIATE CASE' NODE ***/!!!
   /*    EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd */
   ;
   EXEC(`INSERT INTO dept
VALUES (12, 'NAME1', 'TEXAS')`);
   EXEC(`INSERT INTO DEPT
VALUES(13, '${concatValue(DEPT_NAME)}', 'LA')`);
   EXEC(`DELETE FROM
   dept
WHERE id = ?`,[DEPT_ID2]);
   EXEC(`ALTER SESSION SET DATE_INPUT_FORMAT = 'DD-MM-YYYY' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY'`);
   !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'THIS EXECUTE IMMEDIATE CASE' NODE ***/!!!
   /*    EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals */
   ;
$$;