pichin pichon

Pradeep Arun wrote:agreed. If not maprow what is the other options available.

Hi Pradeep,
I have read your post and I try to make something similar,

can you read my post and tell me what is my problem?

my post: https://coderanch.com/t/465377/Spring/StoredProcedure-outParameters-oracle

thanks very much.

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:
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:
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:
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); setFunction(false); //Because is a [b][u]procedure[/u][/b] 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:
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:
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.