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  |
 |
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 #658510 is a reply to message #658503] |
Fri, 16 December 2016 00:12  |
 |
Barbara Boehmer
Messages: 9088
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 |
|
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 #658540 is a reply to message #658538] |
Sat, 17 December 2016 18:21 |
|
Barbara Boehmer
Messages: 9088
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 |
|
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 |
Solomon Yakobson
Messages: 3273
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 #658603 is a reply to message #658597] |
Tue, 20 December 2016 22:24 |
|
Barbara Boehmer
Messages: 9088
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 |
Solomon Yakobson
Messages: 3273
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.
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 08:19:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
