--Additional Params: -t JavaScriptCREATEORREPLACEPROCEDURE PROC1IS MyVariable1 NUMBER; MyOtherVariable2 NUMBER :=1;CURSOR C1 ISSELECT*FROM Table1 WHERE ID =123;CURSOR C2 (paramCursor1 NUMBER) ISSELECT COL1 AS C_1 FROM TABLE1 WHERE ID = paramCursor1;BEGINFOR myCursorRecord IN C1LOOP MyVariable1 := myCursorRecord.Col1;ENDLOOP;FOR myCursorRecord IN (SELECT*FROM Table1 WHERE ID = MyVariable1)LOOP MyVariable1 := myCursorRecord.Col1;ENDLOOP;<<Block1>>FOR myCursorRecord IN C2 (MyOtherVariable2)LOOP MyVariable1 := myCursorRecord.Col1;ENDLOOP Block1;END;
Snowflake
SnowConvert helpers Code removed from the example. You can find them here.
OUT -> Oracle_01.sql
CREATEORREPLACEPROCEDURE PROC1 ()RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$// 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 BYDEFAULT. REFERENCED TABLENOT 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 BYDEFAULT. REFERENCED TABLENOT 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 JavaScriptCREATEORREPLACEPROCEDURE 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 ISSELECT*FROM table1; C2 SYS_REFCURSOR;TYPE COLLECTION_TYPE ISTABLE OF TABLE1.COL1%TYPE; MY_COLLECTION MY_COLLECTION_TYPE := MY_COLLECTION_TYPE(); SOME_SELECT VARCHAR(200);BEGINOPEN 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'USING123;FETCH C2 BULK COLLECT INTO MY_COLLECTION LIMIT2;CLOSE C2;OPEN C2 FORSELECT*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
CREATEORREPLACEPROCEDURE 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" }}'EXECUTEASCALLERAS$$// 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 ISTABLE 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 JavaScriptCREATEORREPLACEPROCEDURE 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;BEGINEXECUTEIMMEDIATE'CREATE TABLE FTABLE35(COL1 NUMBER(3))';IFSQL%FOUND THEN STMT_STAT1 :=1;ENDIF;IFSQL%NOTFOUND THEN STMT_STAT2 :=1;ENDIF;IFSQL%ISOPEN THEN STMT_STAT3 :=1;ENDIF;EXECUTEIMMEDIATE'INSERT INTO FTABLE33 VALUES(:D1,:D2,:D3,:D4)'USINGSQL%ROWCOUNT, STMT_STAT1, STMT_STAT2, STMT_STAT3;END;
Snowflake
SnowConvert helpers Code removed from the example. You can find them here.
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 JavaScriptCREATEORREPLACEPROCEDURE 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;ENDLOOP loop_c; EXIT loop_b WHEN (j >3);ENDLOOP loop_b; EXIT loop_a WHEN (i >3);ENDLOOP loop_a;END;
Snowflake
SnowConvert helpers Code removed from the example. You can find them here.
OUT -> Oracle_04.sql
CREATEORREPLACEPROCEDURE 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" }}'EXECUTEASCALLERAS$$// 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 JavaScriptCREATEORREPLACEPROCEDURE 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 ISTABLE OF NUMBER; sals NumList;BEGINEXECUTEIMMEDIATE'CREATE TABLE dept (id NUMBER, name varchar(14), location varchar2(13))'; sql_stmt :='INSERT INTO dept VALUES (:1, :2, :3)';EXECUTEIMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt :='SELECT * FROM dept WHERE id = :idd';EXECUTEIMMEDIATE sql_stmt INTO dept_rec USING dept_id; sql_stmt :='UPDATE dept SET id = 200 WHERE id = :1 RETURNING name INTO :2';EXECUTEIMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd; sql_stmt :='delete from dept where id = :1 RETURNING name INTO :2';EXECUTEIMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd;EXECUTEIMMEDIATE'INSERT INTO dept VALUES (12, ''NAME1'', ''TEXAS'')';EXECUTEIMMEDIATE'INSERT INTO DEPT VALUES(13, '''|| dept_name ||''', ''LA'')';EXECUTEIMMEDIATE'DELETE FROM dept WHERE id = :num'USING dept_id2;EXECUTEIMMEDIATE'ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MM-YYYY''';EXECUTEIMMEDIATE'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
CREATEORREPLACEPROCEDURE 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" }}'EXECUTEASCALLERAS$$// 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 ISTABLE 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.
EXECUTEIMMEDIATE'SELECT id FROM dept' BULK COLLECT INTO sals;
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. **CREATEORREPLACEPROCEDURE PROCEDURE2 ()RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$// 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`; }$$;
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. **CREATEORREPLACEPROCEDURE PROCEDURE2 ()RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$// 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`)));$$;
----** 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.