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
BedrockMission!

Learn More

View all

Sign in to view all badges

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
Coach
Level 10
Jasmin_Charbonn
Level 10

Likes

149 likes

Total Posts

2,154 posts

Correct Reply

97 solutions
Top badges earned
Coach
Give Back 1000
Give back 900
Give Back 800
Give back 600
View profile

Avatar
Coach
Level 10
Jasmin_Charbonn
Level 10

Likes

149 likes

Total Posts

2,154 posts

Correct Reply

97 solutions
Top badges earned
Coach
Give Back 1000
Give back 900
Give Back 800
Give back 600
View profile
Jasmin_Charbonn
Level 10

10-02-2009

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



Jasmin

Avatar

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
yodadobe
Level 5

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
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
yodadobe
Level 5

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.

Avatar

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
yodadobe
Level 5

19-02-2009