Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » how to return value in anonymous pl sql using eclipselink
how to return value in anonymous pl sql using eclipselink [message #1057940] Thu, 09 May 2013 18:05 Go to next message
Leonardo Luz Fernandes is currently offline Leonardo Luz Fernandes
Messages: 1
Registered: May 2013
Location: Brazil
Junior Member

I need guidance on how to retrieve the value of a variable in a plsql anonymous using EclipseLink. Below I leave a sample of my code. I cannot use functions and stored procedures because I dont have rights. So, how to pass an out parameter and get the result value.

DataReadQuery query = new DataReadQuery();
SQLCall sqlCall = new SQLCall();
StringBuilder plsql = new StringBuilder();

plsql.append("DECLARE ");
plsql.append("\n");
plsql.append("out_variable "); 
plsql.append("foo.bar");
plsql.append(".");
plsql.append("number_field");
plsql.append("%TYPE;");
plsql.append("\n");
plsql.append("BEGIN ");
plsql.append("\n");
plsql.append("UPDATE ");
plsql.append("number_field");
plsql.append(" SET number_field = (number_field+1)");
plsql.append(" WHERE ");
plsql.append(" key_field = ");
plsql.append(Key);
plsql.append(" ");
plsql.append(" RETURNING ");
plsql.append(" number_field ");
plsql.append(" INTO ");
plsql.append(" out_variable ; ");
plsql.append("\n");
plsql.append("END; \n");

sqlCall.setQueryString(plsql.toString());
sqlCall.setQuery(query);
query.setCall(sqlCall);

Session session = 
JpaHelper.getEntityManager(getEntityManager()).getActiveSession();
Object queryResult = session.executeQuery(query);



Re: how to return value in anonymous pl sql using eclipselink [message #1058593 is a reply to message #1057940] Tue, 14 May 2013 10:07 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You need to include a line that assigns the output parameter.

i.e.
plsql.append(" ###OUT_VARIABLE := out_variable ; ");

and call,

sqlCall.setCustomSQLArgumentType("OUT_VARIABLE", BigDecimal.class);

Or, you could unwrap the JDBC connection from the EntityManager and use JDBC directly.

http://wiki.eclipse.org/EclipseLink/Examples/JPA/EMAPI#Getting_a_JDBC_Connection_from_an_EntityManager



James : Wiki : Book : Blog : Twitter
Previous Topic:[Moxy] How to use dynamic Moxy for specific example
Next Topic:Map<Integer, BigDecimal> causes NPE because it is backed by a Hashtable
Goto Forum:
  


Current Time: Thu Jul 31 22:01:26 EDT 2014

Powered by FUDForum. Page generated in 0.02381 seconds