Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedure outParameters oracle Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure outParameters oracle

    Hi, I have a java project where I try to obtain a data type from a Oracle Stored Procedure.
    My problem is when I recive the Map (the Map is returned by execute method from StoredProcedure of Spring), the object I have received is a $Proxy3 Object and I don't know how to obtain my datas:

    In the Oracle data base I have this package:

    Code:
    CREATE OR REPLACE PACKAGE PACKAGE1 AS
    
      TYPE ref_cur IS REF CURSOR;
       
      PROCEDURE myprocedure (name in varchar2, stock_cursor in out ref_cur);
       
    END PACKAGE1;
    /
    
    
    CREATE OR REPLACE PACKAGE BODY PACKAGE1 AS
    
        PROCEDURE myprocedure (name in varchar2, stock_cursor in out ref_cur)
        IS
        
        BEGIN  
            OPEN stock_cursor FOR 'select 
                        id, 
                        name, 
                        email 
                      from 
                        users   
                    where 
                       name = '''||name||''' ';
                        
        END myprocedure;
    END PACKAGE1;
    /
    This procedure can return more of one register. Reason I return a ref cur.

    In Java, I try to do this:

    1- I have a test class (Test_Example), so I can debug step by step.
    2- In class (Example), called from the test class, is where I define the parameters (in and out) and where I compile and where the execute method is called (Actually the method is running the StoredProcedure class of).
    3- I have a class (GenericProced) that extends StoredProcedure, where I obtain my datasource.

    GenericProced.java
    Code:
    import java.util.Map;
    import java.util.Properties;
    import javax.naming.InitialContext;
    import javax.sql.DataSource;
    import org.apache.log4j.Logger;
    import org.springframework.jdbc.object.StoredProcedure;
    
    public abstract class GenericProced extends StoredProcedure
    {		 
    	 public GenericProced(String InitialContextFactory, String providerUrl, String urlPkgPrefixes , String pool, String procedimiento) throws Exception
    	  {		 
    		 super(givemeDatasource ( InitialContextFactory,  providerUrl, urlPkgPrefixes , pool), procedimiento);
    	  }
    	 	 
              /* I obtain the datasource from a configuracion file. It runs OK */
    	  public static DataSource givemeDatasource (String InitialContextFactory, String providerUrl, String urlPkgPrefixes , String pool) throws Exception
    	  {
    		    Properties prop = new Properties();
    		    prop.setProperty(InitialContext.INITIAL_CONTEXT_FACTORY,InitialContextFactory);
    		    prop.setProperty(InitialContext.PROVIDER_URL, providerUrl);
    		    prop.setProperty(InitialContext.URL_PKG_PREFIXES,urlPkgPrefixes);
    		        
    		    //Creamos un contexto y accedemos al recurso.
    		    InitialContext ic = new InitialContext(prop);
    		    DataSource ds = (DataSource)ic.lookup(pool); 
    	    return ds;
    	  }// fin método obtenerConexion.
    	  	 	  	
    	 
    	  public abstract void definition (); 	  
    	  public abstract Map execute(Object[] objeto);
    }

    Example.java

    Code:
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    import oracle.jdbc.OracleTypes;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import es.indra.isia.PL.ProcedimientoGenerico;
    
    public class Example extends GenericProced{
    
    	private static final String name_proce = "PACKAGE1.myprocedure ";
    	private static final String name = "name";
    	private static final String result = "result";
    	
    	
    	public Example(String InitialContextFactory, String providerUrl, String urlPkgPrefixes , String pool) throws Exception{
    	    super(InitialContextFactory,  providerUrl, urlPkgPrefixes , pool, name_proce);
    	    definition();
    	}
    		
    	public void definition() {
    		
    		setFunction(false); //Because is a procedure
    		
    		declareParameter(new SqlParameter(name, OracleTypes.VARCHAR));
    
    		declareParameter(new SqlOutParameter("result", OracleTypes.CURSOR, new RowMapper(){
    		     public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    			     MyEntity entity = new MyEntity(
    				     rs.getString("id"),
    				     rs.getString("name"),
    				     rs.getString("email")
    			     );
    
    			return entity;
                        }
                   }));
    		
                   compile();		
    	}
    
    	public Map execute(Object[] theObject) {
    	     
                   Map datas = new HashMap();
                   datas.put(name, (String) theObject[0]);
    	   
                   return super.execute(datas);
    	}
    }

    My RowMapper class MyEntity.java is:
    Code:
    public class MyEntity{
        private String id;
        private String name;
        private String email;
    
        public MyEntity(String id, String name, String email)
        {
            this.id = id;
            this.name = name;
            this.email = email;
        }
    
        public String toString(  ) {
             return "Employee :"+id+", "+ name;
        }
    
    	public String getId() {
    		return id;
    	}
    
    	public void setId(String id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public String getEmail() {
    		return email;
    	}
    
    	public void setEmail(String email) {
    		this. email = email;
    	}
    }

    Test_Example.java

    Code:
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import javax.xml.transform.TransformerException;
    import Example;
    
    public class Test_Example{
    
      public static void main(String[] args){
        try{
        	Test_Example test = new Test_Example();     
        	test.inicioDePrueba("Tom");
        }catch (Exception e){
          e.printStackTrace();
          System.out.println("ERRORRR: "+e);
        }
      }
          
      public void inicioDePrueba(String name) throws TransformerException, Exception{
    	  Example proc = new Example("org.jnp.interfaces.NamingContextFactory", 
    	  		"jnp://xxx.xxx.xxx.xxx:xxxx", 
    	  		"org.jboss.naming:org.jnp.interfaces",
    	  		"NPS_POOL_NODE");
    
        // RUN
        Object[] objects = new Object[1];
        objects[0] = name;
        
        Map resp = proc.execute(objects);
        
      
        Object obj = (Object)resultado.get("result");
      }  
    }

    When I debug the code (with Eclipse), when I run the line Map resp= proc.execute(objects);, It runs OK, and the resp variable is like:

    name ..............value
    resp..................HashMap (id=16)
    |
    |_ [0]................HashMap$Entry (id=43)
    ......|
    ......|_ key........."result"
    ......|_ value.......$proxy3 (id=47)

    This $Proxy3 Object, How can I read It?

    There is other way to obtain a cursor??

    thanks very much.
    Last edited by blackcat; Oct 5th, 2009, 07:01 AM.

  • #2
    Anyone???

    Comment


    • #3
      really????

      Comment

      Working...
      X