how to get value from Oracle Procedure output parameter ?

[restroika gheo]

Hello.
In whole month, i search and testing in many more way's for get this answered. The question is simple :
i just want to get from output parameter using Parameter OUT.
First of all:
1. Primefaces 4
2. JSF 2.2
3. Oracle 11g
4. Eclipselink 2.5.2

My source :

oracle
----

CREATE OR REPLACE PROCEDURE refresh_persoeis (nama_tabel IN VARCHAR2, in_nrk IN VARCHAR2,userupd IN VARCHAR2,  tgmulai_out  out date)  is
BEGIN
IF UPPER(nama_tabel) = 'VIEW_PENDIDIKAN_JENJANG' THEN
     select 
      t.tgmulai into
tgmulai_out  
      from view_pendidikan_jenjang t where t.nrk=in_nrk;
END;

---

----------first call this function.

public void handlePendidikanNformalButton() {
        try {
            .
            .
            .
            pegawaiFacade.Refresh_Persoeis("VIEW_PENDIDIKAN_JENJANG", pegawai.getNrk(), customer);
           .
           .
           .                        
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Info", "Data Berhasil Disimpan"));
        } catch (EJBException ex) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "Data Gagal Disimpan"));
            ex.printStackTrace();
        }
    }

-------------------


PegawaiFacade.java =======================

import com.pelindo.simpers.ejb.facade.local.PegawaiFacadeLocal;
import com.pelindo.simpers.ejb.facade.remote.PegawaiFacadeRemote;
import com.pelindo.simpers.model.db.Pegawai;
import com.pelindo.simpers.model.db.PegawaiProcedure;
import javax.persistence.NoResultException;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;

public void Refresh_Persoeis(String nama_table, String nomor, String user) {       
         List<Object[]> temp = new ArrayList<Object[]>();
         String nama_tabel_="nama_tabel";
         String in_nrk_="in_nrk";
         String userupd_="userupd";
         try {
            StoredProcedureQuery query = getEntityManager().createNamedStoredProcedureQuery("PegawaiProsRefresh_Persoeis");           
            query.setParameter(nama_tabel_, nama_table);
            query.setParameter(in_nrk_, nomor);
            query.setParameter(userupd_, user);
            
            query.registerStoredProcedureParameter(nama_tabel_, String.class, ParameterMode.IN);            //1
            query.registerStoredProcedureParameter(in_nrk_, String.class, ParameterMode.IN);            //2
            query.registerStoredProcedureParameter(userupd_, String.class, ParameterMode.IN);           //3
            query.registerStoredProcedureParameter("tgmulai_out", Date.class, ParameterMode.OUT);        //4 
            System.out.print(query.getParameter(4));            
            System.out.print(query.getSingleResult());
         } catch (Exception ex) {        
            System.out.print("Exception no result excetion :"+ ex);
            temp = null;
        }
          
    }

=======================

Pegawai.java ==================
@Entity

@Table(name = "pegawai")
    @NamedStoredProcedureQueries({                 
        @NamedStoredProcedureQuery(name = "PegawaiProsRefresh_Persoeis", procedureName="Refresh_Persoeis", resultClasses = PegawaiProcedure.class,
                 parameters={  
                    @StoredProcedureParameter(queryParameter="nama_tabel", name="nama_tabel",direction=Direction.IN,type=String.class),  
                    @StoredProcedureParameter(queryParameter="in_nrk", name="in_nrk",direction=Direction.IN,type=String.class),  
                    @StoredProcedureParameter(queryParameter="userupd", name="userupd",direction=Direction.IN,type=String.class),                      
                    @StoredProcedureParameter(queryParameter="tgmulai_out", name="tgmulai_out",direction=Direction.OUT,type=Date.class)          
}),    @NamedStoredProcedureQuery(name = "PegawaiPros.Pros.RefreshAllPersoeis", procedureName = "refresh_all_persoeis", resultClasses = Pegawai.class)      
    })  

===========================

PegawaiFacadeRemote.java----------------------

    public void    Refresh_Persoeis(String nama_table,String nomor,String user);

.

---------------------------------------------------------

PegawaiProcedure.java ----------

public class PegawaiProcedure {
    public PegawaiProcedure(){
         
    }
   private String nama_tabel;

    public String getNama_tabel() {
        return nama_tabel;
    }

    public void setNama_tabel(String nama_tabel) {
        this.nama_tabel = nama_tabel;
    }

    public String getIn_nrk() {
        return in_nrk;
    }

    public void setIn_nrk(String in_nrk) {
        this.in_nrk = in_nrk;
    }

    public String getUserupd() {
        return userupd;
    }

    public void setUserupd(String userupd) {
        this.userupd = userupd;
    }
   private String in_nrk;
   private String userupd;
    
    @Column(name="tgmulai_out", insertable = false)  
    @Temporal(javax.persistence.TemporalType.DATE)
    private Date tgmulai_out;  

    public Date getTgmulai_out() {
        return tgmulai_out;
    }

    public void setTgmulai_out(Date tgmulai_out) {
        this.tgmulai_out = tgmulai_out;
    }
    
    public String toString() {  
        return "[PegawaiProcedure in_nrk =" + this.in_nrk + ",userupd=" +  
                 this.userupd + ",tgmulai_out=" + this.tgmulai_out + "]";  
    }
    
}

-----

if i deploy above script the result is :
INFO: nama_table :VIEW_PENDIDIKAN_JENJANG
INFO: nomor :KKT900031
INFO: user :SDM
INFO: Parameter[position=4]
WARNING: Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Invalid column index
Error Code: 17003
Call: BEGIN Refresh_Persoeis(nama_tabel=>?, in_nrk=>?, userupd=>?, tgmulai_out=>?); END;
bind => [7 parameters bound]
Query: ResultSetMappingQuery(name="PegawaiProsRefresh_Persoeis" )

Why this happen ? and how to build good script for get value from Oracle Procedure output parameter ?

Thank

[Tim Holloway]

Welcome to the JavaRanch, Restroika!

Personally, I don't recommend stored procedures. They split logic between multiple locations (which raises maintenance costs), source change management for the procedure code tends to be very weak compared to application code, they tie you to one specific vendor's database product. And programming the calls in and out can be troublesome.

On the other hand, there are times when stored procedures can be very useful. When you need performance, central co-ordination of data handling between multiple concurrent unrelated apps, or when some idiot has decreed that all database operations absolutely must be stored procedures and you want to keep your current employment position.

JSF doesn't itself get involved with database operations, so I think you're going to have better luck if I move this question to the Oracle forum. I cannot see what's wrong or why an operation of 4 variables says it's binding 7, but maybe someone there can.

[restroika gheo]

i changed my code to be like this :

Query query = getEntityManager().createNamedQuery("PegawaiProsRefresh_Persoeis");        
return query.getSingleResult().getClass()

and found error :

INFO:   Exception no result excetion :java.lang.RuntimeException: Uncompilable source code - incompatible types: unexpected return value

i try assign that to the object

Query query = getEntityManager().createNamedQuery("PegawaiProsRefresh_Persoeis");  
PegawaiProcedure p = (PegawaiProcedure) query.getSingleResult()      
return p;

but show this error:

cant be assign to the object

i see in another function

 public void refreshPersoeisSingle(String nipp_lama, String nomor, String user) {
        getEntityManager().createNamedQuery("Pegawai.Native.refreshPersoeisSingle").setParameter(1, nipp_lama).setParameter(2, nomor).setParameter(3, user).getSingleResult();
    }

and get normal result.

my question is why my first function in above showing error whereas my last function is get normal result ?

thanks