Declarations

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

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

Variable declaration and assignment

Oracle--Additional Params: -t JavaScript

IN -> Oracle_01.sql

--Additional Params: -t JavaScript

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

OUT -> Oracle_01.sql

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

Record variable declaration

You might also be interested in Records transformation section.

Oracle

IN -> Oracle_02.sql

--Additional Params: -t JavaScript

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

OUT -> Oracle_02.sql

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

Rowtype Record variable declaration

Oracle

IN -> Oracle_03.sql

--Additional Params: -t JavaScript

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

OUT -> Oracle_03.sql

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

Constant Declaration

Oracle

IN -> Oracle_04.sql

--Additional Params: -t JavaScript

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

OUT -> Oracle_04.sql

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 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;
    MY_VAR1 = MY_CONST_VAR1 + MY_CONST_VAR2 + 1;
$$;

Cursor declarations and definition

Oracle

You might also be interested in Cursor helper

IN -> Oracle_05.sql

--Additional Params: -t JavaScript

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.

OUT -> Oracle_05.sql

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

EWIs Related

1. SSC-EWI-0022: One or more identifiers in this statement were considered parameters by default.

2. SSC-EWI-0026: The variable may requiere a cast to date, time or timestamp.