Converting an Oracle Function

[Former Member]

I need to convert the followinf function to SQL Anywhere. Here is the code:

CREATE OR REPLACE FUNCTION f_get_name_address_list  
   (p_name_and_address_id      IN  varchar2)
RETURN SYS_REFCURSOR 
IS  
    v_ret                      SYS_REFCURSOR;
BEGIN  
OPEN v_ret FOR    
   SELECT c.name_and_address_id,           
          c.full_name,           
          c.parent_record_id,           
          LEVEL pass    
     FROM s1_name_and_address c    
    START WITH c.name_and_address_id = p_name_and_address_id    
  CONNECT BY NOCYCLE PRIOR c.name_and_address_id = c.parent_record_id;  
RETURN v_ret;
END;

How do I handle "Start with" and "Connect by"?

I am using SQL Anywhere 11.0.1.2755

[VolkerBarth]

I'm not really familiar with Oracle so I can just give two hints:

• For the hierarchical query: SQL Anywhere uses RECURSIVE UNIONs (from the SQL Standard) to build recursive queries, cf. this current FAQ and Breck Carter's blog article.
  The particular pseudo columns like LEVEL must (and can) be simulated in the recursive query, cf. the "level" column in Breck's sample.

• SQL Anywhere does not support stored functions that return a cursor; instead it has stored procedures that can return result sets - and those can be used inside the FROM clause of a query block in case you have to join the result with other tables.