如何将每 500 个员工 ID 发送到 Oracle 以解析 ORA-07195

本文关键字：Oracle ORA-07195 ID | 更新日期: 2023-09-27 18:31:39

我已经有了这段代码来为每个员工制作字符串生成器，我从另一个表中获取所有员工 ID。但是如果我得到超过 1000 名员工，我会收到错误 ORA-07195 ，我知道此错误与列表中的表达式最大值有关。因此，如何将每 500 名员工发送到数据访问对象中的查询。

Public List<GraphModel> countRequestCreatedByTypeDefaulPage(int year, int month, String employeeID)
    {
        int count = 0;
        int countEmployeess = 0;
        string employeesid = "";
        DataView dv = _employeeOverrideBO.getRelatedEmployees(year, month, employeeID);
        StringBuilder listEmployees = new StringBuilder();
        for (int i = 0; i < countEmployees; i += 500) 
        {
        foreach (DataRowView rowView in dv)
        {
            DataRow row = rowView.Row;
            String employee = row["EMPLOYEE_ID"].ToString();
            if (count > 0)
                listEmployees.Append(",");
            listEmployees.Append("'").Append(employee).Append("'"); 
            count++;
        }
        }
        countEmployeess++;
        employeesid = listEmployees.ToString();
        return _requestDAO.countRequestCreatedByTypeDefaulPage(employeesid);

这也是我在数据访问对象中的查询

public List<GraphModel> countRequestCreatedByTypeDefaulPage(string employeesIds)
    {
        String sql = " select NVL(TO_CHAR(RR.REASON_NM_NEW), 'Total') as SERIES1, count(*) AS VAL" +
                     " from REQUEST R, REQUEST_PERSON RP, REQUEST_REASON RR " +
                     " WHERE R.STATUS IN ('CREATED', 'PENDING APPROVAL', 'APPROVED BY MANAGER', 'APPROVED', 'IN PROCESS') " +
                     " AND R.REQUEST_ID = RP.REQUEST_ID" +
                     " AND RP.REQUEST_ROLE = 'REQUESTOR' " +
                     " AND RR.REASON_ID = R.REASON_ID" +
                     " AND RP.EMPLOYEE_ID IN (" + employeesIds + ") " +
                     " group by rollup (RR.REASON_NM_NEW) " + 
                     " ORDER BY count(*)  DESC";
        OracleCommand cmd = new OracleCommand(sql);

        try
        {
            DataTable dataTable = Data_base_Access.executeSQL(cmd, ConfigurationManager.ConnectionStrings["stage"].ToString());
            return (GraphModel.convertToList(dataTable));
        }
        catch (Exception ex)
        {
            Log.writeError("Request DAO", ex);
            throw new DataAccessException("There was an error counting the open requests");
        }
    }

此外，此查询获取名为 GraphModel 的列表计数

     public static List<GraphModel> convertToList(System.Data.DataTable dataTable)
    {
        List<GraphModel> list = new List<GraphModel>();
        foreach (DataRow dtRow in dataTable.Rows)
        {
            list.Add(convertToGraphModel(dtRow));
        }
        return list;
    }
    public static GraphModel convertToGraphModel(DataRow dtRow)
    {
        GraphModel graphModel = new GraphModel();
        if (dtRow.Table.Columns.Contains("SERIES1") && dtRow["SERIES1"] != DBNull.Value)
        {
            graphModel.SERIES1 = Convert.ToString(dtRow["SERIES1"]);
        }
        if (dtRow.Table.Columns.Contains("SERIES2") && dtRow["SERIES2"] != DBNull.Value)
        {
            graphModel.SERIES2 = Convert.ToString(dtRow["SERIES2"]);
        }
        if (dtRow.Table.Columns.Contains("VAL") && dtRow["VAL"] != DBNull.Value)
        {
            graphModel.VAL = Convert.ToInt32(dtRow["VAL"]);
        }

        return graphModel;
    }
}
我

真的很感谢你的帮助，因为我正在研究很多，我不知道我能做什么

如何将每 500 个员工 ID 发送到 Oracle 以解析 ORA-07195

将列表拆分为 1000 个项目列表，并将查询更改为：

" AND (RP.EMPLOYEE_ID IN (" + ids_1_1000 + ") OR RP.EMPLOYEE_ID IN (" + ids_1001_2000 + "))" +

我最喜欢Oracle的功能之一是Oracle调用接口（OCI），它允许您访问一些更强大的功能或Oracle编程语言。 特别是，对于此示例，进行批量插入的能力应该非常有帮助。

如果不是上面尝试在单个SQL语句中插入数千个文字的方法，而是将这些值放入表中并进行连接，我认为您将：

  1. 使共享池不必编译非常讨厌的 SQL 语句
  2. 无需转义字符串或担心 SQL 注入
  3. 有一个闪电般的快速查询，用联接（数据库的面包和黄油）代替一个巨大的列表

步骤 1：创建 GTT：

create global temporary table employee_list (
  employee_id varchar2(100) not null
) on commit preserve rows;

