This is an old revision of the document!
SQLines open source tools can help you convert Oracle PL/SQL stored procedures, functions and packages to Java.
You can use SQLines SQL Converter tool to convert Oracle PL/SQL to Java. Currently the conversion is available in the command line only:
sqlines -tl=java -s=oracle -t=oracle -in=package.sql |
PL/SQL language elements:
PL/SQL | Java | |||
1 | NULL | NULL value | null | Null reference |
2 | expr IS NULL | Check for NULL value | expr == null |
Derived data types:
PL/SQL | Java | |||
1 | table.column%TYPE | Data type the same as table.column | Java data type | |
2 | table%ROWTYPE | Structure and data types the same as table | Generated Java class |
Object types:
PL/SQL | Java | |||
1 | TYPE Typetab IS TABLE OF data_type INDEX BY BINARY_INTEGER | Collection of data_type objects | HashMap<Integer, data_type> | |
2 | tab Typetab | Creating collection | tab = new HashMap<Integer, data_type>() | |
3 | tab(index) := value | Assign a value | tab.put(index, value) |
Character data types:
PL/SQL | Java | |||
1 | VARCHAR2(n) | Variable-length string, 1 ⇐ n ⇐ 4000 | String |
Numeric data types:
PL/SQL | Java | ||||
1 | INTEGER | INT | Integer number | Integer | |
2 | NUMBER(p,0) | NUMBER(p) | Integer number | Integer | |
3 | NUMBER | NUMBER(*) | Floating-point number | Double |
Note that use of data type classes instead of primitive data types in Java (Double vs double i.e.) helps handling NULLs that are typically widely used in PL/SQL code.
Converting PL/SQL procedures:
PL/SQL | Java | |||
1 | CREATE OR REPLACE PROCEDURE Name | public static void Name | ||
2 | (param IN | OUT | IN OUT datatype, …) | Parameter definition | (datatype param, …) | |
3 | OUT | IN OUT datatype | Output parameter | datatypeOut | Wrapper class |
4 | IS | AS plsql_statements END | { java_statements } |
For more information, see Conversion of PL/SQL Statements.
Converting PL/SQL packages:
PL/SQL | Java | |||
1 | CREATE OR REPLACE PACKAGE BODY Name | public class Name | ||
2 | BEGIN plsql_statements END | Package instance initialization | { java_statements } |
For more information, see Conversion of PL/SQL Statements.
Variable declaration and assignment:
PL/SQL | Java | ||
1 | variable datatype := value | Variable declaration | datatype variable = value |
2 | variable CONSTANT datatype := value | Constant declaration | final datatype variable = value |
3 | variable := expression | Variable assignment | variable = expression |
Flow of control statements:
PL/SQL | Java | ||
1 | IF condition THEN … ELSIF … ELSE … END IF | IF statement | if (condition) { … } else if(…) { … } else { … } |
2 | LOOP statements END LOOP | Loop statement | while (true) { statements } |
3 | EXIT | Leave loop | break |
Cursor declaration and processing:
PL/SQL | Java | ||
1 | CURSOR cur (params) IS select_statement | Cursor declaration | String cur = 'select_statement' |
2 | OPEN cur | Open a cursor | ResultSet rs = stmt.executeQuery(cur) |
3 | FETCH cur INTO var, … | Fetch a cursor | if(cur_found = rs.next()) { var = rs.getObject(1); … } |
4 | cur%FOUND | Row was fetched | cur_found boolean variable |
5 | cur%NOTFOUND | No rows anymore | !cur_found boolean expression |
6 | CLOSE cur | Close a cursor | stmt.close() |
Error and exception handling:
PL/SQL | Java | ||
1 | ExceptionName EXCEPTION | Exception declaration | class ExceptionName extends Exception {} |
2 | RAISE ExceptionName | Raise exception | throw new ExceptionName() |
3 | EXCEPTION WHEN ExceptionName THEN … | Exception block | catch (ExceptionName e) { … } |
Transactions:
PL/SQL | Java | ||
1 | COMMIT | Commit the transaction | conn.commit(); |