Declarations

This section holds the transformation of declarations that can be made inside procedures or functions.

Variable declaration and assignment

Oracle

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;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_VARIABLES ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   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;
$$;

Record variable declaration

You might also be interested in Records transformation section.

Oracle

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;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_RECORDS ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   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;
$$;

Rowtype Record variable declaration

Oracle

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;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.ROWTYPE_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code 
   let VARNAME = 1;
   let BOOK_CURSOR = new CURSOR(`SELECT * FROM PUBLIC.BOOK where 1 = ?`,() => [VARNAME]);
   let BOOK_REC = ROWTYPE(`PUBLIC.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 PUBLIC.BOOK VALUES(/*** MSC-WARNING - MSCEWI1026 - THE  VARIABLE BOOK_REC.ID MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/
?, /*** MSC-WARNING - MSCEWI1026 - THE  VARIABLE BOOK_REC.TITLE MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/
 ?, /*** MSC-WARNING - MSCEWI1026 - 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();
$$;

Constant Declaration

Oracle

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;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_CONSTANTS ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   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.141590118408203;
   MY_VAR1 = MY_CONST_VAR1 + MY_CONST_VAR2 + 1;
$$;

Cursor declarations and definition

Oracle

You might also be interested in Cursor helper

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;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_CURSORS ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code 
   let C1 = new CURSOR();
   let C2 = new CURSOR();
   let C3 = new CURSOR(`SELECT * FROM PUBLIC.Table1 WHERE ID = 110`,() => []);
   let C4 = new CURSOR(`SELECT * FROM PUBLIC.Table1 WHERE ID = 123`,() => []);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   let C5 = new CURSOR(`SELECT * FROM PUBLIC.Table1 WHERE ID = ?`,(CURSORPARAM) => [CURSORPARAM]);
   null;
$$;