GTT 基于会话，因此即使您在多个实例中运行此代码，每个 GTT 也将充当每个实例的空白 - 没有数据冲突的可能性。

步骤 2：在代码中，创建一个transaction来处理以下事实：您需要插入到表中，并对数据进行选择，以便作为同一事务的一部分发生：

OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

步骤 3：使用 ODP.net 的批量插入功能一次插入所有员工 ID。 我鼓励您对此进行基准测试，而不是一次插入一个。 你会感到惊讶。 如果你有超过 50,000 个，那么也许你需要将它们分解成块，但对于一个字段，我认为这应该绰绰有余：

// string[] employeesIds
OracleCommand cmd = new OracleCommand("insert into employee_list values (:EMPLOYEE)",
    conn);
cmd.Transaction = trans;
cmd.Parameters.Add(new OracleParameter("EMPLOYEE", OracleDbType.Varchar2));
cmd.Parameters[0].Value = employeesIds;
cmd.ArrayBindCount = employeesIds.Length;
cmd.ExecuteNonQuery();

注意employeeIds应该是一个数组。

步骤 4：将 SQL 从列表内更改为联接：

 select NVL(TO_CHAR(RR.REASON_NM_NEW), 'Total') as SERIES1, count(*) AS VAL
 from
   REQUEST R,
   REQUEST_PERSON RP,
   REQUEST_REASON RR,
   employee_list e       -- added this
 WHERE R.STATUS IN ('CREATED', 'PENDING APPROVAL', 'APPROVED BY MANAGER',
     'APPROVED', 'IN PROCESS')
 AND R.REQUEST_ID = RP.REQUEST_ID
 AND RP.REQUEST_ROLE = 'REQUESTOR'
 AND RR.REASON_ID = R.REASON_ID
 AND RP.EMPLOYEE_ID = e.employee_id  -- changed this
 group by rollup (RR.REASON_NM_NEW)
 ORDER BY count(*)  DESC
以下是

它们在一起的样子：

public List<GraphModel> countRequestCreatedByTypeDefaulPage(string[] employeesIds)
{
    OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
    OracleCommand cmd = new OracleCommand("insert into employee_list values (:EMPLOYEE)",
        conn);
    cmd.Transaction = trans;
    cmd.Parameters.Add(new OracleParameter("EMPLOYEE", OracleDbType.Varchar2));
    cmd.Parameters[0].Value = employeesIds;
    cmd.ArrayBindCount = employeesIds.Length;
    cmd.ExecuteNonQuery();
    String sql = "";  // code from above goes here
    cmd = new OracleCommand(sql, conn);
    cmd.Transaction = trans;
    DataTable dataTable = null;
    try
    {
        dataTable = Data_base_Access.executeSQL(cmd,
            ConfigurationManager.ConnectionStrings["stage"].ToString());
        return (GraphModel.convertToList(dataTable));
    }
    catch (Exception ex)
    {
        Log.writeError("Request DAO", ex);
        throw new DataAccessException("There was an error counting the open requests");
    }
    finally
    {
        trans.Rollback();
    }
    return dataTable;
}

我解决了这段代码的问题。 

public List<GraphModel> countRequestCreatedByTypeDefaulPage(int year, int month, String employeeID)
    {
        int count = 0;
        int countEmployees = 0;
        Dictionary<string, int> dataChart = new Dictionary<string, int>();
        DataView dv = _employeeOverrideBO.getRelatedEmployeesRequests(year, month, employeeID);
        StringBuilder listEmployees = new StringBuilder();
        foreach (DataRowView rowView in dv) 
        {
            if (countEmployees == 500)
            {
                List<GraphModel> listReturn = _requestDAO.countRequestCreatedByTypeDefaulPage(listEmployees.ToString());
                foreach(GraphModel model in listReturn){
                    if (dataChart.ContainsKey(model.SERIES1))
                    {
                        dataChart[model.SERIES1] = dataChart[model.SERIES1] + model.VAL;
                    }
                    else
                    {
                        dataChart[model.SERIES1] = model.VAL;
                    }
                }
                listEmployees = new StringBuilder();
                count = 0;
                countEmployees = 0;
            }
            DataRow row = rowView.Row;
            String employee = row["EMPLOYEE_ID"].ToString();
            if (count > 0)
                listEmployees.Append(",");
            listEmployees.Append("'").Append(employee).Append("'");
            count++;
            countEmployees++;
        }
        //Last Call
        List<GraphModel> listReturnLast = _requestDAO.countRequestCreatedByTypeDefaulPage(listEmployees.ToString());
        foreach (GraphModel model in listReturnLast) {
            if (dataChart.ContainsKey(model.SERIES1))
            {
                dataChart[model.SERIES1] = dataChart[model.SERIES1] + model.VAL;
            }
            else
            {
                dataChart[model.SERIES1] = model.VAL;
            }
        }


        List<GraphModel> list = new List<GraphModel>();
        foreach (KeyValuePair<string, int> entry in dataChart)
        {
            GraphModel model = new GraphModel();
            model.SERIES1 = entry.Key;
            model.VAL = entry.Value;
            list.Add(model);
        }
        return list;
    }
相关文章：