SQL Query - ORA-01795

Asked

Viewed 137 times

4

I need to make a report from a database query. The problem is that the query returns more than 1000 expressions and presents the following error:

ORA-01795: the maximum number of expressions in a list is 1000.

Follow the query:

        string idsAcoes = string.Join(",\n", listaAcoes.Select(a => a.IdAcao));

        var sql = "SELECT l.LOGM_CD_USUA_OPERACAO, "
                + "  l.LOGM_IN_OPERACAO, "
                + "  l.LOGM_DT_OPERACAO, "
                + "  l.LOGM_TX_DADO_INICIAL, "
                + "  l.LOGM_TX_DADO_FINAL "
                + "FROM LOG_GESTAO_MUDANCA l, ACAO_MUDANCA am "
                + "WHERE l.LOGM_DS_OPERACAO = 'AcaoMudanca' "
                + "  AND l.LOGM_TX_DADO_FINAL LIKE '<AcaoMudanca>%<Id>'||am.ACMU_SQ_ACAO_MDNC||'</Id>%<DataPrazo>%' "
                + "  AND am.ACMU_SQ_ACAO_MDNC IN (" + idsAcoes + ") ";

        resultado = _session
             .CreateSQLQuery(sql)
             .List<dynamic>();
c# sql oracle

  • Related question: https://answall.com/questions/249313/como-resolvero-erro-ora-01795-n%C3%Bamero-m%C3%A1ximo-de-express%C3%B5es-no-codeigniter

  • Best scenario would be a subquery in place of the IN bringing the Ids from its source table. 'Acoes' list brings the Ids from where? It is a select in the same BD?

  • I receive as parameter in the method, but the list comes from a query in the BD.

3 answers

5


  1. Create a temporary table to contain the desired Ids;
  2. Modify your query to reference the temporary table.

Example:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_t_IDS (
    ID         NUMBER
);

Amended WHERE declaration:

[...] AND am.ACMU_SQ_ACAO_MDNC IN (SELECT ID FROM ora$ptt_t_IDS);

Reference:
Private Temporary Tables in Oracle Database 18c
https://oracle-base.com/articles/18c/private-temporary-tables-18c

  • Is there any other solution where it is not necessary to create this temporary table? Because I imagine that I would have to create in all environments (testing, development, homologation and production)

  • 2

    @Gabrielgc maybe if your code is modified to directly query the relevant content sources that, on the C#side, suffer string.join(); the limitation of 1000 parameters, however, is real and not modifiable. Another possibility is the generation of the list via UNION (IN (1..1000) UNION (1001..2000) UNION [...]), but this solution is so subpar that I only mention it as mental exercise.

1

If you create a type parameter CLOB and assign your list as the value of this parameter, you can divide this list in runtime, through a few tricks with CONNECT BY and there will be no need to create a temporary table...

Example:

SELECT TO_NUMBER(RPAD(lst.nro, INSTR(lst.nro || ',', ',') - 1)) valor
  FROM (SELECT SUBSTR(:p_acoes, INSTR(',' || :p_acoes, ',', 1, ROWNUM)) nro
          FROM dual
       CONNECT BY ROWNUM <= LENGTH(:p_acoes) - NVL(LENGTH(REPLACE(:p_acoes, ',')), 0) + 1) lst

Then you put in your IN that query...

SELECT l.logm_cd_usua_operacao,
       l.logm_in_operacao,
       l.logm_dt_operacao,
       l.logm_tx_dado_inicial,
       l.logm_tx_dado_final
  FROM log_gestao_mudanca l, acao_mudanca am
 WHERE l.logm_ds_operacao = 'AcaoMudanca'
   AND l.logm_tx_dado_final LIKE
       '<AcaoMudanca>%<Id>' || am.acmu_sq_acao_mdnc || '</Id>%<DataPrazo>%'
   AND am.acmu_sq_acao_mdnc IN (SELECT TO_NUMBER(RPAD(lst.nro, INSTR(lst.nro || ',', ',') - 1)) valor
                                  FROM (SELECT SUBSTR(:p_acoes, INSTR(',' || :p_acoes, ',', 1, ROWNUM)) nro
                                          FROM dual
                                       CONNECT BY ROWNUM <= LENGTH(:p_acoes) - NVL(LENGTH(REPLACE(:p_acoes, ',')), 0) + 1) lst)

  • I will test this solution and give the return..

  • Researching on, I realized that the most indicated in this situation is to create a temporary table. In terms of performance, can you tell me if there’s a significant difference?

  • It depends on how you will popular this temporary table... You would have to test. Also, with temporary table you will need to have a transaction. Which isn’t really necessary, since you’re only doing one query. Thinking a little about security, this example could be executed by a user who has read-only access.

  • @Brunowarmling There are 2 problems - not with your approach, but with the implementation of drivers for . NET (System.Data.Oracleclient and Oracle.Dataaccess). Large payloads generate ORA-06550/PLS-00172. The first is the parser limit (4kb) in queries, or the stored procedure limit (32kb). Reference: https://stackoverflow.com/questions/3557995/issues-calling-stored-procedure-from-c-sharp-with-large-clob

  • @Onosendai Yes, but these are implementation problems in c# and not query. The error ORA-06550/PLS-00172 only occurs if you pass a parameter greater than 4kb to a type parameter varchar. In this case the parameter in c# must be set to CLOB, as mentioned in the reply. And another, that it is not a good practice to concatenate "values" of parameters in the middle of the query and do it through bindings.

  • 1

    @Exact Brunowarmling - and the questioner specified the C# tag in his question, hence the comment.

Show 1 more comment

-2

Fool the Oracle.

You can take every thousand and join the various Ins with Ors, since the Oracle does not let catch more than a thousand,

const int qtde = 1000;

var sql = "SELECT l.LOGM_CD_USUA_OPERACAO, "
        + "  l.LOGM_IN_OPERACAO, "
        + "  l.LOGM_DT_OPERACAO, "
        + "  l.LOGM_TX_DADO_INICIAL, "
        + "  l.LOGM_TX_DADO_FINAL "
        + "FROM LOG_GESTAO_MUDANCA l, ACAO_MUDANCA am "
        + "WHERE l.LOGM_DS_OPERACAO = 'AcaoMudanca' "
        + "  AND l.LOGM_TX_DADO_FINAL LIKE '<AcaoMudanca>%<Id>'||am.ACMU_SQ_ACAO_MDNC||'</Id>%<DataPrazo>%' "
        + "  AND ( ";

string or = string.Empty;
for (int i = 0; i < listaAcoes.Count; i += qtde)
{
    var milItens = listaAcoes.Select(s => s.IdAcao).Skip(i).Take(qtde);
    string separadosPorVirgula = string.Join(',', milItens);
    sql += or + "am.ACMU_SQ_ACAO_MDNC IN (" + separadosPorVirgula + ")";
    or = " OR ";
}

sql += " )";

resultado = _session
     .CreateSQLQuery(sql)
     .List<dynamic>();

Browser other questions tagged c# sql oracle

You are not signed in. Login or sign up in order to post.