Forum Stats

  • 3,757,133 Users
  • 2,251,200 Discussions
  • 7,869,738 Comments

Discussions

when calling sys_Refcursor to Spring from procedure :Type cannot be null

User_N7IDZ
User_N7IDZ Member Posts: 1 Green Ribbon

I have a code where I pull data to the Spring API from procedure. 

I have two out parameters, one of them is sys_refcursor and the other is number. When I enter IN parameters from Swagger, I get an error. The relevant codes are below:

Oracle Procedure :

     PROCEDURE Z_OEE_GetActualProducedTonnes_Detail(V_plant_config_num_id IN number, p_start_date in date, p_stop_date in DATE,p_cur001 OUT SYS_REFCURSOR, v_result OUT NUMBER)  IS 
  BEGIN
  v_result:=0;
    for p_cur001 in( 
     SELECT po.actual_weight, po.PIECE_ID, ph.ACTUAL_START 
          FROM piece po, 
               piece pi,
               piece_history_in phi,
               piece_history ph,
               piece_history_out pho,
               sty_quality sq 
         WHERE     po.plant_config_num_id = V_plant_config_num_id
               AND PH.ACTUAL_STOP BETWEEN p_start_date AND p_stop_date            
               AND ((V_plant_config_num_id=18 AND po.PRODUCT_TYPE LIKE 'PP%') OR V_plant_config_num_id<>18 )
       )
    loop
       v_result := v_result + p_cur001.actual_weight/1000;
    end loop;           

  END Z_OEE_GetActualProducedTonnes_Detail; 

Java Code :

@Repository
public class OeeDao  {
    
    
    @Autowired
    private EntityManager entitymanager;

      public Map.Entry<List<?>,BigDecimal> GetActualProducedTonnesDetail(int V_plant_config_num_id,Date p_start_date,Date p_stop_date)
        {
            
    
            StoredProcedureQuery query = entitymanager
                    .createStoredProcedureQuery("Z_PKG_OEE_NEW.Z_OEE_GetActualProducedTonnes_Detail")
                    .registerStoredProcedureParameter(
                        1,
                        Integer.class,
                        ParameterMode.IN
                    ).registerStoredProcedureParameter(
                            2,
                            Date.class,
                            ParameterMode.IN
                    ).registerStoredProcedureParameter(
                                3,
                                Date.class,
                                ParameterMode.IN
                    ).registerStoredProcedureParameter(
                        4,
                        void.class,
                        ParameterMode.OUT
                    ).registerStoredProcedureParameter(
                            5,
                            BigDecimal.class,
                            ParameterMode.OUT
                    ).setParameter(1, V_plant_config_num_id).setParameter(2, p_start_date).setParameter(3, p_stop_date);
                     
                    query.execute();
                     
                    ArrayList<List<?>> sys_refcursor=(ArrayList<List<?>>)query.getOutputParameterValue("sys_refcursor");
                    BigDecimal v_result=(BigDecimal)query.getOutputParameterValue("v_result");
                    
                    Map.Entry<List<?>,BigDecimal> resultList=new AbstractMap.SimpleEntry<>(sys_refcursor,v_result);
    
                    return resultList;
        }
    }
}

I encountered the error Type cannot be null.

it gives the error when trying to read p_cur001 ==> registerStoredProcedureParameter(
                        4,
                        void.class,
                        ParameterMode.OUT
                    )