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?