Oracle Managed Data Reader Returning DBNull for One Database Table

Refresh

1 weeks ago

Views

5 time

0

Issue:

I am using the Oracle managed data access client to connect to an Oracle eBS database. When selecting a value from one table I get a result; when changing the table I get a null value.

Background:

In my SQLDeveloper client I can connect to the database and run the following queries:

SELECT MSIB.SEGMENT1 FROM APPS.MTL_SYSTEM_ITEMS_B MSIB WHERE MSIB.ORGANIZATION_ID = 255 AND MSIB.SEGMENT1 = '03F.211';
    -- 1 ROW RETURNED - VALUE OF 03F.211
SELECT MC.SEGMENT1 FROM APPS.MTL_CATEGORIES_VL MC WHERE MC.SEGMENT1 = 'A0042I';
    -- 1 ROW RETURNED - VALUE OF A0042I

Then, in C#, I'm connecting to the database with exactly the same credentials (so it should not be a database permissions issue).

Running this code I get a message box returning what I would expect: 03F.211

 OracleConnection oradbcon = new OracleConnection(strOracleConString);
 oradbcon.Open();
 strQuery = "SELECT MSIB.SEGMENT1 FROM APPS.MTL_SYSTEM_ITEMS_B MSIB WHERE MSIB.ORGANIZATION_ID = 255 AND MSIB.SEGMENT1 = " + ":ITEM_CODE";
 // strQuery = "SELECT MC.SEGMENT1 FROM APPS.MTL_CATEGORIES_VL MC WHERE MC.SEGMENT1 = " + ":ITEM_CODE";
 OracleCommand oradbcmd = new OracleCommand(strQuery, oradbcon);
 oradbcmd.CommandType = CommandType.Text;
 OracleParameter p_item_code = new OracleParameter();
 p_item_code.OracleDbType = OracleDbType.Varchar2;
 p_item_code.Value = "03F.211";
 // p_item_code.Value = "A0042I";
 oradbcmd.Parameters.Add(p_item_code);
 OracleDataReader oradbdr = oradbcmd.ExecuteReader();
 oradbdr.Read();
 strProductMinor = oradbdr.GetString(0).ToString();
 MessageBox.Show(strProductMinor);

But running the code with those commented lines switched I get an error indicating a database null:

OracleConnection oradbcon = new OracleConnection(strOracleConString);
oradbcon.Open();
// strQuery = "SELECT MSIB.SEGMENT1 FROM APPS.MTL_SYSTEM_ITEMS_B MSIB WHERE MSIB.ORGANIZATION_ID = 255 AND MSIB.SEGMENT1 = " + ":ITEM_CODE";
strQuery = "SELECT MC.SEGMENT1 FROM APPS.MTL_CATEGORIES_VL MC WHERE MC.SEGMENT1 = " + ":ITEM_CODE";
OracleCommand oradbcmd = new OracleCommand(strQuery, oradbcon);
oradbcmd.CommandType = CommandType.Text;
OracleParameter p_item_code = new OracleParameter();
p_item_code.OracleDbType = OracleDbType.Varchar2;
// p_item_code.Value = "03F.211";
p_item_code.Value = "A0042I";
oradbcmd.Parameters.Add(p_item_code);
OracleDataReader oradbdr = oradbcmd.ExecuteReader();
oradbdr.Read();
strProductMinor = oradbdr.GetString(0).ToString();
MessageBox.Show(strProductMinor);

Screenshot_of_error_for_dbnull

What would cause one query to return results but the other to not?

(Finally - forgive my ignorance of C# if anything above is ugly. Years of Oracle experience - first C# program ever... :) )

0 answers