SQL Language Elements

Transformation for syntax and semantics of PL/SQL language elements.

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

Cursor FOR LOOP

You might also be interested in Cursor helper and Cursor declaration.

Oracle

IN -> Oracle_01.sql

--Additional Params: -t JavaScript

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;

Snowflake

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

OUT -> Oracle_01.sql

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 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 MYCURSORRECORD = C2.CURRENT;
        MYVARIABLE1 = MYCURSORRECORD.COL1;
    }
    C2.CLOSE();
$$;

OPEN, FETCH and CLOSE Statement

You might also be interested in Cursor helper and Cursor declaration.

Oracle

IN -> Oracle_02.sql

--Additional Params: -t JavaScript

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;

Snowflake

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

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE PROC2 ()
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 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);
    /* ** 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]
    });
    /* ** 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();
$$;

Transformation for the following lines correspond to custom types which are work in progress:

entireRow   table1%ROWTYPE; // ROW TYPES
TYPE COLLECTION_TYPE IS TABLE OF TABLE1.COL1%TYPE; // COLLECTIONS

Currently the next statement is being emitted but the class is not being created yet. A warning will be applied in the future to all the uses of the unsupported custom types.

let MY_COLLECTION = new MY_COLLECTION_TYPE();

SQL Implicit Cursor

Oracle

IN -> Oracle_03.sql

--Additional Params: -t JavaScript

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;

Snowflake

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

OUT -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE SP_IMPLICIT_CURSOR_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  //AUTHID DEFINER
  !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!
  null
  // SnowConvert 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]);
$$;

EXIT

You might also be interested in Loop and while statements.

Transformation for labels is a work in progress.

Oracle

IN -> Oracle_04.sql

--Additional Params: -t JavaScript
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;

Snowflake

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

OUT -> Oracle_04.sql

CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
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 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;
      }
      /* ** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ** */
      /* 
            EXIT loop_b WHEN (j > 3) */
      ;
    }
    /* ** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ** */
    /* 
        EXIT loop_a WHEN (i > 3) */
    ;
  }
$$;

Execute Immediate

You might also be interested in EXEC helper

Oracle

IN -> Oracle_05.sql

--Additional Params: -t JavaScript

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;

Snowflake

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

OUT -> Oracle_05.sql

CREATE OR REPLACE PROCEDURE sp_sample5 ()
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 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`);
   /* ** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ** */
   /*    TYPE NumList IS TABLE OF NUMBER */
   ;
   /* ** 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`;
   /* ** 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`;
   /* ** 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'`);
   /* ** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'THIS EXECUTE IMMEDIATE CASE' NODE ** */
   /*    EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals */
   ;
$$;

Since the "RETURNING INTO" clause requires special analysis of the statement executed, its translation is planned to be delivered in the future.

Transformation for the following line correspond to collection types which is work in progress:

TYPE NumList IS TABLE OF NUMBER;

Currently the next statement is being emitted but the class is not being created yet. A warning will be applied in the future to all the uses of the unsupported custom types.

let SALS = new NUMLIST();

Also the following EXECUTE IMMEDIATE related with the BULK COLLECT into the sals variable, is also work in progress.

EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals;

Errors and Exception Handling

You might also be interested in Raise helper

Raise Helper Usage

Oracle

IN -> Oracle_06.sql

--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE HANDLERS_WITH_OTHERS_COMMENTS AUTHID DEFINER IS
  deadlock_detected EXCEPTION;
  deadlock_dex EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
  PRAGMA EXCEPTION_INIT(deadlock_dex, -63);
BEGIN

  IF true THEN
    RAISE NO_DATA_FOUND;
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, SQLERRM, PARM);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, SQLERRM, TRUE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, SQLERRM, FALSE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, 'CUSTOM ERROR MESSAGE', TRUE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20010, 'SECOND CUSTOM ERROR MESSAGE', TRUE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20010, 'OTHER CUSTOM ERROR MESSAGE', FALSE);
  END IF;

