从C#到OracleCommand的SQL命令给出无效字符错误

本文关键字：无效 字符 错误 命令 SQL OracleCommand | 更新日期: 2023-09-27 18:29:36

下面是我试图使用oracle连接字符串在C#代码中运行的命令，一切都很好（相同的查询在toad中平稳运行）。但是，在填充数据集时，会出现错误ORA-00911：无效字符。我不明白为什么，请帮忙。

 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;

所有使用的变量都是字符串类型，因此对于日期，我必须使用TO_date.rest参数应该是字符串类型。

从C#到OracleCommand的SQL命令给出无效字符错误

您的问题是在查询中使用"@"，但对于Oracle，您需要使用"："

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

要使用存储过程，您需要创建返回sys_refcursor的输出参数，并且一切都应该正常。

我们可以先构建查询，然后执行它，而不是发送参数。在您的代码中，'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;

编辑

对代码的更改。我还没有测试过真实的数据

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;

如果这些解决方案不适用于您，则发布错误

相关文章：