ExecuteNonQuery stuck without throwing error

Question

It must be a silly question but don't know what's wrong with my code
I'm trying to insert some data in oracle database here is my piece of code

try
{                
  OracleConnection con= new OracleConnection("Data Source=ANTI01T.world ;User Id=MyDB;Password=XYZ123 ;");
  con.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection =con;
  cmd.CommandText = query;
  cmd.CommandType = CommandType.Text;
  cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
  MessageBox.Show(ex.Message);
}

where query is

string query = @"INSERT INTO TB_RULE_HISTORY
                                (   N_RULE_ID,
                                    N_RULE_VERSION,
                                    DT_START_DATE,
                                    DT_END_DATE,
                                    N_CURRENCY_ID,
                                    N_VALUE1,
                                    N_VALUE2,
                                    N_PERCENTAGE,
                                    N_MONTHS)
                                    VALUES(" 
                                    + pRuleId.ToString() + " , "
                                    + pRuleVersion.ToString() + " , '"
                                    + DateTime.Now.Date.ToString("dd/MMM/yy") + "' , '"
                                    + pEndDate.ToString("dd/MMM/yy") + "' , "
                                    + pCurrencyId.ToString()+ " , "
                                    + pValue1.ToString()+ " , "
                                    + pValue2.ToString()+ " , "
                                    + pPercentage.ToString()+ " , "
                                    + pMonths.ToString() + ")";

The code is not throwing any error it got stuck don't know where
The possible check I did is that I remove the semicolon from the query by referring Link1 because executing query with ; gives ORA-00911: invalid character, but now it is not throwing any error just got stuck.
Also there is no transaction in use.
Please suggest the possible way to trace it out

Why you don't indicate the connection string to the object OracleConnection ? — aguetat, Dec 03 '15 at 10:47
this is it with the connection string also by using this connection string I'm able to fetch data and insert data from package to database — Amit Bisht, Dec 03 '15 at 10:49

score 1 · Answer 1 · answered Dec 03 '15 at 11:07

First take the value of your string query and run that in Oracle. You have some string values that are surrounded with single quotes, and some like pValue1, pValue2 that are not. I would take the actual Insert from your string query and then run that.

Oracle will hang and do nothing if there is a problem in the script at times. This can be seen by running an invalid script in PL SQL command window. It hangs there waiting for a response.

Second, are you running the insert in PL SQL with the same ID as the connection string? If not you need to prefix the SCHEMA name in front of the insert such as INSERT INTO SCHEMAX.TABLE_NAME. There is no Initial Catalog for Oracle as another person noted, that applies to SQL Server.

score 1 · Answer 2 · answered Dec 03 '15 at 11:15

it can be an issue with your string values can you try to use parameters? it can resolve issue with string values and ...it's common practice to use parameters for number of reasons

string query = "INSERT INTO TB_RULE_HISTORY
(   
N_RULE_ID,
N_RULE_VERSION,
DT_START_DATE,
DT_END_DATE,
N_CURRENCY_ID,
N_VALUE1,
N_VALUE2,
N_PERCENTAGE,
N_MONTHS)
VALUES(:pRuleId
,:pRuleVersion
,:pDateTimeStart
,:pDateTimeEnd
,:pCurrencyId
,:pValue1
,:pValue2
,:pPercentage
,:pMonths)";    


try
{                
  OracleConnection con= new OracleConnection("Data Source=ANTI01T.world ;User Id=AML_UAT;Password=AML_UAT;");
  con.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection =con;
  cmd.CommandText = query;
  cmd.CommandType = CommandType.Text;

  cmd.Parameters.AddWithValue("pRuleId", pRuleId);
  cmd.Parameters.AddWithValue("pRuleVersion", pRuleVersion);

  ...

  cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
  MessageBox.Show(ex.Message);
}

a good approach but getting error CommandText property has not been initialized — Amit Bisht, Dec 03 '15 at 11:33

score 1 · Answer 3 · answered Dec 03 '15 at 14:59

Could be an issue with the NLS Date format on your connection as you are passing in the dates with the string value and letting the connection implicitely convert to a DATE type.

Counting on implicit conversions are always dangerous for long-term implementations, and especially risky for date formats where your code could work on one deployed instance but not another due to environment settings.

So put your date strings into a SQL TO_DATE() function, and specify the format explicitly. I'd also recommend going to the four-digit year for entering any historical dates (birth dates etc) or future dates as letting the database guess the century when they don't know the context of the data can lead to incorrect results.

string query = @"INSERT INTO TB_RULE_HISTORY
                                (   N_RULE_ID,
                                    N_RULE_VERSION,
                                    DT_START_DATE,
                                    DT_END_DATE,
                                    N_CURRENCY_ID,
                                    N_VALUE1,
                                    N_VALUE2,
                                    N_PERCENTAGE,
                                    N_MONTHS)
                                    VALUES(" 
                                    + pRuleId.ToString() + " , "
                                    + pRuleVersion.ToString() + " ,  TO_DATE('"
                                    + DateTime.Now.Date.ToString("dd/MMM/yy") + "','dd/mm/yy') , TO_DATE('"
                                    + pEndDate.ToString("dd/MMM/yy") + "','dd/mm/yy') , "
                                    + pCurrencyId.ToString()+ " , "
                                    + pValue1.ToString()+ " , "
                                    + pValue2.ToString()+ " , "
                                    + pPercentage.ToString()+ " , "
                                    + pMonths.ToString() + ")";

score 0 · Accepted Answer · answered Dec 10 '15 at 15:03

0

The issue I found with this is due to my oracle transaction is not auto commit.
Thus it was getting hanged.

answered Dec 10 '15 at 15:03

Amit Bisht

4,355
13
47
80

1

Could you please provide some code to show how you fixed the problem or to at least explain it a little more? Thanks. – Yoshiya Jun 04 '20 at 12:31

ExecuteNonQuery stuck without throwing error

4 Answers4