EXCEPTION
    WHEN EXC_NAME THEN
        --Handle Exc_name  found exception
        null;
    WHEN NO_DATA_FOUND THEN
        --Handle No data found exception
        null;
    WHEN OTHERS THEN
        --Handler for others exception
        null;
END;

Snowflake

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

OUT -> Oracle_06.sql

CREATE OR REPLACE PROCEDURE HANDLERS_WITH_OTHERS_COMMENTS ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  //AUTHID DEFINER
  !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!
  null
  
  // SnowConvert Helpers Code section is omitted.
  
  /* ** SSC-EWI-OR0052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */
  /*   deadlock_detected EXCEPTION */
  ;
  /* ** SSC-EWI-OR0052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */
  /*   deadlock_dex EXCEPTION */
  ;
  /* ** SSC-EWI-OR0072 - PROCEDURAL MEMBER PRAGMA DECLARATION NOT SUPPORTED. ** */
  /*   PRAGMA EXCEPTION_INIT(deadlock_detected, -60) */
  ;
  /* ** SSC-EWI-OR0072 - PROCEDURAL MEMBER PRAGMA DECLARATION NOT SUPPORTED. ** */
  /*   PRAGMA EXCEPTION_INIT(deadlock_dex, -63) */
  ;
  try {
    if (true) {
      RAISE(100,`NO_DATA_FOUND`,`Single row SELECT returned no rows or your program referenced a deleted element in a nested table or an uninitialized element in an associative array (index-by table).`);
    }
    if (true) {
      RAISE(-20010,SQLERRM);
    }
    if (true) {
      // ** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT PARM WAS REMOVED. **
      RAISE(-20000,SQLERRM);
    }
    if (true) {
      // ** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
      RAISE(-20000,SQLERRM);
    }
    if (true) {
      RAISE(-20000,SQLERRM);
    }
    if (true) {
      // ** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
      RAISE(-20000,`CUSTOM ERROR MESSAGE`);
    }
    if (true) {
      // ** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
      RAISE(-20010,`SECOND CUSTOM ERROR MESSAGE`);
    }
    if (true) {
      RAISE(-20010,`OTHER CUSTOM ERROR MESSAGE`);
    }
  } catch(error) {
    switch(error.name) {
      case `EXC_NAME`: {
        //Handle Exc_name  found exception
        null;
        break;
      }
      case `NO_DATA_FOUND`: {
        //Handle No data found exception
        null;
        break;
      }
      default: {
        //Handler for others exception
        null;
        break;
      }
    }
  }
$$;

When there is not OTHERS handler, SnowConvert uses the "default" case in the switch that throws the original Error Object.

Commit

You might also be interested in EXEC helper

Oracle

IN -> Oracle_07.sql

--Additional Params: -t JavaScript

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

Snowflake

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

OUT -> Oracle_07.sql

CREATE OR REPLACE PROCEDURE PROC1 (param1 FLOAT, param2 FLOAT)
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.
    
    EXEC(`INSERT INTO TABLE1
    VALUES(?, ?)`,[PARAM1,PARAM2]);
    EXEC(`--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT;`);
$$;

CASE

Oracle

IN -> Oracle_08.sql

--Additional Params: -t JavaScript

CREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE2 ()
IS
  localVar1 NUMBER;
  localVar2 VARCHAR(100);
BEGIN
CASE (localVar1)
WHEN 1 THEN
    localVar2 := 'one';
WHEN 2 THEN 
    localVar := 'two'; 
WHEN 3 THEN 
    lovalVar := 'three';
ELSE
    localVar := 'error';
END CASE;

CASE
WHEN localVar = 1 THEN
    localVar2 := 'one';
WHEN localVar = 2 THEN 
    localVar := 'two'; 
WHEN localVar = 3 THEN 
    lovalVar := 'three';
ELSE
    localVar := 'error';
END CASE;
END;

