Hi guys, i have this scenario.
I have to call a procedure on Oracle, this is my fake stupid test:
Code:
create or replace PROCEDURE DATA_TEST_SEARCH_2 (SEARCHFIELD IN INPUT_TEST, ZZ IN VARCHAR2, EL IN VARCHAR2, CUR_SPEC OUT SYS_REFCURSOR) AS
BEGIN
OPEN CUR_SPEC FOR select * from TESTELEMENTPROCEDURE where test=EL;
END DATA_TEST_SEARCH_2;
The particular thing is that i am using a custom oracle type INPUT_TEST done like that:
Code:
create or replace TYPE INPUT_TEST IS VARRAY (300) OF VARCHAR2 (4000);
This is the structure of the table:
Code:
CREATE TABLE "TESTELEMENTPROCEDURE"
( "NAME" VARCHAR2(255 BYTE),
"TEST" VARCHAR2(255 BYTE),
"ID" NUMBER
) ;
That is the entity with the annotation @NamedStoredProcedureQuery
Code:
package fake.luca.test;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.metamodel.Type;
import javax.persistence.metamodel.Type.PersistenceType;
import static javax.persistence.ParameterMode.IN;
import static javax.persistence.ParameterMode.REF_CURSOR;
import java.io.Serializable;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.List;;
@Entity
@NamedStoredProcedureQuery(
name = "SEARCH",
procedureName = "DATA_TEST_SEARCH_1",
resultClasses = TestElementProcedure.class,
parameters = {
@StoredProcedureParameter(mode=IN, name="SEARCHFIELD", type=Struct.class),
@StoredProcedureParameter(mode=IN, name="ZZ", type=String.class),
@StoredProcedureParameter(mode=IN, name="EL", type=String.class),
@StoredProcedureParameter(mode=REF_CURSOR, name="CUR_SPEC", type=void.class),
}
)
public class TestElementProcedure implements Serializable{
private static final long serialVersionUID = -8469467744246561374L;
@Id
public Long id;
public String name;
public String test;
public Long getId() {
return id;
}
public void setId( Long id ) {
this.id = id;
}
public String getName() {
return name;
}
public void setName( String name ) {
this.name = name;
}
public String getTest() {
return test;
}
public void setTest( String test ) {
this.test = test;
}
@Override
public String toString() {
return "TestElementProcedure [name=" + name + ", test=" + test + "]";
}
}
I have tried with Struct, ArrayList, String[] on annotation
@StoredProcedureParameter(mode=IN, name="SEARCHFIELD", type=Struct.class)but i always get an Oracle Error similar to that:
dic 05, 2017 7:29:53 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 6550, SQLState: 65000
dic 05, 2017 7:29:53 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'DATA_TEST_SEARCH_2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I just wondering if it is possible use custom type oracle: i think i have found some suggestion using CustomType of Hibernate but i don't exactly get how to use.
Does anybody has encounter this kind of problem and solved whit success? I think I am missing some point but i don't find a clear example to solve my problem.
Should i have to create custom entity or something like that?
Thanks to any kind of support.
PS: I am using JDK 8, JPA 2.1 and hibernate 5.2.12
Login
FAQ
Search
