Question Re: Converting an Oracle Function in Technology Q&A

Converting an Oracle Function

Former Member — Wed, 18 Apr 2012 15:23:51 GMT

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

Re: Converting an Oracle Function

VolkerBarth — Wed, 18 Apr 2012 15:38:10 GMT

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.

Re: Converting an Oracle Function

Former Member — Thu, 19 Apr 2012 06:17:52 GMT

Regarding the return of a cursor, the last query executed will be returned automatically without defining a cursor. Example:

CREATE OR REPLACE PROCEDURE get_name_address_list  
   (IN p_name_and_address_id      varchar)
BEGIN  
   SELECT c.name_and_address_id,           
          c.full_name,           
          c.parent_record_id,           
          LEVEL pass    
     FROM s1_name_and_address c
END

Re: Converting an Oracle Function

Former Member — Thu, 19 Apr 2012 06:43:54 GMT

See the example below, I hope it will help. Note that Oracle also supports recursive subquery factoring starting from version 11R2. As far as I know, there is no direct equivalent of NOCYCLE clause in SQL Anywhere. The number of recursions is limited by max_recursive_iterations option. Contrary to Oracle, you don't need a refcursor to return a result set from a stored procedure, it occurs automatically. If you return a result set from a procedure then you can query it, i.e. put it into the FROM clause in a query, much like a regular table.

create table s1_name_and_address
(
    name_and_address_id int,
    full_name varchar(50),
    parent_record_id int null
);

insert into s1_name_and_address values (1, 'test_01', null);
insert into s1_name_and_address values (2, 'test_02', null);
insert into s1_name_and_address values (3, 'test_03', 2);
insert into s1_name_and_address values (4, 'test_04', 2);
insert into s1_name_and_address values (5, 'test_05', 3);
insert into s1_name_and_address values (6, 'test_06', 2);
insert into s1_name_and_address values (7, 'test_07', 1);
insert into s1_name_and_address values (8, 'test_08', null);

commit;

select * from s1_name_and_address

with recursive name_and_address
(
    name_and_address_id,
    full_name,
    nest_level
)
as
(
    select 
        name_and_address_id,
        full_name,
        0 nest_level
    from s1_name_and_address
    where parent_record_id is null
    union all
    select
        a.name_and_address_id,
        a.full_name,
        b.nest_level + 1
    from 
        s1_name_and_address a
        inner join name_and_address b
            on  a.parent_record_id = b.name_and_address_id
            and a.parent_record_id <> a.name_and_address_id
            and b.nest_level <= 20
)
select 
    name_and_address_id,
    full_name,
    nest_level
from name_and_address
order by name_and_address_id;