CODEWITHSUNDEEP
xmloracle-databaseplsqloracle11g
bonskijr
bonskijr

Reputation: 29

How to return a result of xmltypes in Oracle stored procedure

I have stored procedure:

CREATE OR REPLACE udp_get_employeeaddress ( result_set out sys_refcursor )
AS
BEGIN
   open result_set for
   select xmlcolumn from employees where rownum < 3;

END udp_get_employees;

When I execute this sp, the following is returned in Output Variables in SQL Developer

XML_COLUMN

oracle.jdbc.driver.OracleSQLXML@49e57334

Taking out the SELECT statement only

select xmlcolumn from employees where rownum < 3;

Returns the xml result

XML_COLUMN

The table is defined as this:

CREATE TABLE "EMP"."Employees"
  (
    "ID"              NUMBER,
    "XML_COLUMN" "XMLTYPE"
  )
  xmltype column "XML_COLUMN" STORE AS BINARY XML
  xmlschema "http://www.web.com/emp.xsd"
  element "root"

I'm hoping for my stored procedure to return the same (resultset) as the standalone query.

This is my first foray to Oracle / xml db, as I've been primarily SQL Server DB Developer. Any help is greatly appreciated.

Thanks

UPDATED: Table definition/query results

Upvotes: 0

Views: 1889

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

This isn't a problem with your procedure, but seems to be an oddity with how SQL Developer is shooing the output variable - I can't see a way to show the actual content of that in that tab.

As an alternative you can see the procedure is working by calling it from an SQL Worksheet (running as a script (F5)):

var rc refcursor;
exec udp_get_employeeaddress(:rc);
print :rc;

Upvotes: 3

Related Questions