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:)

[justin_willey]

I don't know anything about Oracle, but if you want a SQLA procedure to return a result set, you don't need to declare a cursor, just put the sql query in the body of the procedure and include a result clause eg:

ALTER PROCEDURE "pears"."UserPayrollRuns" 
(IN @EmployeeCodeList char(255), IN @startdate date, IN @enddate date)
RESULT(EmployeeCode char(6),CompanyCode char(2),PaymentDate date,Period char(21),GrossPay double,Deductions double, 
NetPay double,PeriodNumber char(2),payslipfullpath long varchar) 
begin
    select 
       s.EmployeeCode as "EMPLOYEECODE",
       s.CompanyCode as "COMPANYCODE", 
       date(PaymentDate) as "PAYMENTDATE",
    etc etc
    group by whatever

 end

[Former Member]

Thanks, people - I now have a better understanding re: getting result sets from a procedure.

I was able to correct my errors. Murray