Oracle PL/SQL to Java Migration

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

To run the conversion in command line:

  # 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 -out=out_dir/

See more command line options.

To run the conversion in SQLines Studio, edit sqlines.cfg and set -tl=java option:

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

Data Types

Character data types:

	PL/SQL		Java
1	CHAR(n)	Fixed-length string, 1 <= n <= 2000	String
2	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.

Data Structures

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)

Records:

	PL/SQL		Java
1	TYPE name IS RECORD (elements)	Record objects	Java class with getters and setters

Built-in SQL Functions

Converting NULL processing functions:

	Oracle		Java
1	NVL(exp, replacement)	Replace NULL with the specified value	Functions.nvl(exp, replacement)

SELECT Statement

Converting queries:

	PL/SQL		Java
1	SELECT INTO		conn.prepareStatement("SELECT ..."); ... stmt.statement.executeQuery(); ...

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 Name	Package specification	Removed except type declarations
2	CREATE OR REPLACE PACKAGE BODY Name	Package body	public class Name
3	BEGIN plsql_statements END	Package code	{ java_statements }

For more information, see Conversion of PL/SQL Statements.

INSERT Statement

Converting SQL INSERT statement:

	PL/SQL		Java
1	INSERT INTO tab VALUES (exp1, ...)		conn.prepareStatement("INSERT INTO tab VALUES (?, ...)"); ...

UPDATE Statement

Converting SQL UPDATE statement:

	PL/SQL		Java
1	UPDATE tab SET c1 = v1 ... WHERE ...		conn.prepareStatement("UPDATE tab SET c1 = ? ... WHERE ..."); ...

DELETE Statement

Converting SQL DELETE statement:

	PL/SQL		Java
1	DELETE FROM tab WHERE ...		conn.prepareStatement("DELETE FROM tab WHERE ..."); ...

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	boolean cur_found = rs.next(); if(cur_found) { 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	EXIT WHEN cur%NOTFOUND	Exit cursor loop	if(!cur_found) break;
8	CLOSE cur	Close a cursor	stmt.close()

Exception handling:

	PL/SQL		Java
1	BEGIN … EXCEPTION … END	Exception block	try { … } catch (SQLException e) { … }
2	WHEN NO_DATA_FOUND THEN NULL	Ignore no data found exception	Remove as it's handled by ResultSet.next()
3	WHEN OTHERS THEN NULL	Ignore all exceptions	catch (SQLException) { }

Custom 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)