Oracle 9i Stored Procedure XML Type input parameter

Question

I'm trying to pass an XML type to a stored proc in Oracle 9i. All it is supposed to do is extract a value from a node and return it, but it isnt returning the value. Can anyone advise?

XMLString = "<?xml version='1.0' encoding='utf-8' ?>";
XMLString += "<Document>";
XMLString += "<DocumentType>WorkOrder</DocumentType>";
XMLString += "<DocumentAction>Create</DocumentAction>";
XMLString += "<WorkOrder>";
XMLString += "<WO>123456</WO>";
XMLString += "<WOTask>1</WOTask>";
XMLString += "</WorkOrder>";
XMLString += "</Document>";

oraCon.ConnectionString = s_connectionString;
oraCon.Open();
oraCommand.CommandType = CommandType.StoredProcedure;
oraCommand.CommandText = "LOUISETEST.GetActionType";
oraCommand.Connection = oraCon;
oraCommand.Parameters.Add("xml_document_i", OracleDbType.XmlType, XMLString, ParameterDirection.Input);
oraCommand.Parameters.Add("action_i", OracleDbType.Varchar2, result, ParameterDirection.Output);
oraCommand.ExecuteNonQuery();

Console.WriteLine("Result : "+result.ToString());

The Stored Procedure is as follows:

  PROCEDURE GetActionType       
    (
    xml_document_i        IN XMLTYPE
    ,action_i          OUT VARCHAR2
    )
     AS
  BEGIN
      SELECT xml_document_i.Extract('/DocumentAction/text()').getstringval() INTO action_i
      FROM TABLE (XMLSEQUENCE (xml_document_i.EXTRACT ('/Document') ) ) ;
  END GetActionType;

Louise · Accepted Answer · 2012-10-09 15:40:22Z

I resolved this in the following way:

            //XMLString = "<?xml version='1.0' encoding='utf-8' ?>";
            XMLString += "<Document>";
            XMLString += "<DocumentType>WO</DocumentType>";
            XMLString += "<DocumentAction>Create</DocumentAction>";
            XMLString += "<WorkOrder>";
            XMLString += "<WO>123456</WO>";
            XMLString += "<WOTask>1</WOTask>";
            XMLString += "</WorkOrder>";
            XMLString += "</Document>";

            Console.WriteLine(XMLString.ToString());

            oraCon.ConnectionString = s_connectionString;
            oraCon.Open();
            oraCommand.CommandType = CommandType.StoredProcedure;
            oraCommand.CommandText = "LOUISETEST.GetActionType";
            oraCommand.Connection = oraCon;
            oraCommand.Parameters.Add("xml_document_i", OracleDbType.XmlType, XMLString, ParameterDirection.Input);

            OracleParameter result = new OracleParameter();
            result.ParameterName = "action_i";
            result.Direction = ParameterDirection.Output;
            result.OracleDbType = OracleDbType.Varchar2;
            result.Size = 256;
            oraCommand.Parameters.Add(result);

            oraCommand.ExecuteNonQuery();

            Console.WriteLine("Result : " + result.Value.ToString());

and with the following SP:

PROCEDURE GetActionType        
    ( 
    xml_document_i        IN XMLTYPE
    ,action_i             OUT VARCHAR2
    ) 
     AS 
     v_name      VARCHAR2(256);
  BEGIN   

  v_name := xml_document_i.extract('/Document/DocumentAction/text()').getStringVal();    
  SELECT v_name INTO action_i FROM dual;

  END GetActionType;

Collectives™ on Stack Overflow

Oracle 9i Stored Procedure XML Type input parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related