Home » SQL & PL/SQL » SQL & PL/SQL » Convert a sys refcursor into table (11g R2)
Convert a sys refcursor into table [message #658503] Thu, 15 December 2016 21:43 Go to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Hi Team,

I have a requirement where I need to compare output from original and modified sys refcursor. So, I came up with the below steps:
1. Take the original refcursor, open it using dbms_sql
2. Convert this into a GTT
3. Do the same for modified output
4. Compare the tables by A-B union B-A

The question here is, how can I convert a sys refcursor into a GTT.
All sources i looked were giving samples to bulk collect sys refcursor into a plsql table type and then insert them into GTT. Is there any other way to achieve this.?

Thanks in advance.
Re: Convert a sys refcursor into table [message #658504 is a reply to message #658503] Thu, 15 December 2016 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

How do you ensure that both refcursor have the same number of columns, the same datatypes in the same order?
What should be done when there are different number of columns or different data types?

Re: Convert a sys refcursor into table [message #658505 is a reply to message #658504] Thu, 15 December 2016 22:52 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Fetch order is not being modified. Only the below levels were tuned to improve the performance. So assuming the fetched columns , column names n data types will still remain the same.
Re: Convert a sys refcursor into table [message #658506 is a reply to message #658505] Thu, 15 December 2016 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Sravz wrote on Thu, 15 December 2016 20:52
Fetch order is not being modified. Only the below levels were tuned to improve the performance. So assuming the fetched columns , column names n data types will still remain the same.
Then you are wasting everyone's time since by your admission there will be NO difference in result set content!
Re: Convert a sys refcursor into table [message #658507 is a reply to message #658506] Thu, 15 December 2016 23:04 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Data would change but not the order. To ensure the data consistency between the original and modified, this activity was planned.
Re: Convert a sys refcursor into table [message #658508 is a reply to message #658507] Thu, 15 December 2016 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Sravz wrote on Thu, 15 December 2016 21:04
Data would change but not the order. To ensure the data consistency between the original and modified, this activity was planned.
Post SQL & results that show above is correct.

How can we reproduce what you claim?
Re: Convert a sys refcursor into table [message #658510 is a reply to message #658503] Fri, 16 December 2016 00:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8897
Registered: November 2002
Location: California, USA
Senior Member
Instead of using a global temporary table, you could use a pipelined table function, then compare the output as your previously described, as demonstrated below. You will need to substitute your actual columns and data types in the declaration of refcur_typ.

-- packaged pipeline table funtion:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE refcur_to_tab_pkg AS
  2    TYPE refcur_typ IS RECORD
  3  	 (deptno      NUMBER(2),
  4  	  dname       VARCHAR2(14),
  5  	  loc	      VARCHAR2(13));
  6    TYPE refcur_tab IS TABLE OF refcur_typ;
  7    FUNCTION refcur_to_tab_func (p_refcur IN SYS_REFCURSOR) RETURN refcur_tab PIPELINED;
  8  END refcur_to_tab_pkg;
  9  /

Package created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY refcur_to_tab_pkg AS
  2    FUNCTION refcur_to_tab_func (p_refcur SYS_REFCURSOR) RETURN refcur_tab PIPELINED IS
  3  	 v_rec refcur_typ;
  4    BEGIN
  5  	 LOOP
  6  	   FETCH p_refcur INTO v_rec;
  7  	   EXIT WHEN p_refcur%NOTFOUND;
  8  	   PIPE ROW (v_rec);
  9  	 END LOOP;
 10  	 CLOSE p_refcur;
 11    END refcur_to_tab_func;
 12  END refcur_to_tab_pkg;
 13  /

Package body created.

-- ref cursors:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_original REFCURSOR
SCOTT@orcl_12.1.0.2.0> BEGIN OPEN :g_original FOR SELECT * FROM dept WHERE deptno IN (10, 20); END;
  2  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> VARIABLE g_modified REFCURSOR
SCOTT@orcl_12.1.0.2.0> BEGIN OPEN :g_modified FOR SELECT * FROM dept WHERE deptno IN (20, 30); END;
  2  /

PL/SQL procedure successfully completed.

-- comparison of ref cursors using packaged pipelined table function:
SCOTT@orcl_12.1.0.2.0> WITH
  2    original AS (SELECT * FROM TABLE (refcur_to_tab_pkg.refcur_to_tab_func (:g_original))),
  3    modified AS (SELECT * FROM TABLE (refcur_to_tab_pkg.refcur_to_tab_func (:g_modified)))
  4  (SELECT * FROM original MINUS SELECT * FROM modified) UNION (SELECT * FROM modified MINUS SELECT * FROM original)
  5  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO

2 rows selected.

[Updated on: Fri, 16 December 2016 00:15]

Report message to a moderator

Re: Convert a sys refcursor into table [message #658538 is a reply to message #658510] Sat, 17 December 2016 09:17 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Hi Barbara,

Thanks so much for the approach. Yes that works fine as long as you know the output. Looking to create something generic. I tried the below code. I am unable to use this if my refcursor output has plsql table in it.

Can you help me in this:

select d.dname,
	   d.loc,
	   CAST (MULTISET(select e.ename from emp e where e.deptno = d.deptno) as t_string_array) as employees
from dept d
where d.deptno = 40;

/*
Package Body - pk_sravz_compare_results
*/
CREATE OR REPLACE PACKAGE pk_sravz_compare_results
AS

FUNCTION fn_get_dept_details(p_in_dept_no IN dept.deptno%TYPE)
RETURN SYS_REFCURSOR;

END pk_sravz_compare_results;


/*
Package Body - pk_sravz_compare_results
*/
CREATE OR REPLACE PACKAGE BODY pk_sravz_compare_results
AS

FUNCTION fn_get_dept_details(p_in_dept_no IN dept.deptno%TYPE)
RETURN SYS_REFCURSOR
IS

l_rc	SYS_REFCURSOR;

BEGIN

OPEN l_rc FOR
select d.dname,
	   d.loc/*,
	   CAST (MULTISET(select e.ename from emp e where e.deptno = d.deptno) as t_string_array) as employees*/
from dept d
where d.deptno = p_in_dept_no;

RETURN l_rc;

EXCEPTION
WHEN OTHERS THEN
	dbms_output.put_line('Error n Func - fn_get_dept_details');
END fn_get_dept_details;

END pk_sravz_compare_results;


--Block to compare the SYS_REFCURSOR
declare
  cx_1              sys_refcursor;
  c                 NUMBER;
  desctab           DBMS_SQL.DESC_TAB;
  colcnt            NUMBER;
  stringvar         VARCHAR2(4000);
  numvar            NUMBER;
  datevar           DATE;
  banker			DBMS_SQL.NUMBER_TABLE;
  concat_col_vals   varchar2(4000);
  col_hash          number;
  h                 raw(32767);
  n                 number;

BEGIN
  cx_1 := pk_sravz_compare_results.fn_get_dept_details(40);

  c := DBMS_SQL.TO_CURSOR_NUMBER(cx_1);
  DBMS_SQL.DESCRIBE_COLUMNS(c, colcnt, desctab);

  -- Define columns:
  FOR i IN 1 .. colcnt LOOP
    IF desctab(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(c, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(c, i, datevar);
	  ELSIF desctab(i).col_type = 122 THEN
      DBMS_SQL.DEFINE_ARRAY(c, i, banker,1,10);
      -- statements
    ELSE
      DBMS_SQL.DEFINE_COLUMN(c, i, stringvar, 4000);
    END IF;
  END LOOP;

  -- Fetch rows with DBMS_SQL package:
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
  concat_col_vals := '~';
    FOR i IN 1 .. colcnt LOOP
      IF (desctab(i).col_type = 1) THEN
        DBMS_SQL.COLUMN_VALUE(c, i, stringvar);
        concat_col_vals := concat_col_vals || '~' || stringvar;

      ELSIF (desctab(i).col_type = 2) THEN
        DBMS_SQL.COLUMN_VALUE(c, i, numvar);
        concat_col_vals := concat_col_vals || '~' || to_char(numvar);

      ELSIF (desctab(i).col_type = 12) THEN
        DBMS_SQL.COLUMN_VALUE(c, i, datevar);
        concat_col_vals := concat_col_vals || '~' || to_char(datevar);

      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(concat_col_vals);
    col_hash :=  DBMS_UTILITY.GET_SQL_HASH(concat_col_vals, h, n);
    DBMS_OUTPUT.PUT_LINE('Return Value: ' || TO_CHAR(col_hash));
    DBMS_OUTPUT.PUT_LINE('Hash: ' || h);
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(c);

END;

[Updated on: Sat, 17 December 2016 09:20]

Report message to a moderator

Re: Convert a sys refcursor into table [message #658539 is a reply to message #658538] Sat, 17 December 2016 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/WHEN_OTHERS

the WHEN OTHERS exception handler should be removed completely & NEVER used anywhere ever again
Re: Convert a sys refcursor into table [message #658540 is a reply to message #658538] Sat, 17 December 2016 18:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8897
Registered: November 2002
Location: California, USA
Senior Member
You did not provide the definition of your type t_string_array, so I have provided one below. In your pl/sql block, you will need to declare a variable for that type. You will also need to figure out the numeric value for that column type on your system, then define the column using the declared variable. You cannot concatenate a type, so you will need to loop through the values and concatenate them. Please see the demonstration below, noting the comments along the left margin. You will need to do the same for any other user-defined types that may be used in your sys_refcursors.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE t_string_array as TABLE of VARCHAR2 (2000);
  2  /

Type created.

SCOTT@orcl_12.1.0.2.0> SELECT d.dname,
  2  	    d.loc,
  3  	    CAST (MULTISET(SELECT e.ename FROM emp e WHERE e.deptno = d.deptno ORDER BY e.ename) AS t_string_array) AS employees
  4  FROM   dept d
  5  WHERE  d.deptno <= 40
  6  /

DNAME          LOC           EMPLOYEES
-------------- ------------- ----------------------------------------------------------------------
ACCOUNTING     NEW YORK      T_STRING_ARRAY('CLARK', 'KING', 'MILLER')
RESEARCH       DALLAS        T_STRING_ARRAY('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
SALES          CHICAGO       T_STRING_ARRAY('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')
OPERATIONS     BOSTON        T_STRING_ARRAY()

4 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE pk_sravz_compare_results
  2  AS
  3    FUNCTION fn_get_dept_details (p_in_dept_no IN dept.deptno%TYPE)
  4    RETURN SYS_REFCURSOR;
  5  END pk_sravz_compare_results;
  6  /

Package created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY pk_sravz_compare_results
  2  AS
  3    FUNCTION fn_get_dept_details (p_in_dept_no IN dept.deptno%TYPE)
  4    RETURN SYS_REFCURSOR
  5    IS
  6  	 l_rc	     SYS_REFCURSOR;
  7    BEGIN
  8  	 OPEN l_rc FOR
  9  	   SELECT d.dname,
 10  		  d.loc,
 11  		  CAST (MULTISET(SELECT e.ename FROM emp e WHERE e.deptno = d.deptno ORDER BY e.ename) AS t_string_array) AS employees
 12  	   FROM   dept d
 13  	   WHERE  d.deptno <= p_in_dept_no;
 14  	 RETURN l_rc;
 15    END fn_get_dept_details;
 16  END pk_sravz_compare_results;
 17  /

Package body created.

SCOTT@orcl_12.1.0.2.0> DECLARE
  2    cx_1		 SYS_REFCURSOR;
  3    c		 NUMBER;
  4    colcnt		 NUMBER;
  5    desctab		 DBMS_SQL.DESC_TAB;
  6    numvar		 NUMBER;
  7    datevar		 DATE;
  8  -- declare variable of type t_string_array:
  9    enames		 t_string_array;
 10    stringvar	 VARCHAR2(4000);
 11    concat_col_vals	 VARCHAR2(4000);
 12    col_hash 	 NUMBER;
 13    h		 RAW(32767);
 14    n		 NUMBER;
 15  BEGIN
 16    cx_1 := pk_sravz_compare_results.fn_get_dept_details(40);
 17    c := DBMS_SQL.TO_CURSOR_NUMBER(cx_1);
 18    DBMS_SQL.DESCRIBE_COLUMNS(c, colcnt, desctab);
 19    FOR i IN 1 .. colcnt LOOP
 20  	 IF desctab(i).col_type = 1 THEN
 21  	   DBMS_SQL.DEFINE_COLUMN(c, i, stringvar, 4000);
 22  	 ELSIF desctab(i).col_type = 2 THEN
 23  	   DBMS_SQL.DEFINE_COLUMN(c, i, numvar);
 24  	 ELSIF desctab(i).col_type = 12 THEN
 25  	   DBMS_SQL.DEFINE_COLUMN(c, i, datevar);
 26  -- determine numeric value (109 here) of col type for t_string_array:
 27  	 ELSIF desctab(i).col_type = 109 THEN
 28  -- define column using variable declared for type t_string_array:
 29  	   DBMS_SQL.DEFINE_COLUMN(c, i, enames);
 30  	 ELSE
 31  	   DBMS_SQL.DEFINE_COLUMN(c, i, stringvar, 4000);
 32  	 END IF;
 33    END LOOP;
 34    WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
 35  	 concat_col_vals := concat_col_vals || '~';
 36  	 FOR i IN 1 .. colcnt LOOP
 37  	   IF (desctab(i).col_type = 1) THEN
 38  	     DBMS_SQL.COLUMN_VALUE(c, i, stringvar);
 39  	     concat_col_vals := concat_col_vals || '~' || stringvar;
 40  	   ELSIF (desctab(i).col_type = 2) THEN
 41  	     DBMS_SQL.COLUMN_VALUE(c, i, numvar);
 42  	     concat_col_vals := concat_col_vals || '~' || to_char(numvar);
 43  	   ELSIF (desctab(i).col_type = 12) THEN
 44  	     DBMS_SQL.COLUMN_VALUE(c, i, datevar);
 45  	     concat_col_vals := concat_col_vals || '~' || to_char(datevar);
 46  -- handle concatenation of type t_string_array:
 47  	   ELSIF (desctab(i).col_type = 109) THEN
 48  	     DBMS_SQL.COLUMN_VALUE(c, i, enames);
 49  	     concat_col_vals := concat_col_vals || '~T_STRING_ARRAY(';
 50  	     FOR j IN 1 .. enames.COUNT LOOP
 51  	       concat_col_vals := concat_col_vals || enames(j) || ',';
 52  	     END LOOP;
 53  	     concat_col_vals := RTRIM (concat_col_vals, ',') || ')';
 54  	   END IF;
 55  	 END LOOP;
 56    END LOOP;
 57    DBMS_SQL.CLOSE_CURSOR(c);
 58    DBMS_OUTPUT.PUT_LINE(concat_col_vals);
 59    col_hash :=  DBMS_UTILITY.GET_SQL_HASH(concat_col_vals, h, n);
 60    DBMS_OUTPUT.PUT_LINE('Return Value: ' || TO_CHAR(col_hash));
 61    DBMS_OUTPUT.PUT_LINE('Hash: ' || h);
 62  END;
 63  /
~~ACCOUNTING~NEW
YORK~T_STRING_ARRAY(CLARK,KING,MILLER)~~RESEARCH~DALLAS~T_STRING_ARRAY(ADAMS,FORD,JONES,SCOTT,SMITH)
~~SALES~CHICAGO~T_STRING_ARRAY(ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD)~~OPERATIONS~BOSTON~T_STRING_ARR
AY()
Return Value: 1709910868
Hash: 0C18A9835F082C4F293EFB5F542BEB65

PL/SQL procedure successfully completed.

[Updated on: Sat, 17 December 2016 18:34]

Report message to a moderator

Re: Convert a sys refcursor into table [message #658587 is a reply to message #658540] Tue, 20 December 2016 12:32 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Hi Barbara,

Thanks for the approach. Now that I am more close to my solution, how can I handle the comparison of below structure using the approach in discussion:

CREATE TYPE t_obj_emp_details AS OBJECT(
ename	VARCHAR2(10),
job		VARCHAR2(9),
gender	char(1),
hiredate	DATE,
emp_address 	t_obj_emp_address
);

CREATE OR REPLACE TYPE t_tab_employees IS TABLE OF t_obj_emp_details;

CREATE TYPE t_obj_dept_details AS OBJECT(
deptno	NUMBER(2),
dname	VARCHAR2(14),
loc		VARCHAR2(13),
employees	t_tab_employees
);

I am trying to understand the way I need to approach, for such a structure. Also, please see that we don't have MAP functions for our existing objects.
And sry for the delay in responding. Thanks in advance.

[Updated on: Tue, 20 December 2016 12:50]

Report message to a moderator

Re: Convert a sys refcursor into table [message #658589 is a reply to message #658587] Tue, 20 December 2016 13:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
I thought you would understand by now creating generic comparison is not possible with this approach since you have to create object type thus you must know the structure upfront. The only way to do this is dynamic SQL type 4 using pure DBMS_SQL which is more complex and less readable. So better revisit your design. Requirement like your's in 95% of cases indicates flawed design. What business problem are you trying to resolve?

SY.

[Updated on: Tue, 20 December 2016 13:20]

Report message to a moderator

Re: Convert a sys refcursor into table [message #658594 is a reply to message #658587] Tue, 20 December 2016 18:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8897
Registered: November 2002
Location: California, USA
Senior Member
You have not provided the select statement that is used in the cursor that uses the types.

In general, you would need to un-nest and concatenate the values.

The more such types that you have and the more column aliases there may be, the bigger the project becomes and it eventually becomes an unworkable mess.

It might help if you explain what the problem is that you are trying to use the cursor comparison to solve. Why do you believe you need to compare two cursors? You have mentioned before and after modification. What is it that you are modifying? What is the actual underlying problem? There may be an easier way that does not even involve cursor comparison.



Re: Convert a sys refcursor into table [message #658595 is a reply to message #658594] Tue, 20 December 2016 19:32 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>Now that I am more close to my solution
please quantify the distance between you & your solution.
What is unit of measure for this distance?
If you are close to your solution but not moving; then you will never get there from here.
It appears to me that you are dead in the water, frozen in place, & waiting for others to get you moving.
Why did you pick a "solution" that you are incapable to implement?
Re: Convert a sys refcursor into table [message #658596 is a reply to message #658595] Tue, 20 December 2016 19:49 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Hi Black swan,

I would appreciate your commitment in constant replying without any solution discussed.
Re: Convert a sys refcursor into table [message #658597 is a reply to message #658594] Tue, 20 December 2016 19:56 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Hi Barbara,

We have a requirement of enhancing the legacy project. Enhancing functionally and in terms of performance.
In database end, we need to tune the underlying packages involved with no/minimal impact on other layers (meaning no output changed). This process should also ensure that results still remain the same and most of the outputs will be refcursors. So to unit test them manually would be or have been a tedious task. That is where it all started.
Kindly suggest if there is a better approach.
Re: Convert a sys refcursor into table [message #658598 is a reply to message #658589] Tue, 20 December 2016 20:00 Go to previous messageGo to next message
Sravz
Messages: 8
Registered: December 2016
Junior Member
Hi Solomon,

I understood the limitations i have and that's where even the approach was modified as I can't compare objects using GTT approach unless they are mapped.
Kindly suggest if there is any solution to this.
Re: Convert a sys refcursor into table [message #658603 is a reply to message #658597] Tue, 20 December 2016 22:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8897
Registered: November 2002
Location: California, USA
Senior Member
It seems like the following should work, but it doesn't, and I can't figure out why. Instead of applying the MINUS, it seems to just return the first cursor.

SCOTT@orcl_12.1.0.2.0> SELECT pk_sravz_compare_results.fn_get_dept_details (30) FROM DUAL
  2  /

PK_SRAVZ_COMPARE_RES
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          LOC           EMPLOYEES
-------------- ------------- ----------------------------------------------------------------------
ACCOUNTING     NEW YORK      T_STRING_ARRAY('CLARK', 'KING', 'MILLER')
RESEARCH       DALLAS        T_STRING_ARRAY('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
SALES          CHICAGO       T_STRING_ARRAY('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')

3 rows selected.


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pk_sravz_compare_results.fn_get_dept_details (20) FROM DUAL
  2  /

PK_SRAVZ_COMPARE_RES
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          LOC           EMPLOYEES
-------------- ------------- ----------------------------------------------------------------------
ACCOUNTING     NEW YORK      T_STRING_ARRAY('CLARK', 'KING', 'MILLER')
RESEARCH       DALLAS        T_STRING_ARRAY('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')

2 rows selected.


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pk_sravz_compare_results.fn_get_dept_details (30) FROM DUAL
  2  MINUS
  3  SELECT pk_sravz_compare_results.fn_get_dept_details (20) FROM DUAL
  4  /

PK_SRAVZ_COMPARE_RES
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          LOC           EMPLOYEES
-------------- ------------- ----------------------------------------------------------------------
ACCOUNTING     NEW YORK      T_STRING_ARRAY('CLARK', 'KING', 'MILLER')
RESEARCH       DALLAS        T_STRING_ARRAY('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
SALES          CHICAGO       T_STRING_ARRAY('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')

3 rows selected.


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pk_sravz_compare_results.fn_get_dept_details (20) FROM DUAL
  2  MINUS
  3  SELECT pk_sravz_compare_results.fn_get_dept_details (30) FROM DUAL
  4  /

PK_SRAVZ_COMPARE_RES
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          LOC           EMPLOYEES
-------------- ------------- ----------------------------------------------------------------------
ACCOUNTING     NEW YORK      T_STRING_ARRAY('CLARK', 'KING', 'MILLER')
RESEARCH       DALLAS        T_STRING_ARRAY('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')

2 rows selected.


1 row selected.

SCOTT@orcl_12.1.0.2.0>
Re: Convert a sys refcursor into table [message #658608 is a reply to message #658603] Wed, 21 December 2016 08:40 Go to previous message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara, I'm surprised it's not raising exception - that's the real bug. It should burp with:

SQL> select cursor(select ename from emp where deptno = 10) from dual
  2  minus
  3  select cursor(select ename from emp where deptno = 20) from dual
  4  /
select cursor(select ename from emp where deptno = 20) from dual
       *
ERROR at line 3:
ORA-22902: CURSOR expression not allowed


SQL> 

SY.

Previous Topic: sorting of an alphanumeric value through oracle pl/sql
Next Topic: transforming rows to columns
Goto Forum:
  


Current Time: Mon Oct 22 02:25:19 CDT 2018