Highlighted

Call stored procedure with OUT parameter

Avatar

Guest

10-02-2009

Hello,



I have created a short-lived process. Within this process I am using the "FOUNDATION > JDBC > Call Stored Procedure" operation to call an Oracle procedure. This procedure has 3 parameters, 2 IN and 1 OUT parameter.



The procedure is being executed correctly. Both IN parameters receive the correct values but I am unable to get the OUT parameter's value in my process.



Rewriting the procedure as a function gives me an ORA-01460 since one of the parameters contains XML (>32K) so this is not option...



Has someone been able to call a stored procedure with an OUT parameter?



Regards,



Nico

Replies

Highlighted

yodadobe

17-02-2009

Object is Foundation, Execute Script

This is for a query, you can change to a stored procedure call. Pull the value back in the Java code then put into the process variable.



import javax.naming.InitialContext;

import javax.sql.DataSource;

import java.sql.*;



PreparedStatement stmt = null;

Connection conn = null;

ResultSet rs = null;

try {

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("java:IDP_DS");

conn = ds.getConnection();

stmt = conn.prepareStatement("select FUBAR from TB_PT_FUBAR where PROCESS_INSTANCE_ID=?");

stmt.setLong(1, patExecContext.getProcessDataLongValue("/process_data/@inputID"));

rs = stmt.executeQuery();

rs.next();

patExecContext.setProcessDataStringValue("/process_data/outData", rs.getString(1));

} finally {

try {

rs.close();

} catch (Exception rse) {}

try {

stmt.close();

} catch (Exception sse) {}

try {

conn.close();

} catch (Exception cse) {}

}
Highlighted

Avatar

Guest

19-02-2009

Nice example.



Can you provide the same thing for a stored procedure call ???



That will resolve the possibility to get the PrimKey of a newly

created record with the StorProc !



Thx,

BG.