Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle to SQL, ODP.NET, ADO.NET, OracleCommand to SqlCommand, invalid numeric precision/scale, table

2744438Sep 2 2014 — edited Sep 24 2014

Sorry about the title, but I've been searching these terms to no avail.

I'm trying to pull data from Oracle to SQL Server using ADO.NET in a console application.  I keep running into an "invalid numeric precision/scale" exception and I haven't been able to resolve it.

Driver:

Oracle.DataAccess.dll 4.112.2.0 (11g)

Oracle data type:

NUMBER(38, 0)

SQL Server data type: (none of these work)

FLOAT,

NUMERIC(38, 0),

DECIMAL(38, 0)

For reference, here is Oracle's documentation:

* Data Type Conversion

And Microsoft's documentation:

* Data Type Mapping for Oracle Publishers

* Table-Valued Parameters

Here is the code that I'm using:

using (OracleConnection oracx = new OracleConnection("connectionString"))

{

     oracx.Open();

     using (SqlConnection sqlcx = new SqlConnection("connectionString"))

     {

          sqlcx.Open();

          OracleCommand oc = oracx.CreateCommand();

          oc.CommandText   = "SELECT COLUMN1 FROM TABLE";

          oc.CommandType   = CommandType.Text;

          SqlCommand sc    = sqlcx.CreateCommand();

          sc.CommandText   = "NameOfStoredProcedure";

          sc.CommandType   = CommandType.StoredProcedure;

          SqlParameter tvp = sc.Parameters.AddWithValue("@tvp", oc.ExecuteReader());

          tvp.SqlDbType    = SqlDbType.Structured;

          sc.ExecuteNonQuery();

          sqlcx.Close();

     }

     oracx.Close();

}

Here is the stored procedure in SQL Server:

USE [Database1]

GO

CREATE PROCEDURE [dbo].[Sproc1]

     @tvp [TvpTable1] READONLY

AS

BEGIN

     INSERT INTO

          [Table1]

          (

               [COLUMN1]

          )

     SELECT

          t1.[COLUMN1]

     FROM

          @tvp AS t1

END

Here is the structure of the table-valued parameter (user-defined table type):

USE [Database1]

GO

CREATE TYPE [dbo].[TvpTable1] AS TABLE

(

     [COLUMN1] [float] NULL

)

GO

I've also tried having [COLUMN1] as [numeric](38, 0) and [decimal](38, 0).

All of the above has failed to work properly with the exception thrown at line 20 - invalid numeric precision/scale.

This is very frustrating!

Any suggestions and/or advice?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 22 2014
Added on Sep 2 2014
4 comments
2,023 views