Call stored procedure with OUT parameter

Avatar

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

Avatar

Avatar

Jasmin_Charbonn

Avatar

Jasmin_Charbonn

Jasmin_Charbonn

10-02-2009

We can't use out parameter in stored procedures in the current release.



Jasmin

Avatar

Avatar

yodadobe

Avatar

yodadobe

yodadobe

16-02-2009

A script object could be used.

Avatar

17-02-2009

@S. Bishop ("A script object could be used.")



If possible, could you please provide more input?

Avatar

Avatar

yodadobe

Avatar

yodadobe

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) {}

}

Avatar

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.