Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Call stored procedure with OUT parameter

Not applicable
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
6 Replies
Jasmin_Charbonn
Level 10
Level 10
We can't use out parameter in stored procedures in the current release.



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



If possible, could you please provide more input?
yodadobe
Level 6
Level 6
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) {}

}
Not applicable
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.