Capturing a value returned by a stored procedure using GEL

[Anon Anon]

Folks,
I've looked at a lot of different posts that are similar to this but have not been able to get anything to work.
I had this code working with a MSSQL server

sql:update dataSource="${clarityDS}" var="nextId" escapeText="false">
{ call clarity.cmn_id_sp('PRJ_OBS_ASSOCIATIONS',NULL,1,NULL) }
</sql:update>

But the same code on oracle generates a bevy of errors depending on what I change.

The below code I used got me around the errors. However I can't figure out how to get the value out.

<sql:update dataSource="${clarityDS}" var="nextId" escapeText="false">

DECLARE


TABLENAME VARCHAR2(32767);


OUTPUT NUMBER;

BEGIN


TABLENAME :='PRJ_OBS_ASSOCIATIONS';

cmn_id_sp(TABLENAME,OUTPUT,1,NULL);

COMMIT;

END;
</sql:update>
${nexId} currently displays "1"

The OUTPUT oracle variable isn't being returned with the current code. Am I coming at this from the wrong direction?

This is being used as part of a auto initiated process within the application.

[Anon Anon]

What you are trying to do is not possible with the standard jelly tags. The only way to achieve what you are after is to write your own tag library.

[Anon Anon]

Can you "wrap" the call to the SP in some database code such that you can then "SELECT" the results back through a simple SELECT statemnent?

In Oracle terms I could write a little bit of PL/SQL that calls the SP and returns the output paraterms through an Oracle function. I can then write a SELECT statement that just "selects" the value of the function - and GEl can read that.

(Guessing a bit here - I may have actually done this in the past, I will look through some code as well to see if I have a real example).

--

EDIT : actually looking at the code you posted above, I think all I am saying is that you need to be able to do a

select OUTPUT from dual

somewhere in your SQL (just to get your OUTPUT variable to be returned in a SQL statement)

[Anon Anon]

Hi,
I have previously written a new Jelly Tag library to call the autonum stored procedure from a Gel script.
In its current implementation it can only cope with a single output variable, so it may suit your situation.
I'm happy to share it, just let me know.

Regards
Steve

[Ken_Kuperberg]

Steve,
I would love to see what you have written.
I am struggling with v13.0.1 and a process that requires auto-numbering to work.
Can you email it to me at kkuperberg at gmail.com?
I will provide all credit to you!
Thanks,
Ken

[Anon Anon]

Sure Ken, no problem. Work is busy at the moment, but I will get it over to you as soon as I can :smile

[Anon Anon]

Ken, you have mail... :grin:

[Anon Anon]

Hi Steven,

Is this ok if you could send me too the same code at [e-mail]?

i have used cmn some thing something autonum sp for this in my java program to create XMLs, but never used in Gel script. Thanks in advance.

Regards,
Manish Narang

[Anon Anon]

Manish, you have mail...

[Anon Anon]

Thank You Steve :)

[Anon Anon]

I've run across this problem, too.

I usually create a function to call the procedure I want and return the needed value with a call to it in a select statement.

Given your code above:

CREATE OR REPLACE FUNCTION c_get_id( p_tablename VARCHAR2 )
RETURN NUMBER
IS
v_output NUMBER;
BEGIN
clarity.cmn_id_sp('PRJ_OBS_ASSOCIATIONS',v_output);
RETURN v_output;
END c_get_id;
/

Then your gel code would be:

<sql:query dataSource="${clarityDS}" var="getNum">
SELECT c_get_id( 'PRJ_OBS_ASSOCIATIONS' ) new_num FROM dual
</sql:query>
<core:forEach items="${getNum.rowsByIndex}" trim="true" var="row">
<core:set var="newNum">${getNum.rows[0].new_num}</core:set>
</core:forEach>

--Mark