Oracle PL/SQL to Java Migration

SQLines open source tools can help you convert Oracle PL/SQL stored procedures, functions and packages to Java.

SQLines Tools

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

Migration Reference

Language Elements

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)

Data Types

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.

CREATE PROCEDURE Statement

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.

CREATE PACKAGE Statement

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.

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();