Declarations

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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;
    const MY_CONST_VAR1 = 40;
    const MY_CONST_VAR2 = MY_CONST_VAR1;
    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;
$$;

Known Issues

No issues were found.

Related EWIs

No related EWIs.

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.