CODEWITHSUNDEEP
c#sqloracle-database
Salik
Salik

Reputation: 518

SQL command from C# through OracleCommand giving invalid character error

Following is the command i am trying to run in my C# code using oracle connection string and everything is fine(the same query runs smoothly in toad). However upon filling the dataset it gives an error of ORA-00911: invalid character. I can't figure out why, please help.

 OracleCommand cmd = new OracleCommand(
            "SELECT DSAT.ORGANIZATION_ID, APPS.DAW_INV_ORG_NAME(DSAT.ORGANIZATION_ID) ORGANIZATION_NAME,APPS.DAW_CAT_NAME(MC.SEGMENT4) PRODUCT,TRUNC(NVL(DSAT.SCH_END_DATE,DSAT.SCH_START_DATE)) TRANSACTION_DATE,"
            +"TO_CHAR(NVL(DSAT.SCH_END_DATE,DSAT.SCH_START_DATE),'MM-YYYY') MONTH,DSAT.ITEM_SEGMENTS ITEM_CODE, DSAT.ITEM_DESCRIPTION,"
            +"DSAT.CURRENT_FORECAST_QUANTITY PLAN_QTY FROM APPS.DAW_SCPDB_ASCP_TPP_V   DSAT,APPS.MTL_ITEM_CATEGORIES   MIC,"
            +"APPS.MTL_CATEGORIES      MC"
        + " WHERE NVL(SCH_END_DATE,SCH_START_DATE) BETWEEN TO_DATE(@vfrom,'dd/mm/yyyy') AND TO_DATE(@vto,'dd/mm/yyyy')"+
            " AND  MIC.CATEGORY_SET_ID=1100000061 AND  MC.CATEGORY_ID=MIC.CATEGORY_ID AND  DSAT.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID"
            +" AND  DSAT.ORGANIZATION_ID=MIC.ORGANIZATION_ID"
            +" AND DSAT.FORECAST_SET IN (@vForecastSetDPL1,@vForecastSetDPL2,@vForecastSetURIL)"
            +" AND APPS.DAW_CAT_NAME(MC.SEGMENT4) ='MICRO WAVE OVEN';", con);
        cmd.Parameters.Add("@vfrom",OracleDbType.Varchar2).Value= vfrom;
        cmd.Parameters.Add("@vto",OracleDbType.Varchar2).Value= vto;
        cmd.Parameters.Add("@vForecastSetDPL1", OracleDbType.Varchar2).Value=vForecastSetDPL1;
        cmd.Parameters.Add("@vForecastSetDPL2",OracleDbType.Varchar2).Value=vForecastSetDPL2;
        cmd.Parameters.Add("@vForecastSetURIL",OracleDbType.Varchar2).Value= vForecastSetURIL;

        cmd.CommandType = CommandType.Text;

All the variables used are of String type hence for date i must use TO_DATE.rest of the parameters are supposed to be string type.

Upvotes: 0

Views: 2355

Answers (2)

T.S.
T.S.

Reputation: 19330

Your problem is that in your query you use "@" but for Oracle you need to use ":"

 BETWEEN TO_DATE(@vfrom,'dd/mm/yyyy') - incorrect 
 BETWEEN TO_DATE(:vfrom,'dd/mm/yyyy') - correct

To use Stored procedure you need to create output parameter returning sys_refcursor and everything should be OK

Upvotes: 1

Jasti
Jasti

Reputation: 947

Instead of sending parameters we can build the query first and then execute it. In your code we don't need semicolon in 'MICRO WAVE OVEN';"

    string _currentQuery = "SELECT DSAT.ORGANIZATION_ID, APPS.DAW_INV_ORG_NAME(DSAT.ORGANIZATION_ID) ORGANIZATION_NAME,APPS.DAW_CAT_NAME(MC.SEGMENT4) PRODUCT,TRUNC(NVL(DSAT.SCH_END_DATE,DSAT.SCH_START_DATE)) TRANSACTION_DATE,"
    +"TO_CHAR(NVL(DSAT.SCH_END_DATE,DSAT.SCH_START_DATE),'MM-YYYY') MONTH,DSAT.ITEM_SEGMENTS ITEM_CODE, DSAT.ITEM_DESCRIPTION,"
    +"DSAT.CURRENT_FORECAST_QUANTITY PLAN_QTY FROM APPS.DAW_SCPDB_ASCP_TPP_V DSAT,APPS.MTL_ITEM_CATEGORIES MIC,"
    +"APPS.MTL_CATEGORIES MC"