Snowflake

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

OUT -> Oracle_08.sql

--** SSC-FDM-OR0007 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. **
CREATE OR REPLACE PROCEDURE PROCEDURE2 ()
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;
  switch(LOCALVAR1) {
    case 1:LOCALVAR2 = `one`;
    break;
    case 2:LOCALVAR = `two`;
    break;
    case 3:LOVALVAR = `three`;
    break;
    default:LOCALVAR = `error`;
    break;
  }
  if (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT localVar MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
    LOCALVAR == 1) {
    LOCALVAR2 = `one`;
  } else if (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT localVar MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
    LOCALVAR == 2) {
    LOCALVAR = `two`;
  } else if (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT localVar MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
    LOCALVAR == 3) {
    LOVALVAR = `three`;
  } else {
    LOCALVAR = `error`;
  }
$$;

CASE in a variable assignment

Oracle

IN -> Oracle_09.sql

--Additional Params: -t JavaScript

CREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE2 ()
IS
  localVar1 NUMBER;
BEGIN
	var1 := CASE flag
	WHEN 1 THEN 'one'
	WHEN 2 THEN 'two'
	WHEN 3 THEN 'three'
	WHEN 4 THEN 'four'
	ELSE 'unknown' END;

END;

Snowflake

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

OUT -> Oracle_09.sql

--** SSC-FDM-OR0007 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. **
CREATE OR REPLACE PROCEDURE PROCEDURE2 ()
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;
	VAR1 =
					!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT flag MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
					FLAG == 1 && `one` || (
						!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT flag MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
						FLAG == 2 && `two` || (
							!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT flag MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
							FLAG == 3 && `three` || (
								!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT flag MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
								FLAG == 4 && `four` || `unknown`)));
$$;

Call to external C or Java programs

Oracle

IN -> Oracle_10.sql

CREATE OR REPLACE EDITIONABLE PROCEDURE "OWB_REP_OWNER"."WB_RT_DP_CREATE_FKPARTITION" (prfID IN NUMBER,datatype IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'oracle.wh.service.impl.dataProfile.analysis.storedprocs.ForeignKey.createFKPartition(int,java.lang.String)';

Snowflake

OUT -> Oracle_10.sql

----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE PROCEDURE IS OUT OF TRANSLATION SCOPE. **
--CREATE OR REPLACE EDITIONABLE PROCEDURE "OWB_REP_OWNER"."WB_RT_DP_CREATE_FKPARTITION" (prfID IN NUMBER,datatype IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'oracle.wh.service.impl.dataProfile.analysis.storedprocs.ForeignKey.createFKPartition(int,java.lang.String)'
                                                                                                                                                                                                                                                                                   ;

Known Issues

No issues were found.

SSC-EWI-0022: One or more identifiers in a specific statement are considered parameters by default.
SSC-EWI-0053: Object may not work.
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-EWI-OR0052: Exception declaration is handled by the raise function.
SSC-EWI-OR0072: Procedural Member not supported.
SSC-EWI-OR0075: Current of clause is not supported in Snowflake.
SSC-EWI-OR0104: Unusable collection variable.
SSC-FDM-OR0007: Snowflake does not support the versioning of objects. Developers should consider alternate approaches for code versioning.
SSC-FDM-OR0009: SQL IMPLICIT CURSOR VALUES MAY DIFFER.
SSC-FDM-OR0011: The Boolean argument was removed because the "add to stack" options is not supported.
SSC-FDM-OR0012: COMMIT and ROLLBACK statements require adequate setup to perform as intended.

PreviousDDL - DML Statements NextExpressions and operators

Last updated 6 months ago

Cursor FOR LOOP

OPEN, FETCH and CLOSE Statement

SQL Implicit Cursor

EXIT

Execute Immediate

Errors and Exception Handling

Raise Helper Usage

Commit

CASE

CASE in a variable assignment

Call to external C or Java programs

Known Issues

Related EWIs