--Additional Params: -t JavaScriptCREATE OR REPLACE PROCEDURE PROC1IS 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$// 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();$$;
--Additional Params: -t JavaScriptCREATE OR REPLACE PROCEDURE PROC2IS 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'EXECUTE AS CALLERAS$$// 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); !!!RESOLVE EWI!!! /*** 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 TABLE1WHERE COL1 <> ?`, binds : [123] }); !!!RESOLVE EWI!!! /*** 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 TABLE1WHERE COL1 = NUM1` }); C2.CLOSE();$$;
Transformation for the following lines correspond to custom types which are work in progress:
entireRow table1%ROWTYPE; // ROW TYPESTYPE 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.
letMY_COLLECTION=newMY_COLLECTION_TYPE();
SQL Implicit Cursor
Oracle
IN -> Oracle_03.sql
--Additional Params: -t JavaScriptCREATE 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'EXECUTE AS CALLERAS$$ !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!//AUTHID DEFINERnull// 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 FTABLE33VALUES(?, ?, ?, ?)`,[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 JavaScriptCREATE OR REPLACE PROCEDURE PROCEDURE1IS 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'EXECUTE AS CALLERAS$$// 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; } !!!RESOLVE EWI!!! /*** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ***/!!!/* EXIT loop_b WHEN (j > 3) */ ; } !!!RESOLVE EWI!!! /*** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ***/!!!/* EXIT loop_a WHEN (i > 3) */ ; }$$;
--Additional Params: -t JavaScriptCREATE 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';locationVARCHAR2(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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'EXECUTE AS CALLERAS$$// 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`); !!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ***/!!!/* TYPE NumList IS TABLE OF NUMBER */ ; !!!RESOLVE EWI!!! /*** 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 deptVALUES (?, ?, ?)`; EXEC(SQL_STMT,[DEPT_ID,DEPT_NAME,LOCATION]); SQL_STMT =`SELECT * FROM deptWHERE id = ?`; EXEC(SQL_STMT,[DEPT_ID],{ rec : dept_rec }); SQL_STMT =`UPDATE dept SET id = 200 WHERE id = ? RETURNING name INTO :2`; !!!RESOLVE EWI!!! /*** 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 deptwhere id = ?RETURNING name INTO :2`; !!!RESOLVE EWI!!! /*** 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 deptVALUES (12, 'NAME1', 'TEXAS')`); EXEC(`INSERT INTO DEPTVALUES(13, '${concatValue(DEPT_NAME)}', 'LA')`); EXEC(`DELETE FROM deptWHERE id = ?`,[DEPT_ID2]); EXEC(`ALTER SESSION SET DATE_INPUT_FORMAT = 'DD-MM-YYYY' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY'`); !!!RESOLVE EWI!!! /*** 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.
letSALS=newNUMLIST();
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;
--Additional Params: -t JavaScriptCREATE 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 exceptionnull; WHEN NO_DATA_FOUND THEN--Handle No data found exceptionnull; WHEN OTHERS THEN--Handler for others exceptionnull;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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'EXECUTE AS CALLERAS$$ !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!//AUTHID DEFINERnull// SnowConvert Helpers Code section is omitted.try { !!!RESOLVE EWI!!! /*** SSC-EWI-OR0052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ***/!!!/* deadlock_detected EXCEPTION */ ; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ***/!!!/* deadlock_dex EXCEPTION */ ; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!/* PRAGMA EXCEPTION_INIT(deadlock_detected, -60) */ ; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!/* PRAGMA EXCEPTION_INIT(deadlock_dex, -63) */ ;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 exceptionnull;break; }case`NO_DATA_FOUND`: {//Handle Nodata found exceptionnull;break; }default: {//Handler for others exceptionnull;break; } } }$$;
When there is not OTHERS handler, SnowConvert uses the "default" case in the switch that throws the original Error Object.
--Additional Params: -t JavaScriptCREATE OR REPLACE PROCEDURE PROC1 (param1 NUMBER, param2 NUMBER)ISBEGIN 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$// 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 JavaScriptCREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE2 ()IS localVar1 NUMBER; localVar2 VARCHAR(100);BEGINCASE (localVar1)WHEN 1 THEN localVar2 :='one';WHEN 2 THEN localVar :='two'; WHEN 3 THEN lovalVar :='three';ELSE localVar :='error';END CASE;CASEWHEN 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$// SnowConvert Helpers Code section is omitted. let LOCALVAR1; let LOCALVAR2; switch(LOCALVAR1) {case1:LOCALVAR2 =`one`;break;case2:LOCALVAR =`two`;break;case3: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`; } elseif ( !!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT localVar MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!! LOCALVAR ==2) { LOCALVAR =`two`; } elseif ( !!!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 JavaScriptCREATE 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 STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$// 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 INVARCHAR2) 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.
Related EWIs
SSC-EWI-0022: One or more identifiers in a specific statement are considered parameters by default.