+ " WHERE NVL(SCH_END_DATE,SCH_START_DATE) BETWEEN TO_DATE('" + vfrom + "','dd/mm/yyyy') AND TO_DATE('" + vto + "','dd/mm/yyyy')" +
    " AND  MIC.CATEGORY_SET_ID=1100000061 AND  MC.CATEGORY_ID=MIC.CATEGORY_ID AND  DSAT.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID"
    +" AND  DSAT.ORGANIZATION_ID=MIC.ORGANIZATION_ID"
    +" AND DSAT.FORECAST_SET IN ('" + vForecastSetDPL1 + "','" + vForecastSetDPL2 + "','" + vForecastSetURIL + "')"
    +" AND APPS.DAW_CAT_NAME(MC.SEGMENT4) ='MICRO WAVE OVEN'";

    OracleCommand cmd = new OracleCommand(_currentQuery,con);

    cmd.CommandType = CommandType.Text;

EDIT

Changes to your code. I have not tested with real data

OracleCommand cmd = new OracleCommand(
            "SELECT DSAT.ORGANIZATION_ID, APPS.DAW_INV_ORG_NAME(DSAT.ORGANIZATION_ID) ORGANIZATION_NAME,APPS.DAW_CAT_NAME(MC.SEGMENT4) PRODUCT,TRUNC(NVL(DSAT.SCH_END_DATE,DSAT.SCH_START_DATE)) TRANSACTION_DATE,"
            +"TO_CHAR(NVL(DSAT.SCH_END_DATE,DSAT.SCH_START_DATE),'MM-YYYY') MONTH,DSAT.ITEM_SEGMENTS ITEM_CODE, DSAT.ITEM_DESCRIPTION,"
            +"DSAT.CURRENT_FORECAST_QUANTITY PLAN_QTY FROM APPS.DAW_SCPDB_ASCP_TPP_V   DSAT,APPS.MTL_ITEM_CATEGORIES   MIC,"
            +"APPS.MTL_CATEGORIES      MC"
        + " WHERE NVL(SCH_END_DATE,SCH_START_DATE) BETWEEN TO_DATE(:vfrom,'dd/mm/yyyy') AND TO_DATE(:vto,'dd/mm/yyyy')"+
            " AND  MIC.CATEGORY_SET_ID=1100000061 AND  MC.CATEGORY_ID=MIC.CATEGORY_ID AND  DSAT.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID"
            +" AND  DSAT.ORGANIZATION_ID=MIC.ORGANIZATION_ID"
            +" AND DSAT.FORECAST_SET IN (:vForecastSetDPL1,:vForecastSetDPL2,:vForecastSetURIL)"
            +" AND APPS.DAW_CAT_NAME(MC.SEGMENT4) ='MICRO WAVE OVEN'", con);
        cmd.Parameters.Add("vfrom",OracleDbType.Varchar2).Value= vfrom;
        cmd.Parameters.Add("vto",OracleDbType.Varchar2).Value= vto;
        cmd.Parameters.Add("vForecastSetDPL1", OracleDbType.Varchar2).Value=vForecastSetDPL1;
        cmd.Parameters.Add("vForecastSetDPL2",OracleDbType.Varchar2).Value=vForecastSetDPL2;
        cmd.Parameters.Add("vForecastSetURIL",OracleDbType.Varchar2).Value= vForecastSetURIL;

        cmd.CommandType = CommandType.Text;

post errors if these solutions doesnt work for you

Upvotes: 1

Related Questions