Oracle proc returning XML
Hello All,
I need to create a procedure on oracle that retrives data in xml format (using xmlgen) and returns it to a .NET application.
I am not sure if the following code would help me get this.
Code:
CREATE OR REPLACE PROCEDURE "XMLTEST1" (p_output OUT varchar2) IS
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off1 PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM scott.emp WHERE empno = '|| emp_no);
-- Replace the default ROWSET tag with my own
dbms_xmlgen.SetRowSetTag(ctx, 'DocumentLevel');
-- Remove the default ROW level tag with my own
-- If I passed in text instead of NULL I would just change the element name
-- I can leave it off altogther and get the default, canonical format
dbms_xmlgen.SetRowTag(ctx, NULL);
xml := dbms_xmlgen.getXML(ctx);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off1, p_output);
-- Display null tags for empty columns
--dbms_xmlgen.SetNullHandling(ctx, dbms_xmlgen.EMPTY_TAG);
-- Create an XML document out of the ref cursor
--p_output := dbms_xmlgen.getXMLType( ctx );
END;
/
Can anyone help me with this..or provide me with any link?
thanks
Sam
Probably granted through role
PL/SQL will not take into account privileges that you have been granted through a role. You must be granted privileges on the objects directly. That is likely the issue you are running into.