Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this function (written here by someone), placed in the package ENVER.ENCODING_VERIFIER:

FUNCTION loadxml_table (p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_format IN VARCHAR2, p_tmpClob IN NUMBER) RETURN number
IS

  v_ctx dbms_xmlsave.ctxType;
  v_rows number := -1;

BEGIN

  for c1 in (select theclob
               from enver.tmp_xml_clob
              where id = p_tmpClob) loop

    v_ctx := dbms_xmlsave.newContext(upper(p_owner || '.' || p_tableName));
    dbms_xmlsave.setDateFormat(v_ctx, p_format);

    v_rows := dbms_xmlsave.insertxml(v_ctx, c1.theclob);
    dbms_xmlsave.closeContext(v_ctx);

    delete from enver.tmp_xml_clob
     where id = p_tmpclob;
  end loop;

  return v_rows;

END loadxml_table;

END;

In the table: tmp_xml_clob, column: theclob (id=1), I have this record:

<ROWSET>
 <ROW>
  <ID>1</ID>
  <IMAGE></IMAGE>
  <MESSAGE>a</MESSAGE>
 </ROW>
</ROWSET>

The structure of MYSCHEMA.MYTABLE table is like the following:

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(38)
IMAGE                                              BLOB
MESSAGE                                            VARCHAR2(10)

When I call it from SQLPLUS logged as user ENVER (but it give me the same error if I call it from Java method, as explained in the link to the page I have included above):

declare
     x number;
     begin
     x := enver.encoding_verifier.loadxml_table('MYSCHEMA', 'MYTABLE', 'dd-MMM-yyyy HH.mm.ss', 1);
     end;
/

Unfortunately, this error is returned:

ORA-29532: Java call terminated by uncaught Java exception:
oracle.xml.sql.OracleXMLSQLException: 'oracle.jdbc.driver.OracleSQLException:
ORA-01031: insufficient privileges
' encountered during processing ROW element 1.  All prior XML row changes were
rolled back. in the XML document.
ORA-06512: at "SYS.DBMS_XMLSAVE", line 115
ORA-06512: at "ENVER.ENCODING_VERIFIER", line 179
ORA-06512: at line 4

The error is generated by the line:

v_rows := dbms_xmlsave.insertxml(v_ctx, c1.theclob);

In the trace file I have this message:

Exception in thread "Root Thread" oracle.xml.sql.OracleXMLSQLException: 'oracle.jdbc.driver.OracleSQLException: ORA-01031: insufficient privileges
' encountered during processing ROW element 1.  All prior XML row changes were rolled back. in the XML document.
        at oracle.xml.sql.dml.OracleXMLSave.saveXML(OracleXMLSave.java:2568)
        at oracle.xml.sql.dml.OracleXMLSave.insertXML(OracleXMLSave.java:1521)
        at oracle.xml.sql.dml.OracleXMLStaticSave.insertXML(OracleXMLStaticSave.java:466)

I don't know what privileges refers to the error. The user ENVER have all privileges in any of these tables. I tried to perform the same function as SYS, but nothing.

I have already granted execute on DBMS_XMLSAVE to ENVER, but it still does not work.

What could be the problem?

Thank you.

share|improve this question
1  
Does ENVER have privs on MYSCHEMA? Is that the real schema name? You're better off not bothering hiding the real names. Only causes confusion –  Phil Feb 9 at 22:56
1  
Sorry, I thought the opposite. Anyway, that is my fault, I made a stupid mistake of not giving the priv "insert any table" to ENVER :/. You made me think about this. Thanks. –  Shaunyl Feb 9 at 23:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.