JPA + Oracle procedures

Due the Oracle's license agreement there is no public repository with the Oracle Driver JAR. So first off all download  oracle  jdbc driver and install to local maven repository (or publish to your artifactory server).

	mvn install:install-file
	-Dfile=ojdbc.jar
	-DgroupId=com.oracle
	-DartifactId=ojdbc11
	-Dversion=11.0
	-Dpackaging=jar

view raw mvn_install_oracle_driver.sh hosted with ❤ by GitHub

Let's create simple procedures:

	CREATE OR REPLACE PACKAGE CLIENT_INFORMATION AS
	PROCEDURE send_info (clientInfo IN VARCHAR2);
	PROCEDURE get_info (uid IN VARCHAR2, clientInfo OUT VARCHAR2);
	END CLIENT_INFORMATION;
	CREATE OR REPLACE PACKAGE BODY CLIENT_INFORMATION AS
	PROCEDURE send_info(clientInfo IN VARCHAR2) AS
	BEGIN
	DBMS_OUTPUT.PUT_LINE('data');
	END send_info;
	PROCEDURE get_info(uid IN VARCHAR2, clientInfo OUT VARCHAR2) AS
	BEGIN
	clientInfo := 'inparam: ' || uid;
	END get_info;
	END CLIENT_INFORMATION;

view raw simple-procedures.sql hosted with ❤ by GitHub

Create repository and call procedure using entity manager:

	import org.springframework.stereotype.Repository;
	import javax.persistence.EntityManager;
	import javax.persistence.ParameterMode;
	import javax.persistence.PersistenceContext;
	import javax.persistence.StoredProcedureQuery;
	@Repository
	public class ClientIndoRepository {
	@PersistenceContext
	private EntityManager em;
	private final Logger log = Logger.getLogger(ProcedureController.class.getName());
	public void sendClientInfo(String clientData) {
	this.em.createNativeQuery("BEGIN CLIENT_INFORMATION.send_info(:clientInfo); END;")
	.setParameter("clientInfo", clientData)
	.executeUpdate();
	}
	public String getClientInfo(String uid) {
	StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("CLIENT_INFORMATION.get_info");
	storedProcedure.registerStoredProcedureParameter("uid", String.class, ParameterMode.IN);
	storedProcedure.registerStoredProcedureParameter("clientInfo", String.class, ParameterMode.OUT);
	storedProcedure.setParameter("uid", uid);
	storedProcedure.execute();
	String result = (String)storedProcedure.getOutputParameterValue("clientInfo");
	log.info("RESULT: {}",result);
	return result;
	}
	}

view raw ClientIndoRepository.java hosted with ❤ by GitHub