Clarity

Hi,

In one process i need to execute a Stored Procedure which has In and Out parameters.

So,help me by giving some examples on how to call procedure with in and out parameters?

Thanks in advance

You would have to have another procedure (or function) that does the call to this sp and stores the out parameter values where you can get them later.

There isn't a way to define out parameters in the <sql:update>. tag itself.

Hi

Please use the below sample code .


<!-- Geneate DPH Code from Auto Numbering Sction -->
<core:catch var="v_queryError">
<sql:query
dataSource="${clarityDS}" var="dph_code">






EXEC Z_AUTONUM_GET_NEXT_SP 'amd_demand_plan','code','NIKU.ROOT'





</sql:query>
</core:catch>
<core:if test="${v_queryError != Null}">
<gel:log level="ERROR">SQL ERROR = ${v_queryError} </gel:log>
</core:if>
<core:set
value="${dph_code.rows[0].CODE}" var="dphID"/>



Thanks
Senthil

HI Senthil,
the best approach is to use what Nick recommends which is create a function that calls the stored procedure.
then you write a quiery in your gel that calls the function and you will get the results you are looking for.

This is exactly how we got around the autonumber issue you are having.

the other option you have for the autonumber is to read the value from the autonumber table, then as soon as you read, execute the stored proc (this will increment the value by one as soon as you grab it). Of course option 2 has a small possibility of error as if 2 actions are happening at the exact time, it is possible that 2 actions get the same ID.
be it, it is very small possibility, i have seen it.

hope this helps
Federico

Here is a sample process which can call a stored proc by sending parameters and receiving output. This for a SQL Server backend but I think it should work for oracle as well. This code be useful for On-Demand users who do not have power to create procedures or functions but would like to call a OOB proc.

<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:setDataSource dbId="niku"/>
<sql:query escapeText="0" var="result"><![CDATA[

Insert your SQL SELECT STATEMENT here

]]></sql:query>
<core:forEach items="${result.rowsByIndex}" trim="false" var="row">
<core:set value="${row[0]}" var="Code"/>
<core:set value="${row[1]}" var="ProjectID"/>

<sql:query escapeText="0" var="result2">
declare @rvar Decimal(8,2)
exec z_custproc '${ProjectID}', @rvar output
select @rvar
</sql:query>
<core:forEach items="${result2.rowsByIndex}" trim="false" var="row2">
<core:set value="${row2[0]}" var="rVariable"/>
</core:forEach>
<sql:update var="rmn">
Insert your SQL UPDATE STATEMENT here
</sql:update>
</core:forEach>
</gel:script>