Trying to call stored procedure in Oracle from C#?

[E11esar]

Hello there.
I am going in bit of a circle with this matter;

First some background: I am trying to upload the details of a CSV file into an Oracle table. I am using a StreamReader to copy a line at a time into an array, using a

string[] columns = line.Split(separators.ToCharArray());

command.

Here is a definition of the table I am loading into:
======================================
UPRN NUMBER (12)
USRN NUMBER (18)
SAO_START_NUMBER NUMBER (4)
SAO_START_SUFFIX VARCHAR2 (1 Byte)
SAO_END_NUMBER NUMBER (4)
SAO_END_SUFFIX VARCHAR2 (1 Byte)
SAO_TEXT VARCHAR2 (90 Byte)
PAO_START_NUMBER NUMBER (4)
PAO_START_SUFFIX VARCHAR2 (1 Byte)
PAO_END_NUMBER NUMBER (4)
PAO_END_SUFFIX VARCHAR2 (1 Byte)
PAO_TEXT VARCHAR2 (90 Byte)
STREET_DESCRIPTOR VARCHAR2 (100 Byte)
LOCALITY_NAME VARCHAR2 (35 Byte)
TOWN_NAME VARCHAR2 (30 Byte)
ADMINISTRATIVE_AREA VARCHAR2 (30 Byte)
POST_CODE VARCHAR2 (8 Byte)
BLPU_LOGICAL_STATUS NUMBER (1)
LPI_LOGICAL_STATUS NUMBER (1)
BLPU_CLASS VARCHAR2 (4 Byte)
X_COORDINATE NUMBER (9,2)
Y_COORDINATE NUMBER (9,2)
LOCAL_CUSTODIAN_CODE NUMBER (4)
BLPU_LAST_UPDATE_DATE DATE
SAON VARCHAR2 (100 Byte)
PAON VARCHAR2 (100 Byte)
FULL_ADDRESS VARCHAR2 (548 Byte)
=========================================

Now using a stored procedure, I am trying to insert values from the CSV file into the Database, here is the stored procedure:

----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SADAS_MANAGER.nlpg_insert_24

(
p_uprn in number,
p_lpi_log_stat in number,
p_blpu_date in date,
p_sao_start_num in number,
p_sao_start_suf in varchar2,
p_sao_end_num in number,
p_sao_end_suf in varchar2,
p_sao_text in varchar2,
p_pao_start_num in number,
p_pao_start_suf in varchar2,
p_pao_end_num in number,
p_pao_end_suf in varchar2,
p_pao_text in varchar2,
p_usrn in number,
p_post_code in varchar2
)

IS

BEGIN

insert into NLPG_UK
(UPRN, LPI_LOGICAL_STATUS,SAO_START_NUMBER, SAO_START_SUFFIX, SAO_END_NUMBER, SAO_END_SUFFIX, SAO_TEXT, PAO_START_NUMBER,
PAO_START_SUFFIX, PAO_END_NUMBER, PAO_END_SUFFIX, PAO_TEXT, USRN, POST_CODE)
VALUES (
p_uprn,
p_lpi_log_stat,
p_sao_start_num,
p_sao_start_suf,
p_sao_end_num,
p_sao_end_suf,
p_sao_text,
p_pao_start_num,
p_pao_start_suf,
p_pao_end_num,
p_pao_end_suf,
p_pao_text,
p_usrn,
p_post_code
)
;
END;
------------------------------------------------------------------------

and here is the code in the c# that I am using to invoke the stored procedure:

************************************************** ********
insCmd.Parameters.Add("p_uprn", OleDbType.UnsignedInt).Value = columns[3];
insCmd.Parameters.Add("p_lpi_log_stat", OleDbType.Char).Value = columns[6];
insCmd.Parameters.Add("p_blpu_date", OleDbType.Date).Value = Convert.ToDateTime(columns[10]);
insCmd.Parameters.Add("p_sao_start_num", OleDbType.Integer).Value = Convert.ToInt32(columns[11]);
insCmd.Parameters.Add("p_sao_start_suf", OleDbType.Char).Value = columns[12].Length == 2 ? "" : columns[12].Substring(2, 1);
insCmd.Parameters.Add("p_sao_end_num", OleDbType.Integer).Value = Convert.ToInt32(columns[13]);
insCmd.Parameters.Add("p_sao_end_suf", OleDbType.Char).Value = columns[14].Length == 2 ? "" : columns[14].Substring(2, 1);
insCmd.Parameters.Add("p_sao_text", OleDbType.Char).Value = columns[15];
insCmd.Parameters.Add("p_pao_start_num", OleDbType.Integer).Value = Convert.ToInt32(columns[16]);
insCmd.Parameters.Add("p_pao_start_suf", OleDbType.Char).Value = columns[17].Length == 2 ? "" : columns[17].Substring(2, 1);
insCmd.Parameters.Add("p_pao_end_num", OleDbType.Integer).Value = Convert.ToInt32(columns[18]);
insCmd.Parameters.Add("p_pao_end_suf", OleDbType.Char).Value = columns[19].Length == 2 ? "" : columns[19].Substring(2, 1);
insCmd.Parameters.Add("p_pao_text", OleDbType.Char).Value = columns[20];
insCmd.Parameters.Add("p_usrn", OleDbType.Integer).Value = Convert.ToInt32(columns[21]);
insCmd.Parameters.Add("p_post_code", OleDbType.Char).Value = columns[24].Length > 2 ? columns[24].Substring(2, (columns[24].Length - 2)) : "";
insCmd.Parameters.Add("p_post_code", OleDbType.Char).Value = columns[24].Length > 2 ? columns[24].Substring(2, (columns[24].Length - 2)) : "";

insCmd.ExecuteNonQuery();
************************************************** ***********************
When I run this I am getting a constant error message of:

"Value was either too large or too small for a UInt32." This is for the first entry of:

insCmd.Parameters.Add("p_uprn", OleDbType.UnsignedInt).Value = columns[3];

Any ideas what I need to do here to enable the value to be passed to the Database please, as I have tried several options now and all fall flat?

Thank you.

Mark :o)

[comphead]

1. Use OracleType instead of OleDbType
2. use watch to see the problem value

[E11esar]

Thank you for that response, it has got me past that particular error situation now. :)