2 Replies Latest reply: Mar 6, 2010 10:24 AM by petez RSS

    XML Type and ODP

    petez
      Hi,
      I have some problems with running application which uses ODP to connect to Oracle in version 9.2.0.6.
      There is one PLSQL packages with maybe 30 procedures. Half of this have VARCHAR2 input parameters.
      In that version of Oracle there is bug - I very ofen get "Numeric or value error" during calling PLSQL.
      Only one suggestion from Oracle is to upgrade into higher version. My Admin told me that upgrade is planned
      but he cannot provide date. So I am going to change my code from:
      ------------------------------------
      PLSQL

      PROCEDURE Test1( param1 IN VARCHAR2
                ,param2 IN VARCHAR2
                ,param3 IN NUMBER
                ,param4 OUT CLOB)

      C#

      OracleCommand theCommand = new OracleCommand("package.Test1");
      theCommand.CommandType = CommandType.StoredProcedure;
      theCommand.Parameters.Add("param1", OracleDbType.Varchar2, value1, ParameterDirection.Input);
      theCommand.Parameters.Add("param2", OracleDbType.Varchar2, value2, ParameterDirection.Input);
      theCommand.Parameters.Add("param3", OracleDbType.Decimal, value3, ParameterDirection.Input);
      theCommand.Parameters.Add("param4", OracleDbType.clob, ParameterDirection.Output);

      ------------------------------------
      INTO

      PLSQL

      --old procedure is not changed
      PROCEDURE Test1( param1 IN VARCHAR2
                ,param2 IN VARCHAR2
                ,param3 IN NUMBER
                ,param4 OUT CLOB)


      --new procedure added with XMLType input
      PROCEDURE Test1( params IN XMLType
                ,paramClob OUT CLOB)
      IS
      param_1 VARCHAR2(4000);
      param_2 VARCHAR2(4000);
      param_3 NUMBER;
      BEGIN
      SELECT ExtractValue( params, '/params/param1')
      ,ExtractValue( params, '/params/param2')
      ,ExtractValue( params, '/params/param3')
      INTO param_1, param_2, param_3
      FROM DUAL;
      --now calling old proc
      Test1(param_1, param_2, param_3, paramClob);
      END Test1;


      C#

      OracleCommand theCommand = new OracleCommand("package.Test1");
      theCommand.CommandType = CommandType.StoredProcedure;

           XDocument packedParams = new XDocument
      (
      new XElement("params",
      new XElement("param1", value1),
      new XElement("param2", value2),
      new XElement("param3", value3)
      )
      );


      theCommand.Parameters.Add("params", OracleDbType.XMLType, packedParams , ParameterDirection.Input);
      theCommand.Parameters.Add("paramClob", OracleDbType.clob, ParameterDirection.Output);

      ------------------------------------

      What do You think about such solution, do you have any bad experiences with using ODP/XMLType ?

      Regards,
      Piotr
        • 1. Re: XML Type and ODP
          sb92075
          I doubt proposed change will reduce frequency of errors.
          "Numeric or value error"
          In V9 this error cause 2 possible causes.
          1) VARCHAR2 is too small
          2) non-numeric value being assigned to numeric datatype.

          If the root cause is #2 above, the error will continue with proposed change.

          Good Luck!
          • 2. Re: XML Type and ODP
            petez
            Hi,
            we opened SR in Oracle for that error. It is confirmed.
            So you dont have any experience,

            Thanks