-1

I have a function that returns a cursor. When I execute

select t.id, t.f_rc(id) from my_table t

I get

id F_RC(TID)
1 < cursor >
2 < cursor >
3 < cursor >
...

How to expand values to get content of cursor? Would cursor be an array I would just do

select t.id, t.f_rc(id)[1], t.f_rc(id)[2], t.f_rc(id)[3], t.f_rc(id)[4] from my_table t    

But this doesn't work with PL SQL.

create or replace function f_rc (name_in in number)
    return sys_refcursor
  is
    l_rc sys_refcursor;
  begin
    open l_rc for
    WITH
       dummy_links as (
             SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
             SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
             SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
             SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
             SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
             SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
             SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
             SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
             SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
             SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
       res AS (SELECT ID,
              in_node,
              out_node,
              weight,
              MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
              MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
              MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
              CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                 ELSE 'out'
              END direction_from_orig_node,
              LEAST(in_node, out_node) node1,
              GREATEST(in_node, out_node) node2,
              row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   dummy_links
           START WITH ID = name_in
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                    OR PRIOR in_node IN (in_node, out_node))
                    AND LEVEL <= 2)
    SELECT orig_id,
         COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
         nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
         COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
         nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
    FROM   res
    WHERE  rn = 1
    AND    ID != orig_id
    GROUP BY orig_id;

    return l_rc;
  end f_rc;


  with        dummy_t as (
             SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
             SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
             SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
             SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
             SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
             SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
             SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
             SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
             SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
             SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual)


select dl.id, f_rc(dl.id) from dummy_t dl ;

This works as expected:

 -- ..dummy_t definition s above..
 select  dt.id as d2id, ct.my_dlid, ct.my_cur from 
         (select cur_t.id as my_dlid, f_rc(cur_t.id) as my_cur from dummy_t cur_t ) ct,
         dummy_t dt
         --where ct.my_dlid = dt.id --this works returning 1 1 < cursor> 
         where ct.my_cur[1] = dt.id --this fails

What is the correct way to access values inside of a cursor? in a query? Or should I just save values to a temporary table?

0

It looks OK (as far as ref cursor is concerned) to me - if you run it in SQL*Plus.

<snip>
 11               SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual)
 12  select dl.id, f_rc(dl.id) from dummy_t dl ;

        ID F_RC(DL.ID)
---------- --------------------
         1 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         1          4         35          0          0

         2 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         2          0          0          3         31

         3 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         3          1         33          3         30

Which tool do you use? If it is some GUI (such as TOAD or SQL Developer), you'll have to find a way to display data in a readable manner.

For example - TOAD: as far as I can tell, you should rewrite a query you posted into a procedure:

create or replace procedure p_rc (par_rc out sys_refcursor) as
begin  
  open par_rc for select f_rc(dl.id) 
  from 
    (        SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
             SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
             SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
             SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
             SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
             SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
             SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
             SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
             SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
             SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual
    ) dl;
end;
/

Once it is created,

  • find it in Object Browser,
  • right-click its name,
  • choose "Execute procedure" from the pop-up menu
  • click the "Reset current procedure parameters to default values" (looks like a "Refresh" button)
  • click OK

The result will be displayed in a new, "REF CURSOR Results" window and look like this:

1, 4, 35, 0, 0
2, 0, 0, 3, 31    
3, 1, 33, 3, 30
<snip>
| improve this answer | |
  • I am using PL/SQL Developer OCI: version 11.1 Oracle DB 12c Enterprise. I run create or replace procedure/function in a new query window. I am looking for a return type that I can join or query against. select t.id, f1(t.id) as my_var, f2(t.value) as my_val from my_table t should return a table that I can further slice and dice as if it was select my_id, my_var, my_val from my_t – Stepan Nov 14 '18 at 12:28
  • I've updated the question. Briefly, given a table select my_id, my_cur from cur_t such that my_cur is a < cursor > I want to do select my_id, my_cur from cur_t where my_cur.id = 42. Is this possible? – Stepan Nov 14 '18 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.