Announcement Announcement Module
Collapse
No announcement yet.
IN/OUT stored procedure parameters question Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • IN/OUT stored procedure parameters question

    I have a PL/SQL procedure that uses an IN/OUT cursor parameter. How do I handle this in Spring?

    sample PL/SQL procedure...

    Code:
       PROCEDURE PR_SEARCH_FOR_EQUIPMENT
             (p_equip_seq_1      IN     NUMBER,
              p_equip_seq_2      IN     NUMBER,
              p_equipment_cur    IN OUT REF CURSOR)
    sample Java code...



    Code:
    public class FindEquipmentSpringDaoImpl extends JdbcDaoSupport implements IFindEquipmentDao  
    {
      
    	private Log logger = LogFactory.getLog(FindEquipmentSpringDaoImpl.class);
    
      private FindEquipmentQuery findEquipmentQuery;	
    
      protected final static String findEquipmentQuerySql = 
        "{call pr_search_for_equipment(?, ?, ?)}";
    
    
      public FindEquipmentSpringDaoImpl()
      {
        super();
    
        logger.info("creating FindEquipmentSpringDao");
      }
    
    
      public void initDao() throws ApplicationContextException
      {
        logger.debug("initializing dao after properties are set");  
    
        findEquipmentQuery = new FindEquipentQuery(getDataSource(), findEquipmentQuerySql);
      }  
    
    
      public GuardTableModel findEquipmentDetails()
    	  	throws DataAccessException
      {
        try
    	  {
     	   
          Map params = new HashMap();	 
          params.put("p_equip_seq_1", 100);
          params.put("p_equip_seq_2", 200);
        
          // not sure how to define this...
          params.put("p_equipment_cur", null);
    	  	   
          findEquipmentQuery.execute((Map)params);		
        }
        catch (DataAccessException de) 
        {	    	
          logger.error("findEquipment Operation unsuccessful");
          throw de;
        }
      }
      
      
      protected static class FindEquipmentQuery
        extends StoredProcedure
      {
    
        protected FindEquipmentQuery(DataSource ds, String sql)
        {
          super(ds, sql);
          declareParameter(new SqlParameter("p_equip_seq_1", Types.INTEGER));
          declareParameter(new SqlParameter("p_equip_seq_2", Types.INTEGER));
          
          // this will define the "out" part of my variable... how do I define the "in" part ??
          declareParameter(new SqlReturnResultSet("p_equipment_cur", new equipmentMapper()));
          compile();
        }
        
        private class equipmentMapper implements RowCallbackHandler
        {
          public void processRow(ResultSet rs) throws SQLException
          {
            ResultSet cur = (ResultSet) rs.getObject(1);
            while (cur.next())
            {
    	        System.out.println("Equipment number = " + cur.getString(1));
            }
            cur.close();
          }
        }
      }
    }

  • #2
    Check this out.
    http://forum.springframework.org/showthread.php?t=10363
    I don't know if you will have any issues with the refcursor type parameter. I have never used one.
    Last edited by robyn; May 19th, 2006, 05:43 AM.

    Comment


    • #3
      Hi,

      How do you use the refcursor passed as a parameter in the procedure? Is there a calling pl/sql that passes in the ref cursor to this procedure for it fetching data? Usually, its the other way round, getting a handle of the ref cursor executed query as a jdbc result set. Not sure about jdbc supporting passing of a handle from a query executed in java to the refcursor. You might find this link useful on ref cursors: http://oraclesvca2.oracle.com/docs/c..._ora.htm#i7106

      Regards,

      Amit

      Comment

      Working...
      X