How to perform SQL cursor transformation

Question

In my Oracle stored procedure, there is a select-based query, not so complex just a few joined tables with additional plain filters and single ordering by.

The query contains a users (ordered by name) with a few attributes and possible few rows per user.

However I need to add a few calculated extra columns.

1. set 1 to 'flags' column in first row in group with equal 'name' (other rows are 0).

2. cumulative column "rights' which does join the same field 'rights' previous row value and other column and current row value ('group' in my case) and also should be re-set per user

e.g.:

john;        admin;         1;            admin
john;        poweruser;     0;            admin | poweruser
ken;         guest;         1;            guest
ted;         developer;     1;            developer
ted;         user;           0;           developer | user
ted;         techwriter;     0;           developer | user | techwriter

I need to perform some formatting and return as a result from stored procedure. not sure how to set required values and return this from stored procedure.

Should I declare initial query as a cursor, loop through this and set (is possible at all? For non-database fields?) and how to return this cursor from the stored procedure?

Answer 1

I know you've already got an answer that works for you. I'll add this for posterity.

The SQL MODEL clause would work well for this. Here is an example using the DBA_ROLE_PRIVS table, which exists in every Oracle database and has a similar structure and concept to the data in your post. Obviously, you'd replace DBA_ROLE_PRIVS with your table name.

select grantee,
       granted_role,
       DECODE(rn,1,1,0) flag,
       role_list
FROM dba_role_privs
MODEL 
  PARTITION BY (grantee)
  DIMENSION BY (ROW_NUMBER() OVER ( PARTITION BY grantee ORDER BY granted_role) AS rn)
  MEASURES (CAST(NULL AS VARCHAR2(4000)) as role_list, granted_role)
  RULES UPSERT
    ( role_list[1] = granted_role[1],
      role_list[rn>1] = role_list[cv(rn)-1] || ',' || granted_role[cv(rn)]);

I won't post sample results from my database (for security reasons), but I think they match what you are after.

Answer 2

Unfortunately the analytic version of listagg() doesn't allow a window clause, which would have made this fairly straightforward.

You can sort of mimic this with recursive subquery factoring, but as you have no real ordering criteria (beyond the name) you need to add something to take its place, such as a row number or ranking function; which can be in a CTE too:

with cte (name, right, rn) as (
  select name,
    right,
    row_number() over (partition by name order by null)
  from your_data
),
rcte (name, right, rn, flag, rights) as (
  select name, right, rn, 1, right
  from cte
  where rn = 1
  union all
  select c.name, c.right, c.rn, 0, r.rights || ' | ' || c.right
  from rcte r
  join cte c on c.name = r.name and c.rn = r.rn + 1
)
select name, right, flag, rights
from rcte
order by name, rn;

NAME RIGHT            FLAG RIGHTS                        
---- ---------- ---------- ------------------------------
john admin               1 admin                         
john poweruser           0 admin | poweruser             
ken  guest               1 guest                         
ted  developer           1 developer                     
ted  user                0 developer | user              
ted  techwriter          0 developer | user | techwriter 

Here your_data is whatever your query is doing now; the whole thing can probably go inside the CTE with the rn calculation just tacked on the end, but without seeing your existing query it isn't entirely clear. Hopefully that will be adaptable to your data.

My rn calculation is ordering by null, which is not deterministic - it gives you a fixed value within one execution of the query, but if run again you might get a different value. As the order of the rights doesn't seem to matter you might just as well order by those anyway, to give a deterministic result; which may or may not change the output (since the above is non-deterministic, it might happen to match this anyway; sometimes...):

cte (name, right, rn) as (
  select name,
    right,
    row_number() over (partition by name order by right)
  from your_data
),
rcte (name, right, rn, rights) as (
  select name, right, rn, right
  from cte
  where rn = 1
  union all
  select c.name, c.right, c.rn, r.rights || ' | ' || c.right
  from rcte r
  join cte c on c.name = r.name and c.rn = r.rn + 1
)
select name, right, case when rn = 1 then 1 else 0 end as flag, rights
from rcte
order by name, rn;

NAME RIGHT            FLAG RIGHTS                        
---- ---------- ---------- ------------------------------
john admin               1 admin                         
john poweruser           0 admin | poweruser             
ken  guest               1 guest                         
ted  developer           1 developer                     
ted  techwriter          0 developer | techwriter        
ted  user                0 developer | techwriter | user 

You may actually have some other criteria you can use, like a flag or sequence in one of the tables you're joining; or you can apply your own based on the right values via a case expression.

Answer 3

You can also use some other analytic functions together with LISTAGG() to do the trick. I'll demonstrate with the EMP table since you don't provide test data:

select deptno, ename,
  case rn when 1 then 1 else 0 end flags,
  case cnt when rn
    then enames
    else substr(enames, 1, instr(enames, ' | ', 1, rn) - 1)
  end enames
from (
  select deptno, ename,
    row_number() over(partition by deptno order by ename) rn,
    count(*) over(partition by deptno) cnt,
    listagg(ename, ' | ') within group(order by ename) over(partition by deptno) enames
  from emp
) a;

DEPTNO   ENAME    FLAGS  ENAMES                                           
    10   CLARK        1  CLARK                                             
    10   KING         0  CLARK | KING                                      
    10   MILLER       0  CLARK | KING | MILLER                             
    20   ADAMS        1  ADAMS                                             
    20   FORD         0  ADAMS | FORD                                      
    20   JONES        0  ADAMS | FORD | JONES                              
    20   SCOTT        0  ADAMS | FORD | JONES | SCOTT                      
    20   SMITH        0  ADAMS | FORD | JONES | SCOTT | SMITH              
    30   ALLEN        1  ALLEN                                             
    30   BLAKE        0  ALLEN | BLAKE                                     
    30   JAMES        0  ALLEN | BLAKE | JAMES                             
    30   MARTIN       0  ALLEN | BLAKE | JAMES | MARTIN                    
    30   TURNER       0  ALLEN | BLAKE | JAMES | MARTIN | TURNER           
    30   WARD         0  ALLEN | BLAKE | JAMES | MARTIN | TURNER | WARD

Best regards, Stew Ashton

Answer 4

Here is one possibility how to transform a cursor returned by a stored procedure.

You have to do three steps:

1) define a row and table TYPEs for the original cursor

2) define a table function returning the original cursor

3) define new procedure that opens a new cursor while querying the table function (point 2) and joining it with other sources

Example

original procedure

create procedure P1(cur OUT SYS_REFCURSOR) IS
begin
  open cur for
  select id, col from V1;
end;
/

1) define types

create or replace type t_row is object
(id int,
 col VARCHAR2(5));
/

create or replace type t_table is table of t_row;
/

2) define table function returning the original cursor

create or replace  function F1 return t_table PIPELINED as
  cv sys_refcursor;
  v_row  v1%rowtype; 
begin
  P1(cv);
  loop
    FETCH cv  INTO v_row;
    EXIT WHEN cv%NOTFOUND;
    PIPE ROW(t_row(v_row.id, v_row.col));
  end loop;
  close cv;
  return;
end;
/

Note that now you can access the original cursor with SQL:

select * from table(F1);

        ID COL  
---------- -----
         1 A    
         2 B

3) define new procedure performing the transformation

Note that I'm neglecting your details only simulating the transformation with adding of a new dummy column

create procedure P2(cur OUT SYS_REFCURSOR) IS
begin
  open cur for
  select id, col, 'new' col2
  from table(F1);
end;
/