How to call a Stored Procedure in a process using Gel script

[Anon Anon]

Hi every body,

I've create a stored procedure to automate the change of Collaboration Manager when the PM is modified.

I've created a Process where I have a "Start Condition ( Project Manager != Project Manager [Previous Value] )"

But when I try to execute my SP, nothing seems to run.

Any ideas, which code do I need to put in my Custom Script.

Thx in adance,
Philippe

[Anon Anon]

You can call SPs from GEL using the <SQL:UPDATE> tags

There are a few examples on the boards; such as 85034077

(search for SQL:UPDATE and you'll get a few hits)

[Anon Anon]

Thanks Dave,

So If I look Carefully I have

<sql:update>
<!-- Procedure to be executed and which expects an argument in its invocation -->
EXEC PROCEDURE_SP ?
<!-- Description of parameter expected by the procedure for its implementation with success -->
<sql:param value="nameParam"/>
</sql:update>

However, I don't have any Parameter to set so I put
<sql:update>
EXEC Z_CUST_UPDATE_COLLAB_MGR
</sql:update>


but i receive the following message

org.apache.commons.jelly.JellyTagException: null:21:14: <sql:update> EXEC Z_CUST_UPDATE_COLLAB_MGR: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00900: invalid SQL statement
:angry:

[Anon Anon]

No real idea; theres some working code in a thread a couple of posts below (apparently) 98350193

GUESSES : do you need to reference your SP as [color=#ff0000]NIKU.[color]Z_CUST_UPDATE_COLLAB_MGR

How about an explicit BEGIN / END block (like in the code I just linked to) rather than EXEC (since I have now seen you are on Oracle)?

[Anon Anon]

Thx Dave,

Yes It seems I will have to look in this way, I will keep you inform...

[Anon Anon]

I have had success using the CALL command instead of the EXEC command and I think you will need to qualify the procedure name with the schema name as Dave suggests.

[Anon Anon]

OK I've done it


<sql:update>

CALL NIKU.Z_CUST_UPDATE_COLLAB_MGR_SP?

<sql:param value="pv_createdBy"/>

</sql:update>

Thanks to all