How to bring XML output from Oracle Stored procedure in c#

Question

I am facing issues to use Oracle with C#. We are moving away from SQL to Oracle and our S/W vendor provide us APIusing the AX_Gate.Process method, to return trading styles via a PL/SQL anonymous block:

declare
  vMName varchar2(100);
  vInput XMLtype;
  vOutput XMLtype;
begin
  vMName := 'Marketing Styles';
  vInput := XMLtype('<Marketing_Styles-Read/>');
  vOutput := AX_Gate.Process(vMethodName, vInput);
  DBMS_OUTPUT.PUT_LINE(vOutput.getStringVal());
end;

To use this process in C# i develope code that is able to send information but it is not sending back the required XML output. C# code is below:

try
            {
                //Oracle connection open
                OracleConnection.Open();

                //SQL script to invoke  AX_Gate.Process
                string SQLScript = "declare vMName varchar2(100); vInput XMLtype; vOutput XMLtype; "+
                                   "begin vOutput := AX_Gate.Process(" + vMethodName + " , " + vInput + ");  end;";

                OracleCommand OraCommand = new OracleCommand(SQLScript);
                OraCommand.Connection = OracleConnection;
                OraCommand.CommandType = System.Data.CommandType.Text;
                //OraCommand.Parameters.Add(new OracleParameter("vOutput", OracleDbType.XmlType, ParameterDirection.Output));
                OraCommand.Parameters.Add(new OracleParameter("vOutput", OracleDbType.XmlType)).Direction = ParameterDirection.Output;

                //OracleDataReader dr = new OracleDataReader();
                OracleXmlType poXml;
                OracleDataReader poReader = OraCommand.ExecuteReader();



                var sqlDataAdapter = new OracleDataAdapter(OraCommand);
                var dataTable = new DataTable("vOutput");
                sqlDataAdapter.Fill(dataTable);
                OracleConnection.Close();

How can I send XML and retrieve back output as an XML using above Oracle process / procedure.

Thank You

Stawros · Accepted Answer · 2015-11-25 12:03:27Z

        string SQLScript = "declare vMName varchar2(100); vInput XMLtype; vOutput XMLtype; "+
                           "begin vOutput := AX_Gate.Process(" + vMethodName + " , " + vInput + ");  end;";
...
OraCommand.Parameters.Add(new OracleParameter("vOutput", OracleDbType.XmlType)).Direction = ParameterDirection.Output;

In your code there is not one bind paramter and then OracleParameter never used. If you want to use out parameter in pl/sql code, you should not to declare it in declaration, but use it with colon sign ":" -

    String queryString =
        @"declare
             xml_ xmltype := xmltype('<root></root>');
            begin
             :par := xml_;
            end;";

    using (OracleConnection connection = new OracleConnection(source))
    {
        OracleCommand command = new OracleCommand(queryString, connection);
        connection.Open();
        var res = command.Parameters.Add("par", OracleDbType.XmlType, ParameterDirection.Output);
        command.ExecuteNonQuery();
        MessageBox.Show(((Oracle.DataAccess.Types.OracleXmlType)(res.Value)).Value);
    }

Collectives™ on Stack Overflow

How to bring XML output from Oracle Stored procedure in c#

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