Using XI - RFC table and an Oracle stored procedure that returns a cursor.

[Former Member]

I need to create an interface using XI between an RFC table and an Oracle stored procedure that returns a cursor. We are on oarcle 9.2 and SP12.

My stored procedure looks something like this:

CREATE OR REPLACE

PROCEDURE testproc_xi2 (p_recordset1 OUT SYS_REFCURSOR,

in_quoteid IN varchar2 )

AS

BEGIN

OPEN p_recordset1 FOR

SELECT q.quote_id,

q.modified_by,

q.quote_status,

q.total_cost

FROM quote q

WHERE q.quote_id = in_quoteid

AND q.total_cost > 0 ;

END testproc_xi2 ;

My RFC has table and one import parameter .

I wanted to know how to create the data type for the ref cursor? and also for the table type in the RFC?

CAN XI handle multi rows coming from a Stored procedure? Are there any other alternative methods if this is not supported?Any pointers to this would be helpful.

I have called a Oracle SP from an RFC before, but that interface had one input parameter going to the stored procedure from the RFC and about 6 o/p parameters coming from the Stored procedure. This works fine.

Thanks for the help.

Mala

[ravi_raman2]

Mala,

i dont think there is anything called an rfc table...RFC stands for remote function call. That in essence would imply you need a rfc to jdbc connection.

yes XI can handle multiple rows cooming from the the stored procedure if you have them mapped appropriately.

Now as to how to create the data type within xi , you need to know what fields are going to be returned and whether they are nested and then just create them as you would for an xml

for ex

<Details>

<FirstName>

<LastName>

</Details>

that in xi would be smthing like

Details type of data occurence

FirstName type of data occurence

LastName type of data occurence.

Hope that helps.

If it does dont forget the points..:-)

[Former Member]

Ravi,

Yes, the interface is from RFC to JDBC. My question about the data type was related to cursor. The o/p type from the stored procedure is a cursor and what does this need to be accociated to while creating the data type -(it is not xsd:string/integer - is there some type like 'cursor' available. I could not find one.

Thanks

Mala

[niranjan_yerraguntla]

Mala,

Even we are having the same issue.

The documentaion says that you can use sql data type 'CURSOR'. Not sure where to use this . Should this be used while creating 'Message types' or while creating 'Mapping Objects'

Can any one help?

Thanks

Ninja