Oracle PL/SQL to Java Migration

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

The tool does not translate to 100% ready to run Java programs, but still can be useful to eliminate routine conversion work.

Currently the conversion is available in command line only:

  # Convert a single file
  ./sqlines -s=oracle -t=oracle -tl=java -in=package.sql -out = Package.java
 
  # Convert multiple files
  ./sqlines -s=oracle -t=oracle -tl=java -in=*.sql

See more command line options.

Migration Reference

Language Elements

PL/SQL language elements:

PL/SQL Java
1 'string_literal' String literals in single quotes "string_literal" String literals in double quotes
2 NULL NULL value null Null reference
3 expr IS NULL Check for NULL value expr == null
4 -- Text Single line comment // Text

Comparison:

PL/SQL Java
1 a = 10 Comparing variable with an integer literal a == 10
2 a <> 10 Not equal comparison with an integer literal a != 10
3 a = b Comparing variables a.equals(b) == compares object references
4 a <> b Not equal comparison of variables !a.equals(b)

Operators:

PL/SQL Java
1 || Operator String concatenation + Operator
2 AND Logical AND operator &&
3 OR Logical OR operator ||

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(p,s) Fixed-point number BigDecimal
4 NUMBER NUMBER(*) Floating-point number BigDecimal

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.

SELECT Statement

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.

INSERT Statement

UPDATE Statement

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 FOR rec IN cursor LOOP ... END LOOP Cursor loop stmt.executeQuery(); while(rs.next()) {...}
3 LOOP statements END LOOP Loop statement while (true) { statements }
4 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 FOR rec IN cursor LOOP ... END LOOP Cursor loop stmt.executeQuery(); while(rs.next()) {...}
5 cur%FOUND Row was fetched cur_found boolean variable
6 cur%NOTFOUND No rows anymore !cur_found boolean expression
7 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();

Built-in PL/SQL Packages

Converting built-in PL/SQL packages:

Oracle Java
1 DBMS_OUTPUT.PUT_LINE(text) Output a message System.out.println(text)