cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Stored Procedure Returning Cursors in XI 3.0

Former Member
0 Kudos

In using a stored procedure, has anyone returned either a cursor back or a record structure?

An Oracle example would look like the following:

PROCEDURE qryEmployee(pUserID IN varchar2, pEmployee out t_cursor)

IS v_cursor t_cursor;

Begin

open v_cursor for

Select e.user_id, e.emp_name, e.functional_group_id

from TOTS_EMPLOYEE e

where e.user_id = pUserID;

pEmployee := v_cursor;

Exception

when NO_DATA_FOUND then

null;

when OTHERS then

raise;

End qryEmployee;

The idea is to return a curosr that has records attached and have XI map it out to an XI response.

It would be called within XI as follows:

<MT_Employee_Procedure>

<root>

<qryEmployee action="EXECUTE">

<table>TOTS_PROCEDURES.qryEmployee</table>

<pUserID isInput="true" type="VARCHAR">baudaisk</pUserID>

<pEmployee isOutput="true" type="CURSOR"></pEmployee>

</qryEmployee>

</root>

</MT_Employee_Procedure>

As far as I know the type CURSOR is not supported in XI. Is there a way of returning cursor information using stored procedures?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

There is no cursor support in XI. Similar to a table-select, the store procedure will return a resultset.

Bill

Former Member
0 Kudos

Is the result set based on the selects within the procedure and uses the same structure as the SELECT statment?

Former Member
0 Kudos

Yes, it should be.

You can run a test by defining any "bogus" interface for the response and take a look in SXMB_MONI for the exact structure returned. Then, define the message type based on this structure, so mapping can be done.

Regards,

Bill

Former Member
0 Kudos

Hi Bill,

Thank you for your help in this.

Best Regards,

Ken.

Answers (1)

Answers (1)

Former Member
0 Kudos

See own Message

Message was edited by: Mark Newman