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 Go to next message
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 #324528 is a reply to message #324508] Mon, 02 June 2008 23:44 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Please show us with code.
It is kinda hard to get the root cause of your error when you only vaguely describe it.
Re: Retrieving XML data type column in PL/SQL cursor [message #324686 is a reply to message #324508] Tue, 03 June 2008 12:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 9077
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> 



Previous Topic: Calling WebService using jsp
Next Topic: SELECT XML DATA IN CLOB
Goto Forum:
  


Current Time: Fri Mar 29 08:34:58 CDT 2024