Need some Oracle help if possible [modified]

[Michael J Eber]

I could use some help with our Oracle implementation. We are trying to do an insert through a REST service using Oracle.DataAccess.dll. When we execute UPDATE the process runs without problems. But an INSERT causes a type mismatch error. The frustrating part of this is that we have the database defined to take null for all fields but the fields rejected are null. Edit:: I should add that we are sending the data as comma delimited strings to the function so there is no numeric fields being passed in the call. Here is the function we are calling: CREATE OR REPLACE FUNCTION CNRP.CNRP_S_15_109_STAKEHLD_I ( STAKEHOLDER_ID IN SIMS_STAKEHOLDER.STAKEHOLDER_ID%TYPE, SITE_ID IN SIMS_STAKEHOLDER.SITE_ID%TYPE, STAKEHOLDER_TYPE IN SIMS_STAKEHOLDER.STAKEHOLDER_TYPE%TYPE, TITLE IN SIMS_STAKEHOLDER.TITLE%TYPE, NAME_FULL IN SIMS_STAKEHOLDER.NAME_FULL%TYPE, MAIL_ADDRESS IN SIMS_STAKEHOLDER.MAIL_ADDRESS%TYPE, MAIL_CITY IN SIMS_STAKEHOLDER.MAIL_CITY%TYPE, MAIL_STATE IN SIMS_STAKEHOLDER.MAIL_STATE%TYPE, MAIL_ZIP IN SIMS_STAKEHOLDER.MAIL_ZIP%TYPE, PHONE_HOME IN SIMS_STAKEHOLDER.PHONE_HOME%TYPE, PHONE_CELL IN SIMS_STAKEHOLDER.PHONE_CELL%TYPE, PHONE_WORK IN SIMS_STAKEHOLDER.PHONE_WORK%TYPE, PREF_LANG_WRITTEN IN SIMS_STAKEHOLDER.PREF_LANG_WRITTEN%TYPE, PREF_LANG_VERBAL IN SIMS_STAKEHOLDER.PREF_LANG_VERBAL%TYPE, EMAIL IN SIMS_STAKEHOLDER.EMAIL%TYPE, PHONE_FAX IN SIMS_STAKEHOLDER.PHONE_FAX%TYPE ) RETURN BOOLEAN AS DONE BOOLEAN; BEGIN INSERT INTO SIMS_STAKEHOLDER (SITE_ID, STAKEHOLDER_TYPE, TITLE, NAME_FULL, MAIL_ADDRESS, MAIL_CITY, MAIL_STATE, MAIL_ZIP, PHONE_HOME, PHONE_CELL, PHONE_WORK, PREF_LANG_WRITTEN, PREF_LANG_VERBAL, EMAIL, PHONE_FAX) VALUES (CNRP_S_15_109_STAKEHLD_I.SITE_ID, CNRP_S_15_109_STAKEHLD_I.STAKEHOLDER_TYPE, CNRP_S_15_109_STAKEHLD_I.TITLE, CNRP_S_15_109_STAKEHLD_I.NAME_FULL, CNRP_S_15_109_STAKEHLD_I.MAIL_ADDRESS, CNRP_S_15_109_STAKEHLD_I.MAIL_CITY, CNRP_S_15_109_STAKEHLD_I.MAIL_STATE, CNRP_S_15_109_STAKEHLD_I.MAIL_ZIP, CNRP_S_15_109_STAKEHLD_I.PH

[Chris Meech]

With what you have provided it is difficult to recognize what type mis-match is occurring. Even if the database fields are allowed to be null, the type mismatch could be occurring on the function call. Since you indicate that the call to the function is supplying comma delimited string data, then you should verify that each of the data types for all of the input parameters will accept character data. Just a guess, but possibly the parameter SITE_ID is actually expecting to receive a binary numeric value instead of a string. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

[Michael J Eber]

Thanks Chris, Since I normally work with Sql Server I'm out of water in this problem. I can pass any additional info you need to help me. Our code is a central service that gets passed a function and makes the call to the database:

\[ServiceBehavior(IncludeExceptionDetailInFaults = true,
                    InstanceContextMode = InstanceContextMode.Single,
                    ConcurrencyMode = ConcurrencyMode.Single)\]
\[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)\]
public class DataService : SingletonDataServiceBase<String>, ISingletonDataServiceBase<String>
{
    protected override String OnFunctionRequest(string function)
    {
        try
        {
            OracleConnection oracleCon = new OracleConnection(ConfigurationManager.ConnectionStrings\["CNRPConnectionString"\].ConnectionString);
            OracleCommand sqlCom = new OracleCommand(
               string.Format( "begin   :REF\_CUR := {0} ;end;", function), oracleCon);

            sqlCom.Parameters.Add("REF\_CUR", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

            OracleDataAdapter dataAdapter = new OracleDataAdapter();
            sqlCom.Connection.Open();
            sqlCom.ExecuteNonQuery();

            //Populate the DataSet and close the connection
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "REF\_CUR", (OracleRefCursor)(sqlCom.Parameters\["REF\_CUR"\].Value));

            // Release rare resources immediately to prevent memory leaks and database locks
            sqlCom.Dispose();
            oracleCon.Close();
            oracleCon.Dispose();

            return this.Serialize(dataSet.Tables\[0\]);
        }
        catch (Exception Exp)
        {
            throw Exp;
        }
    }

When this method is call our insert function is passed as follows to Oracle:

CNRP_S_15_108_STRUCINF_I(7,null,'PWS Repository','Storage',0,null,null,null,null,0,null,null,null,null,null,-1,0,1995,0,0,null,null,0,-1,'16-May-11',-1,'13-Apr-11')

Hope this provides more information to you.

[jschell]

At a minimum you need to get your code postings in sync. Your posted Oracle function doesn't match the string that you posted. Of course maybe that is the source of your problem - you are mixing up which function it is failing on.

[Chris Meech]

It looks as though you are assigning the result of the function call to a variable and the types of the two do not match. The function is going to return a BOOL type and the variable is of type OracleRefCursor. Not sure if this will work, but instead of ExecuteNonQuery(), would you be able to ExecuteQuery(string) instead and then check the value of the parameter. The parameter should be defined as type BOOL as well.

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

[Michael J Eber]

Well I'm working on something like 4 things at once with 4 hours sleep. I mixed up a few things. No need for sarcasm. :)

[SilimSayo]

I should add that we are sending the data as comma delimited strings Using comma separated strings data can be problematic because some times the data itself may contain a commas so the data string gets parsed incorrectly. Strings such as "NY, NEW YORK, STATE OF" will be parsed incorrectly. I suggest you use pipes (|) to separate data elements for example "NY**|**NEW YORK, STATE OF". If you can get the data in Excel, you can quickly create a CSV that uses a pipe delimiter(sounds like an Oxymoron..). To change Excel such that it uses a pipe instead of a comma in CSV files, go to Control Panel, Regional and Language Options,Click Customize, then change the list separator value to | (from a ,). So when you save files in Excel as CSV, the data elements will actually be separated by '|' instead of ','. Also, change your parser-whatever that is, to use '|'

[Michael J Eber]

Thanks Chris! will doing the ExecuteNonQuery with begin : boolean := 'insert statement' also work? Michael

[Chris Meech]

As long as the parameter :boolean is defined as type BOOL, then I think it might. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

[jschell]

I wasn't being sarcastic. To help with diagnosing a code question one needs to post the code that is the source of the problem.