Home » Developer & Programmer » JDeveloper, Java & XML » Retrieving XML data type column in PL/SQL cursor (10.2.0.3.0)
| Retrieving XML data type column in PL/SQL cursor [message #324508] |
Mon, 02 June 2008 19:03  |
lupeg
Messages: 5
Registered: June 2008
|
Junior Member |
|
|
I've got a problem retrieving a column of xmltype using a cursor in a PL/SQL procedure. I'm able to run the sql manually and I see the xml column being pulled. However when I run the same sql in a PL/SQL procedure inside of a Package using a cursor, it comes back blank. Does anyone know how these particular columns should be pulled?
|
|
|
|
|
|
| Re: Retrieving XML data type column in PL/SQL cursor [message #324686 is a reply to message #324508] |
Tue, 03 June 2008 12:50  |
lupeg
Messages: 5
Registered: June 2008
|
Junior Member |
|
|
My Apologies. Here is a longer explanation of what I'm trying to do. I have a table where I'm storing XML data in. Here is a sample of the DDL used to create that table
CREATE TABLE users
(
user_id NUMBER,
username VARCHAR2(100 BYTE),
user_preference_xml SYS.XMLTYPE,
create_date DATE,
last_update_date DATE,
user_email VARCHAR2(75)
);
If you notice, I have a column in there as sys.xmltype. This is where I store the xml data in. What I'm needing to do is in a stored procedure get the entire xml data that is in that column, as well as a piece of it for a specific user and pass that in a cursor back to calling program. Here is an example of that procedure.
CREATE OR REPLACE PROCEDURE get_user_info (
in_username IN users.username%TYPE,
out_result OUT sys_refcursor
)
IS
BEGIN
OPEN out_result
FOR
SELECT user_id, username, user_preference_xml, create_date,
last_update_date, user_email,
(SELECT (EXTRACT (user_preference_xml,
'/communicationsuite/user/gui/profileData'
)
)
FROM users
WHERE username = in_username) AS user_profile
FROM users
WHERE username = in_username;
END get_user_info;
The problem lies in the xml data not being passed back in the cursor. The two places where I'm grabbing that are returned blank in the cursor. If I grab the sql and run that separately using any tool (SQL plus, Toad, etc), it works fine. I can see the xml data being returned back. Its in the procedure that it is not being returned back to me. Any ideas what I could be doing wrong here?
|
|
|
|
| Re: Retrieving XML data type column in PL/SQL cursor [message #324724 is a reply to message #324686] |
Tue, 03 June 2008 15:50 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You do realize that this procedure does not return a piece of xml, but rather a cursor variable? It may be obvious, but I have no idea of your level of pl/sql knowledge.
Do you have a test-program you can use in sql*plus to call this procedure and to examine the "contents" of the returned ref cursor?
|
|
|
|
| Re: Retrieving XML data type column in PL/SQL cursor [message #324735 is a reply to message #324508] |
Tue, 03 June 2008 16:55 |
lupeg
Messages: 5
Registered: June 2008
|
Junior Member |
|
|
Yes I do realize that. But if you notice in the DDL I provided, the user_preference_xml column is an xmltype column. What I'm trying to do is select that column in a procedure and pass it back in the cursor. When I run the SQL in sql plus, I can see data coming back. However, in the procedure nothing is returned, its just blank.
This is probably not going to look nice here, but here goes. I ran these in SQL Plus and this is what I get back. When I run the Query by itself, outside of the procedure, this is what is returned. Notice the xml data in bold
USER_ID Usernam
---------- -------
USER_PREFERENCE_XML
----------------------------------------------------------------------------------------------------
CREATE_DATE LAST_UPDATE_DATE USER_EMAIL
-------------------- -------------------- ----------------------------------------------------------
USER_PROFILE
----------------------------------------------------------------------------------------------------
932 TestUse
<?xml version='1.0' encoding='utf-8'?><!-- 2004 (http://www.
25-MAR-2008 06:06:33 25-MAR-2008 06:06:33 user@users.com
<avsettings><avenabled>true</avenabled><audioSend>true</audioSend><videoSend>tru
This is what it looks like when I execute the procedure from SQL Plus. Notice that there is no XML data being returned back.
USER_ID Usernam
---------- -------
USER_PREFERENCE_XML
----------------------------------------------------------------------------------------------------
CREATE_DATE LAST_UPDATE_DATE USER_EMAIL
-------------------- -------------------- ----------------------------------------------------------
USER_PROFILE
----------------------------------------------------------------------------------------------------
932 TestUse
25-MAR-2008 06:06:33 25-MAR-2008 06:06:33 user@users.com
Any ideas what I'm doing wrong?
|
|
|
|
| Re: Retrieving XML data type column in PL/SQL cursor [message #324738 is a reply to message #324735] |
Tue, 03 June 2008 18:08 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I would not use a subquery, since you are only selecting from one table. I have provided an example below. If that does not help, then please post a copy and paste of the query used in the procedure, as I have done below, substituting a literal value for the variable. You might check whether your extract is trying to return multiple rows. If so, you can apply the solution from your other post.
SCOTT@orcl_11g>
SCOTT@orcl_11g> CREATE TABLE users
2 (
3 user_id NUMBER,
4 username VARCHAR2(100 BYTE),
5 user_preference_xml SYS.XMLTYPE,
6 create_date DATE,
7 last_update_date DATE,
8 user_email VARCHAR2(75)
9 )
10 /
Table created.
SCOTT@orcl_11g> INSERT INTO users
2 SELECT e.empno, e.ename,
3 XMLTYPE (DBMS_XMLGEN.GETXML
4 ('SELECT d.dname AS profiledata
5 FROM dept d
6 WHERE d.deptno = ' || e.deptno)),
7 hiredate,
8 hiredate,
9 job
10 FROM emp e
11 WHERE e.deptno = 10
12 /
3 rows created.
SCOTT@orcl_11g> SELECT * FROM users
2 /
USER_ID
----------
USERNAME
--------------------------------------------------------------------------------
USER_PREFERENCE_XML
--------------------------------------------------------------------------------
CREATE_DA LAST_UPDA
--------- ---------
USER_EMAIL
---------------------------------------------------------------------------
7782
CLARK
<?xml version="1.0"?>
<ROWSET>
<ROW>
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
</ROW>
</ROWSET>
09-JUN-81 09-JUN-81
MANAGER
7839
KING
<?xml version="1.0"?>
<ROWSET>
<ROW>
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
</ROW>
</ROWSET>
17-NOV-81 17-NOV-81
PRESIDENT
7934
MILLER
<?xml version="1.0"?>
<ROWSET>
<ROW>
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
</ROW>
</ROWSET>
23-JAN-82 23-JAN-82
CLERK
3 rows selected.
SCOTT@orcl_11g> SELECT user_id, username, user_preference_xml, create_date,
2 last_update_date, user_email
3 , EXTRACT (user_preference_xml,
4 '//PROFILEDATA')
5 FROM users
6 WHERE username = 'KING'
7 /
USER_ID
----------
USERNAME
--------------------------------------------------------------------------------
USER_PREFERENCE_XML
--------------------------------------------------------------------------------
CREATE_DA LAST_UPDA
--------- ---------
USER_EMAIL
---------------------------------------------------------------------------
EXTRACT(USER_PREFERENCE_XML,'//PROFILEDATA')
--------------------------------------------------------------------------------
7839
KING
<?xml version="1.0"?>
<ROWSET>
<ROW>
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
</ROW>
</ROWSET>
17-NOV-81 17-NOV-81
PRESIDENT
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
1 row selected.
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE get_user_info
2 (in_username IN users.username%TYPE,
3 out_result OUT sys_refcursor)
4 AS
5 BEGIN
6 OPEN out_result FOR
7 SELECT user_id, username, user_preference_xml, create_date,
8 last_update_date, user_email
9 , EXTRACT (user_preference_xml,
10 '//PROFILEDATA')
11 FROM users
12 WHERE username = in_username;
13 END get_user_info;
14 /
Procedure created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXECUTE get_user_info ('KING', :g_ref)
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> PRINT g_ref
USER_ID
----------
USERNAME
--------------------------------------------------------------------------------
USER_PREFERENCE_XML
--------------------------------------------------------------------------------
CREATE_DA LAST_UPDA
--------- ---------
USER_EMAIL
---------------------------------------------------------------------------
EXTRACT(USER_PREFERENCE_XML,'//PROFILEDATA')
--------------------------------------------------------------------------------
7839
KING
<?xml version="1.0"?>
<ROWSET>
<ROW>
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
</ROW>
</ROWSET>
17-NOV-81 17-NOV-81
PRESIDENT
<PROFILEDATA>ACCOUNTING</PROFILEDATA>
1 row selected.
SCOTT@orcl_11g>
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 17:21:04 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
