Home » SQL & PL/SQL » SQL & PL/SQL » Create table by using REF Cursor Results (Oracle 11i )
| Create table by using REF Cursor Results [message #525727] |
Tue, 04 October 2011 22:34  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi All ,
I want to create table by using the ref cursor results which is returned by the function / procedure.
We need achieve this simple query .
Thanks in advance
Sai Pradyumn
|
|
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525732 is a reply to message #525728] |
Tue, 04 October 2011 23:55  |
|
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi
Sorry for the in inconvenience
For example I have function like
CREATE OR REPLACE FUNCTION GETEMPLIST(DNO IN NUMBER)
RETURN SYS_REFCURSOR
IS
RETURN_VALUE SYS_REFCURSOR;
BEGIN
OPEN RETURN_VALUE FOR
SELECT* FROM SCOTT.EMP S
WHERE S.DEPTNO = DNO;
RETURN RETURN_VALUE;
END;
To Execute the function i have defined one REF Cursor variable
BEGIN
-- CALL THE FUNCTION
:RESULT := GETEMPLIST(DNO => 10);
END;
Now i want to create a table with ref cursor variable ("RESULT" ) information .
This can be done my executing the following query .
CREATE TABLE TEST_EMP AS SELECT * FROM EMP
WHERE DEPTNO = 10 ;
But in my function i have some calculations which are not possible by using simple query.
Thanks in Advance
Sai pradyumn
[Updated on: Wed, 05 October 2011 01:26] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525822 is a reply to message #525743] |
Wed, 05 October 2011 14:35 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I suspect that if you provide your business requirement there will be a simpler way to accomplish what you want. However, I have provided a demonstration below that I believe does what you asked for, assuming that this might be a simplification of a greater problem. I gather that you have a function that returns the rows that you want and you want to be able to use the ref cursor value returned from that function in a create table statement to create a table with that data as you would with a standard create table ... as select ... (ctas). I have created a packaged pipelined table function that converts the ref cursor to pipelined rows that you can use in this manner. The demonstration assumes that you have the standard scott.emp demo table.
-- the function you provided:
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION GETEMPLIST
2 (DNO IN NUMBER)
3 RETURN SYS_REFCURSOR
4 IS
5 RETURN_VALUE SYS_REFCURSOR;
6 BEGIN
7 OPEN RETURN_VALUE FOR
8 SELECT * FROM SCOTT.EMP S
9 WHERE S.DEPTNO = DNO;
10 RETURN RETURN_VALUE;
11 END;
12 /
Function created.
-- packaged pipelined table function:
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE refcur_to_table_pkg
2 AS
3 TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;
4 TYPE outrec_typ IS RECORD
5 (EMPNO NUMBER(4),
6 ENAME VARCHAR2(10),
7 JOB VARCHAR2(9),
8 MGR NUMBER(4),
9 HIREDATE DATE,
10 SAL NUMBER(7,2),
11 COMM NUMBER(7,2),
12 DEPTNO NUMBER(2));
13 TYPE outrecset IS TABLE OF outrec_typ;
14 FUNCTION refcur_to_table_func
15 (p refcur_t)
16 RETURN outrecset PIPELINED;
17 END refcur_to_table_pkg;
18 /
Package created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE BODY refcur_to_table_pkg
2 AS
3 FUNCTION refcur_to_table_func
4 (p refcur_t)
5 RETURN outrecset PIPELINED
6 IS
7 out_rec outrec_typ;
8 BEGIN
9 LOOP
10 FETCH p INTO out_rec;
11 EXIT WHEN p%NOTFOUND;
12 PIPE ROW (out_rec);
13 END LOOP;
14 CLOSE p;
15 RETURN;
16 END refcur_to_table_func;
17 END refcur_to_table_pkg;
18 /
Package body created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- usage of above functions to create table:
SCOTT@orcl_11gR2> CREATE TABLE test_emp
2 AS
3 SELECT *
4 FROM TABLE
5 (refcur_to_table_pkg.refcur_to_table_func
6 (getemplist (10)))
7 /
Table created.
-- results:
SCOTT@orcl_11gR2> DESC test_emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@orcl_11gR2> SELECT * FROM test_emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
3 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525828 is a reply to message #525826] |
Thu, 06 October 2011 00:58 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Thanks a lot! Would only suggest to define outrecset consistent as TABLE of emp%rowtype:
TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;
TYPE outrecset IS TABLE OF emp%ROWTYPE;
-- TYPE outrec_typ IS RECORD
-- (EMPNO NUMBER(4),
-- ENAME VARCHAR2(10),
-- JOB VARCHAR2(9),
-- MGR NUMBER(4),
-- HIREDATE DATE,
-- SAL NUMBER(7,2),
-- COMM NUMBER(7,2),
-- DEPTNO NUMBER(2));
-- TYPE outrecset IS TABLE OF outrec_typ;
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525853 is a reply to message #525828] |
Thu, 06 October 2011 04:58 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
_jum wrote on Wed, 05 October 2011 22:58Thanks a lot! Would only suggest to define outrecset consistent as TABLE of emp%rowtype:
TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;
TYPE outrecset IS TABLE OF emp%ROWTYPE;
-- TYPE outrec_typ IS RECORD
-- (EMPNO NUMBER(4),
-- ENAME VARCHAR2(10),
-- JOB VARCHAR2(9),
-- MGR NUMBER(4),
-- HIREDATE DATE,
-- SAL NUMBER(7,2),
-- COMM NUMBER(7,2),
-- DEPTNO NUMBER(2));
-- TYPE outrecset IS TABLE OF outrec_typ;
I don't think so. Not with a pipelined table function. What would you use instead of ourec_typ? What would you fetch into and pipe out? Please provide a demo using what you suggest that compiles and runs, as I did.
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525855 is a reply to message #525853] |
Thu, 06 October 2011 05:25 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Here is the complete demo code:
CREATE OR REPLACE FUNCTION GETEMPLIST (DNO IN NUMBER)
RETURN SYS_REFCURSOR
IS
RETURN_VALUE SYS_REFCURSOR;
BEGIN
OPEN RETURN_VALUE FOR
SELECT * FROM SCOTT.EMP S
WHERE S.DEPTNO = DNO;
RETURN RETURN_VALUE;
END;
/
CREATE OR REPLACE PACKAGE refcur_to_table_pkg
AS
TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;
TYPE outrecset IS TABLE OF emp%ROWTYPE;
out_rec emp%ROWTYPE;
FUNCTION refcur_to_table_func (p refcur_t)
RETURN outrecset PIPELINED;
END refcur_to_table_pkg;
/
CREATE OR REPLACE PACKAGE BODY refcur_to_table_pkg
AS
FUNCTION refcur_to_table_func (p refcur_t)
RETURN outrecset PIPELINED
IS
BEGIN
LOOP
FETCH p INTO out_rec;
EXIT WHEN p%NOTFOUND;
PIPE ROW (out_rec);
END LOOP;
CLOSE p;
RETURN;
END refcur_to_table_func;
END refcur_to_table_pkg;
/
SHOW ERRORS
SELECT *
FROM TABLE(refcur_to_table_pkg.refcur_to_table_func (getemplist (10)));
EMPNO ENAME JOB MGR SAL DEPTNO
---------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 2450 10
7839 KING PRESIDENT 5000 10
7934 MILLER CLERK 7782 1300 10
[Updated on: Thu, 06 October 2011 05:27] Report message to a moderator |
|
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525905 is a reply to message #525872] |
Thu, 06 October 2011 14:10 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara,
Both your _jum's solutions imply refcursor result rowtype is known upfront. In such case XML could be a simpler solution - no need for pipelined function. For example, if refcursor returns emp%rowtype:
select extractvalue(column_value,'ROW/EMPNO') empno ,
extractvalue(column_value,'ROW/ENAME') ename,
extractvalue(column_value,'ROW/JOB') job,
extractvalue(column_value,'ROW/MGR') mgr,
extractvalue(column_value,'ROW/HIREDATE') hiredate,
extractvalue(column_value,'ROW/SAL') sal,
extractvalue(column_value,'ROW/COMM') comm,
extractvalue(column_value,'ROW/DEPTNO') deptno
from table(xmlsequence(getemplist(10)))
/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTN
----- ---------- ---------- ----- ---------- ---------- ---------- -----
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL>
SY.
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525906 is a reply to message #525905] |
Thu, 06 October 2011 14:37 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Actually, this might be better:
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'
/
select *
from xmltable(
'/ROWSET/*'
passing xmltype(getemplist(10))
columns
empno number(4) path '/ROW/EMPNO',
ename varchar2(10) path '/ROW/ENAME',
job varchar2(9) path '/ROW/JOB',
mgr number(4) path '/ROW/MGR',
hiredate date path '/ROW/HIREDATE',
sal number(7,2) path '/ROW/SAL',
comm number(7,2) path '/ROW/COMM',
deptno number(2) path '/ROW/DEPTNO'
)
/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Create table by using REF Cursor Results [message #525998 is a reply to message #525917] |
Fri, 07 October 2011 07:58 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Thu, 06 October 2011 19:07However, I find it odd that it only works with certain nls_date_format's. You would think that since the function returns it in the default session format that the xmltable would expect it in the same default format. I wonder why it doesn't or if there is another way to work around that without altering the nls_date_format for the session.
10.2 DocsFormatting of XML Dates and Timestamps
The XML Schema standard specifies that dates and timestamps in XML data be in standard formats. XML generation functions in Oracle XML DB produce XML dates and timestamps according to this standard.
In releases prior to Oracle Database 10g Release 2, the database settings for date and timestamp formats were used for XML, instead of the XML Schema standard formats. You can reproduce this previous behavior by setting the database event 19119, level 0x8, as follows:
ALTER SESSION SET EVENTS '19119 TRACE NAME CONTEXT FOREVER, LEVEL 0x8';
Therefore, without altering NLS_DATE_FORMAT you need something like:
SQL> select *
2 from xmltable(
3 '/ROWSET/*'
4 passing xmltype(getemplist(10))
5 columns
6 empno number(4) path '/ROW/EMPNO',
7 ename varchar2(10) path '/ROW/ENAME',
8 job varchar2(9) path '/ROW/JOB',
9 mgr number(4) path '/ROW/MGR',
10 hiredate date path '/ROW/HIREDATE',
11 sal number(7,2) path '/ROW/SAL',
12 comm number(7,2) path '/ROW/COMM',
13 deptno number(2) path '/ROW/DEPTNO'
14 )
15 /
select *
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL>
SQL> select empno,
2 ename,
3 job,
4 mgr,
5 to_date(hiredate) hiredate,
6 sal,
7 comm,
8 deptno
9 from xmltable(
10 '/ROWSET/*'
11 passing xmltype(getemplist(10))
12 columns
13 empno number(4) path '/ROW/EMPNO',
14 ename varchar2(10) path '/ROW/ENAME',
15 job varchar2(9) path '/ROW/JOB',
16 mgr number(4) path '/ROW/MGR',
17 hiredate varchar2(50) path '/ROW/HIREDATE',
18 sal number(7,2) path '/ROW/SAL',
19 comm number(7,2) path '/ROW/COMM',
20 deptno number(2) path '/ROW/DEPTNO'
21 )
22 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 05:17:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
