6 Replies Latest reply on May 15, 2006 7:55 PM by gdarling - oracle

    Stored procedure parameter value.

    512278
      Hi All,

      I have a serious problem with the parameter passing to a stored procedure.

      database 9i;
      Language VB.NET 2003;
      Framework 1.1;

      Task: Tring to receive a Timestamp value from the database by using an Input output parameter defined with a datbase type of 'CHAR' and size of 50.

      Getting error "Cast from type 'OracleString' to type 'String' is not valid".

      In test stored procedure there are 7 parameters going in and the value for the 'Timestamp' is the 5th value.

      The parameter value looks like this;
      Dim parm As New OracleParameter("inout_parmfield", OracleDbType.Int32,
      ParameterDirection.InputOutput)
      parm.Value = FieldValue
      .prameters.Add(parm)

      parameters.Add("in_field2", OracleDbType.Char, ParameterDirection.InputOutput).Value = mField2

      parameters.Add("in_field3", OracleDbType.Char, ParameterDirection.InputOutput).Value = mField3


      parameters.Add("in_field4", OracleDbType.Char, ParameterDirection.InputOutput).Value = mField4


      Dim parm1 As New OracleParameter("inout_Timestamp", OracleDbType.Varchar2,
      ParameterDirection.InputOutput)
      parm1.Size = 50
      parm1.Value = mField5
      .prameters.Add(parm)


      parameters.Add("in_field6", OracleDbType.Char, ParameterDirection.InputOutput).Value = mField6


      parameters.Add("in_field7", OracleDbType.Char, ParameterDirection.InputOutput).Value = mField7

      FieldValue = .parameters.Item("inout_parmfield").Value

      mField5 = .parameters.Item("inout_Timestamp").Value


      Please tell me why I get this error code or what is the proper way to call the procedure with this type of data passsing.

      Thansk,

      James
        • 1. Re: Stored procedure parameter value.
          gdarling - oracle
          Hi,

          I'm not quite clear on your problem statement, this works fine for me though.

          Cheers,
          Greg


          /*
          create or replace procedure get_ts(v1 timestamp) as
          begin
          select systimestamp into v1 from dual;
          end;
          /
          */
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;
          using Oracle.DataAccess.Types;

          public class testrefcur
          {
          public static void Main()
          {
          OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;");
          con.Open();
          OracleCommand cmd = new OracleCommand("get_ts", con);
          cmd.CommandType = CommandType.StoredProcedure;
          OracleParameter oparam = cmd.Parameters.Add("p1", OracleDbType.Varchar2);
          oparam.Direction = ParameterDirection.InputOutput;
          oparam.Size = 50;
          cmd.ExecuteNonQuery();
          Console.WriteLine(oparam.Value);
          }
          }
          • 2. Re: Stored procedure parameter value.
            512278
            Hi Greg,

            Thanks for the reply, the stored proc is supposed to return a Timestamp value about 28 characters long but for some reason there isn't a data type in .NET that can handel the Timestamp value coming from the database. Could you test this process for me and see what you receive in the Value property of the parameter object. All I get is an Timestamp object type that cant be cast to any data type in .NET.

            Thanks,

            James
            • 3. Re: Stored procedure parameter value.
              512278
              Ok I've done a little research on the problem that I am having. Sorry about the confusion on the original post. What is happening, is that I am having problems receiving string values from any stored procedure that I call. Whether the value on the "OUTPUT" parameter is defined as "CHAR", "VARCHAR" or any of the valid Oracle data types.

              What does come back is a data type of "OracleString" which can not be cast to any .NET data type except object. From this data type I can retrieve the data from the Value property, but this should not have to be this way.

              So If anyone has an answer for this horror that I am going through please let me know. I'm working on a new development effort and time is of the essence.

              Thanks,

              James
              • 4. Re: Stored procedure parameter value.
                gdarling - oracle
                Hi James,

                See if this helps.

                Greg


                /*
                create or replace procedure getstring (v1 out varchar2) is
                begin
                v1 :='hello';
                end;
                /
                */
                using System;
                using System.Data;
                using Oracle.DataAccess.Client;
                using Oracle.DataAccess.Types;

                public class teststring
                {
                public static void Main()
                {
                OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;");
                con.Open();
                OracleCommand cmd = new OracleCommand("begin getstring(:p1);end;", con);
                OracleParameter oparam = cmd.Parameters.Add("p1", OracleDbType.Varchar2);
                oparam.Direction = ParameterDirection.Output;
                oparam.Size = 50;
                cmd.ExecuteNonQuery();
                // either of these will work
                string s1 = oparam.Value.ToString();
                string s2 = (string)(OracleString)oparam.Value;
                //cleanup
                }
                }
                • 5. Re: Stored procedure parameter value.
                  505551
                  I seem to be having the same issue. My procedure returns varchar data type.
                  Procedure works fine, but on ht eC# side oDP is returning NULL or not values.


                  Similar code to the previous
                  oraCmd.ExecuteNonQuery();
                  schema=v_schema.Value.ToString();

                  schema is null and even on checking oraCmd parameters are null.
                  I really fell ODP is not so good after all.
                  • 6. Re: Stored procedure parameter value.
                    gdarling - oracle
                    Hi Rashmi,

                    Your problem doesnt sound similar to the original poster's to me.
                    Anyway, can you provide a very small testcase similar to what I have provided above to reproduce the behavior?

                    Cheers
                    Greg