ODP.NET: PLS-00707 with xmltype input parameter 1

[goaway1234]

I have a web application where a web service written in C# coordinates communication between an Oracle 9i release 2 database and the rest of the application. In a method that is used to call store procedures, I am trying to add the ability to create xmltype input parameters.

Everything works as expected until the procedure is actually executed. When I call OracleCommand.ExecuteNonQuery, the procedure being called always errors out with "PLS-00707: unsupported construct or internal error [2603]".

Called Procedure is:

create or replace procedure test_xml_params(p_xdoc in xmltype, p_result out xmltype) is
    i integer;
begin
    insert into test_xml_tab values (p_xdoc);
    p_result := p_xdoc;
end;

C# code is:

XmlDocument request;
string xpath;

XmlNode pnode;
XmlDocument pdoc;
OracleXmlType pval
OracleParameter p;
OracleConnection dbCon;
OracleCommand cmd;

pnode = request.SelectSingleNode(xpath);
pdoc = new XmlDocument();
pdoc.AppendChild(pdoc.ImportNode(pnode, true));
pval = new OracleXmlType(dbCon, pdoc);
p = new OracleParameter(key, OracleDbType.XmlType, pval, 
                        ParameterDirection.Input);
OracleCommand cmd = new OracleCommand("test_xml_params", dbCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
...

cmd.ExecuteNonQuery();

I've left out sections of code for brevity's sake, but I assure you that when ExecuteNonQuery() is called, the command has all of the necessary parameters, and the connection is open and works for other procedures and queries. Also, this procedure works if you eliminate the input parameter; it will retrieve xmltype output parameters just fine. The xml document I am attempting to pass is simply: <testing>Hello World</testing>

All of the documentation and online code samples I can find indicate that I am creating everything correctly. The exact same code works with a 10g database.

Can anybody provide any insight as to what is happening?

 

[carp]

I believe an XMLTYPE has a database-specific OID assigned to it. Inbound XMLTYPEs require this OID and you are not supplying it (at least, one that your database recognizes). An outbound XMLTYPE will have this OID, although your code may not require it.
It sounds like 10g has eliminated this problem.
Corrections gratefully solicited!

 

[goaway1234]

Thank you very much for your help--this problem has been frustrating me for the entire day.

I tried a workaround. I create a function that returns an xmltype that always contains the document <xml/>. In .NET, I call this function and get the resulting OracleXmlType object, then use the Update method to assign it the value I want to pass to the sp. Everything works beautifully until the actual value is passed to the sp. No matter what value the OracleXmlType has, the parameter is always null.

At this point, I am ready for a new approach. Do CLOBs also use OIDs?

 

[carp]

Sadly, yes.
I ran into this same problem a year or so ago. Oracle Support's solution was to "wait for the next release and then upgrade". Which I think is their way of saying "you can't get there from here"!

 

[goaway1234]

Well, I did finally have some luck using CLOBs. If CLOBs have OIDs, then perhaps the theory is wrong. I was able to create an OracleClob in .NET and pass it to an sp with no problems.

I'm still not sure what the problem was, but it is a whole lot easier to put something like "xml_value := XMLType(clob_value)" in my stored procedures than to pass an xmltype parameter.

Anyway, thanks for all of your help, and one star for teaching me something new about objects in oracle.

 

[carp]

Hmm. Perhaps I was thinking of BLOBs! If CLOBs work, then that IS good news!