-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Call a Oracle Procedure with Custom Type in input
PostPosted: Wed Dec 06, 2017 3:17 am 
Newbie

Joined: Thu May 05, 2011 6:33 am
Posts: 16
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


Top
 Profile  
 
 Post subject: Re: Call a Oracle Procedure with Custom Type in input
PostPosted: Wed Dec 06, 2017 6:25 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Try with type = Class.class:

Code:
@StoredProcedureParameter(mode=IN, name="SEARCHFIELD", type=Class.class)


Top
 Profile  
 
 Post subject: Re: Call a Oracle Procedure with Custom Type in input
PostPosted: Tue Dec 12, 2017 5:55 am 
Newbie

Joined: Thu May 05, 2011 6:33 am
Posts: 16
Hi Vlad,
thanks for the short reply. I have tested using Class but do not solve anything.

Should I create an object that is the expected object in Oracle?

Thanks for your time and any kind of suggestion

Luca


Top
 Profile  
 
 Post subject: Re: Call a Oracle Procedure with Custom Type in input
PostPosted: Tue Dec 12, 2017 6:27 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
I'm not really sure. For custom Oracle types, I think you need to use the custom Oracle JDBC Driver support they offer, in which case, you will have to use plain JDBC.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.