<< Click to Display Table of Contents >> Current:  Create Data Set > SQL Data Set > Data Set for Stored Procedure  ORACLE Database Returning Data Set(Cursor)

The way of accessing the Oracle database is the same as the way of accessing the DB2 database. If the SQL statements for the stored procedure created in the database are as follows:

CREATE PROCEDURE SCOTT.SP_OUT_JOBS(ret_cursor1 OUT sys_refcursor)

IS

BEGIN

OPEN ret_cursor1 for select * from HR.JOBS;

END;

 

And the call statement written in the SQL statements of Yonghong Z-Suite is Call "CELINA"."SP_OUT_JOBS" (?{out_cursor}), as shown in the following figure.

At the same time, the parameters should be configured, including parameter type, direction and default value etc. as shown in the figure below:

 

The refreshed data is as shown in the figure below:

 

If multiple cursor results are defined in a stored procedure, you can specify the returned cursor results by configuring the size of the parameter _RETURN_SP_LOCATION_. If the statement for a stored procedure created in the database is:

CREATE OR REPLACE PROCEDURE AUTOCASE."PRO_QUERY_001"

(

in_lx  IN int,

p_cus_01 OUT   SYS_REFCURSOR,

p_cus_02 OUT   SYS_REFCURSOR    

)

AS

BEGIN

OPEN p_cus_01 FOR

   Select "year","month","day" From AUTOCASE.STRTNUMBER a   Where a."ints" = in_lx;

OPEN p_cus_02 FOR  

   Select"ID","market","product"From AUTOCASE."coffee_chain" a   Where a."ID" = in_lx;  

Exception

  WHEN OTHERS Then

  ROLLBACK;

  Return;

End pro_query_001;

Then the call statement written in the product's sql statement is call AUTOCASE.pro_query_001(55,?{ret_cursor1},?{ret_cursor2}),as follows.

At the same time to configure the parameters, the default return to the first cursor results, if you want to return to the cursor 2 results have, you need to configure the parameters including parameters _RETURN_SP_LOCATION_ for the integer 2, ret_cursor1 and ret_cursor2 type for cursor, the direction of the out, configure the following figure.

The refreshed data is the data in the second cursor's coffee_chain table, as shown below.