Call oracle functions java

JavaMadeSoEasy.com (JMSE)

The java.sql. CallableStatement is an interface which can be used to execute SQL stored procedures in java.

create table EMPLOYEE ( ID number ( 4 ), NAME varchar2 ( 22 ), SALARY number ( 8 ), CREATION_DATE date );

—Procedure accepts id, name, salary and creation_date as IN parameter inserts them in EMPLOYEE table

Example/ Full Programs JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java — CallableStatement example in java

Additionally, you may use below script for executing above Stored Procedure as a standalone procedure from oracle SQL developer .

The java.sql. CallableStatement is an interface which can be used to execute SQL stored procedures in java.

create table EMPLOYEE ( ID number ( 4 ), NAME varchar2 ( 22 ), SALARY number ( 8 ), CREATION_DATE date );

Example/ Full Programs JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter — CallableStatement example in java

System. out .println( «name background-color: transparent; color: black; font-family: Consolas; font-size: 13.333333333333332px; font-style: normal; font-variant: normal; font-weight: 700; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;»> + callableStmt.getString(2));

System. out .println( «salary background-color: transparent; color: black; font-family: Consolas; font-size: 13.333333333333332px; font-style: normal; font-variant: normal; font-weight: 700; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;»> + callableStmt.getInt(3));

System. out .println( «creationDate background-color: transparent; color: black; font-family: Consolas; font-size: 13.333333333333332px; font-style: normal; font-variant: normal; font-weight: 700; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;»> +callableStmt.getDate(4)); //returns java.Sql.Date

Источник

How to call oracle function or procedure using hibernate (entitymanager) or jpa in Java?

When developing Java applications, there may come a time when you need to call a stored function or procedure in an Oracle database. The process can be complicated when using Hibernate (EntityManager) or JPA, as these frameworks are built for object-relational mapping (ORM) and don’t directly support stored procedures or functions.

Method 1: Native SQL Query

To call an Oracle function or procedure using Hibernate’s EntityManager or JPA, you can use Native SQL Query. Here are the steps to do it:

1. Create a NamedStoredProcedureQuery with the name of your function or procedure and the result class.

@NamedStoredProcedureQuery( name = "yourFunctionOrProcedureName", procedureName = "your_function_or_procedure_name", resultClasses = < YourResultClass.class >)

1. Inject the EntityManager and call the createNamedStoredProcedureQuery method with the name of your NamedStoredProcedureQuery.

@PersistenceContext private EntityManager entityManager; . StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery("yourFunctionOrProcedureName");

storedProcedureQuery.setParameter("parameterName", parameterValue);

ListYourResultClass> result = storedProcedureQuery.getResultList();

Here’s an example of how to call an Oracle function with one parameter:

@NamedStoredProcedureQuery( name = "getEmployeeName", procedureName = "get_employee_name", resultClasses = < String.class >) public class Employee < . >. StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery("getEmployeeName"); storedProcedureQuery.setParameter("employeeId", 1); String employeeName = (String) storedProcedureQuery.getSingleResult();

And here’s an example of how to call an Oracle procedure with two parameters:

@NamedStoredProcedureQuery( name = "updateEmployeeName", procedureName = "update_employee_name", ) public class Employee < . >. StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery("updateEmployeeName"); storedProcedureQuery.setParameter("employeeId", 1); storedProcedureQuery.setParameter("newName", "John Doe"); storedProcedureQuery.execute();

Method 2: Named Stored Procedure Query

To call an Oracle function or procedure using Hibernate (EntityManager) or JPA with Named Stored Procedure Query, follow these steps:

