-
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
-
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
-
well do you have access to dbms_xmlgen? (ps - you dont)
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|