Oracle proc returning XML

[aspdba]

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.

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

[aspdba]

I get the following error while executing the current proc

ERROR at line 1:
ORA-19202: Error occurred in XML processing
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_XMLGEN", line 7
ORA-06512: at "SYS.DBMS_XMLGEN", line 147
ORA-06512: at "SYSMAN.XMLTEST1", line 19
ORA-06512: at "SYSMAN.XMLTEST2", line 4
ORA-06512: at line 1

[davey23uk]

well do you have access to dbms_xmlgen? (ps - you dont)

[aspdba]

I get the xml output when i just query from sql prompt...but when I try to do the same from PL/SQL, it throws this error.

Do we have to give spl. permissions for it to be used in PL/SQL?

Thanks
Sam

[wdfloyd]

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.