What is the best approach to convert an oracle Procedure that uses SYS_REFCURSOR

[Former Member]

I have an Oracle procedure that uses SYS_REFCURSOR to return the output. Oracle SQL:

CREATE OR REPLACE PROCEDURE p_purchase_sale_amounts
( adt                           IN  date,
  cursor_x                     OUT  SYS_REFCURSOR
)
AS
   ld_offsetdate               date;

BEGIN
   ld_offsetdate               := adt;

   OPEN cursor_x FOR
   SELECT type,
          FLOOR(SUM(amount1)),
          currency_code,
          currency_description,
          CASE WHEN type = 'A/P'
                   THEN CAST('TotalPurchases' AS varchar2(50))
                   ELSE CAST('TotalSales' AS varchar2(50))
          END                                  AS xlm_tag
     FROM (SELECT 'A/R'                        AS type,
                  COALESCE(SUM((ROUND(s1_prepayment.extended_amount,2) + ROUND(s1_prepayment.tax_1_amount, 2) + ROUND(s1_prepayment.tax_2_amount, 2) + ROUND(s1_prepayment.tax_3_amount,2))),0)
                                               AS amount1,
                  s1_prepayment.currency_code  AS currency_code,
                  s1_currency.description      AS currency_description
             FROM s1_prepayment,
                  s1_grainsmart_option,
                  s1_currency
            WHERE s1_prepayment.invoice_date >= ld_offsetdate
              AND s1_grainsmart_option.go_key = 1
              AND s1_grainsmart_option.ar_software_interface <> 'GS'
              AND s1_prepayment.currency_code = s1_currency.currency_code
            GROUP BY s1_prepayment.currency_code,
                     s1_currency.currency_code,
                     s1_currency.description) a
            GROUP BY currency_code,
                     description
          ) xxx
    GROUP BY xxx.type,
             xxx.currency_code,
             xxx.currency_description;

END;
/

Is there a similar methodology on SQL Anywhere?

Thanks Murray

[VolkerBarth]

I'm not familiar with ORACLE and SYS_REFCURSOR, either, but it seems that the SYS_REFCURSOR is meant to give the client calling the ORACLE STP control over how many rows from the result set it wants to fetch. (So I would assume that otherwise the client would be supplied with all rows.)

If this is correct, please have a look at Mark's thorough description of the way SQL Anywhere's procedures handle their result sets:

• What happens when a procedure RESULT set is ignored?

In a nutshell, SQL Anywhere generally just provides as many rows as the client requests, so that seems quite similar to the SYS_REFCURSOR facility IMVHO.

[justin_willey]

That is a really useful reference - thanks

[VolkerBarth]

Yep, the Golden WATCOM rule:)