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`) /*** SSC-EWI-OR0077 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR table1 ROWTYPE ATTRIBUTE. TABLE ROWTYPE IS ASSUMED. ***/;
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; // COLLECTIONSCurrently 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
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-OR0077 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR deptt ROWTYPE ATTRIBUTE. TABLE ROWTYPE IS ASSUMED. ***/;
/* ** 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)'
;Related EWIs
SSC-OOS:
SSC-FDM-OR0009: SQL IMPLICIT CURSOR VALUES MAY DIFFER.
Last updated