@NamedStoredProcedureQuery( name = "getEmployeeName", procedureName = "GET_EMPLOYEE_NAME", parameters =  @StoredProcedureParameter(mode = ParameterMode.IN, name = "emp_id", type = Long.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_name", type = String.class) > )

EntityManager entityManager = entityManagerFactory.createEntityManager();

StoredProcedureQuery storedProcedure = entityManager.createNamedStoredProcedureQuery("getEmployeeName");

storedProcedure.setParameter("emp_id", 1234L);

String empName = (String) storedProcedure.getOutputParameterValue("emp_name");

That’s it! You have successfully called an Oracle function or procedure using Hibernate (EntityManager) or JPA with Named Stored Procedure Query.

Here is the complete code example:

@Entity @NamedStoredProcedureQuery( name = "getEmployeeName", procedureName = "GET_EMPLOYEE_NAME", parameters =  @StoredProcedureParameter(mode = ParameterMode.IN, name = "emp_id", type = Long.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_name", type = String.class) > ) public class Employee  @Id private Long id; private String name; // getters and setters > EntityManager entityManager = entityManagerFactory.createEntityManager(); StoredProcedureQuery storedProcedure = entityManager.createNamedStoredProcedureQuery("getEmployeeName"); storedProcedure.setParameter("emp_id", 1234L); storedProcedure.execute(); String empName = (String) storedProcedure.getOutputParameterValue("emp_name");

Method 3: @NamedStoredProcedureQuery Annotation

To call an Oracle function or procedure using Hibernate EntityManager or JPA, you can use the «@NamedStoredProcedureQuery» annotation. This annotation allows you to map a named stored procedure query to a Java class.

Here is an example of how to use «@NamedStoredProcedureQuery» to call an Oracle function:

@NamedStoredProcedureQuery( name = "getEmployeeName", procedureName = "get_employee_name", parameters =  @StoredProcedureParameter(mode = ParameterMode.IN, name = "employee_id", type = Integer.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "employee_name", type = String.class) > ) @Entity public class Employee  @Id private Integer id; private String name; // getters and setters >

In this example, we have defined a named stored procedure query called «getEmployeeName» that maps to an Oracle function called «get_employee_name». The function takes an «employee_id» parameter and returns an «employee_name» output parameter.

To call this function using EntityManager or JPA, you can use the following code:

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("getEmployeeName"); query.setParameter("employee_id", 1); query.execute(); String employeeName = (String) query.getOutputParameterValue("employee_name");

In this code, we first create a stored procedure query using the EntityManager’s «createNamedStoredProcedureQuery» method and pass in the name of our named stored procedure query. We then set the «employee_id» parameter to 1 and execute the query using the «execute» method. Finally, we retrieve the value of the «employee_name» output parameter using the «getOutputParameterValue» method.

That’s it! With this simple example, you can call an Oracle function or procedure using Hibernate EntityManager or JPA.

Источник

Call Java Oracle Functions From Java program

Beginning with version 8i, the Oracle database includes a fully functional Java Virtual Machine, Oracle JVM. Out of this close relationship between Oracle and Java emerges an important technology for Oracle developers: Java stored procedures. With Java stored procedures, developers have the ability to harness the power of Java when building database applications. This article provides an overview of this increasingly popular technology. Its intended audience is Java developers new to Oracle, and Oracle PL/SQL developers with a basic understanding of Java.

There are a number of scenarios where it makes sense to use Java stored procedures. Given Java’s popularity today, it is certainly possible that members of a development team are more proficient in Java than PL/SQL. Java stored procedures give Java programmers the ability to code in their preferred language. For experienced PL/SQL developers, using Java allows you to take advantage of the Java language to extend the functionality of database applications. Also, Java makes it possible to write database-neutral code. Better yet, it allows you to reuse existing code and dramatically increase productivity.

The better your understanding of Java stored procedures, the easier it will be to decide how they best fit your development practices. A common approach is to use PL/SQL when writing programs primarily concerned with database access. Then, as requirements arise that are more easily satisfied by Java, classes can be developed, followed by the necessary call specifications.

Perhaps, for instance, that a database application needs to interact with operating system files and directories. Oracle provides limited functionality with the UTL_FILE package for accessing system files. However, Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on. So, why not leverage this power? The user of a command-line PL/SQL program might want to place job parameters in a configuration file. You could write a Java method to read these parameters.

Example: To run the example you will need the Sun JDK and Oracle 8i or better.

The steps involved are:-

1) Create a Java class containing your stored procedure/function.

--DROP JAVA SOURCE MyUser."TestFunction"; CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED MyUser."TestFunction" as package util.oracle; public class TestFunction < public static String testcall(String input) < return "prashant" + input; >> ;

2) Grant permissions to execute java source.

GRANT EXECUTE ON JAVA SOURCE MyUser."TestFunction" TO My_User;

3) Compile the class outside of the database using a Java compiler.(Optional) This step will be better and required just to ensure java code is running properly.

4) Create a oracle package having a oracle function

CREATE OR REPLACE PACKAGE MyUser.TESTFUNCTION AS FUNCTION testcall(Param1 VARCHAR2) return VARCHAR2 ; end testfunction;

Now run the script below where it contain body for the package declared.

CREATE OR REPLACE PACKAGE body MyUser.TESTFUNCTION AS FUNCTION testcall(Param1 VARCHAR2) return VARCHAR2 AS LANGUAGE java NAME 'util.oracle.TestFunction.testcall(java.lang.String) return java.lang.String'; end TESTFUNCTION; /

Please do execute the package along with functions.

5) Now create a java client and run the program.Make sure you have classes12.zip in classpath.

Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:dev4","dba","dba"); System.out.println("Connection created. "); String call = "< ? = call MyUser.TESTFUNCTION.testcall(?) >"; CallableStatement cstmt = con.prepareCall(call); cstmt.setQueryTimeout(1800); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, "hello. "); cstmt.executeUpdate(); String val = cstmt.getString(1); cstmt.close(); con.close(); System.out.println(val); Prints Connection created. prashanthello.

About Krishna Srinivasan

He is Founder and Chief Editor of JavaBeat. He has more than 8+ years of experience on developing Web applications. He writes about Spring, DOJO, JSF, Hibernate and many other emerging technologies in this blog.

Источник