spring jpa本机查询insert ora-01002:提取顺序错误

nlejzf6q  于 2021-06-29  发布在  Java
关注(0)|答案(1)|浏览(309)

嗨，我有一个带有本机查询的存储库类，当我从控制台运行它时，它正常工作。但是当我从spring启动类运行它时，我得到一个错误msg=ora-01002:fetchoutofsequence错误

@Modifying
@Transactional
@Query(value = "insert into TBL_WARH_REMITTANCE_DETAIL\n" +
        "(id, c_created_by, d_created_date, n_version, n_e_status, b_editable, n_amount,\n" +
        " f_depot_id,  f_inventory_id,\n" +
        " f_remittance_id,  f_source_tozine_id, f_unit_id, n_weight)\n" +
        "with initial_criteria as (\n" +
        "    select * from TMP_RD_INV_T where TARGETID = :target_id and FIRST_SOURCEID in :source_id_list\n" +
        "),\n" +
        "     base_on_sum_tmp as (select SUM_WEIGHT SUM_WEIGHT, DAT DAT\n" +
        "                     from initial_criteria\n" +
        "                     where ROWNUM = 1\n" +
        "                       and dat = (select max(DAT)\n" +
        "                                  from initial_criteria\n" +
        "                                  where SUM_WEIGHT < :total_weight)),\n" +
        "      base_on_sum as (\n" +
        "          select SUM_WEIGHT,DAT from base_on_sum_tmp\n" +
        "            union\n" +
        "            select SUM_WEIGHT,dat from (\n" +
        "            select 0 as SUM_WEIGHT,'0' as dat from dual) where ROWNUM=1)\n" +
        "     ,remained as (select :total_weight - (select SUM_WEIGHT from base_on_sum) r from dual)\n" +
        "      ,t_20 as (\n" +
        "         select *\n" +
        "         from initial_criteria\n" +
        "         where N_WEIGHT = 20000\n" +
        "           and dat > (select DAT from base_on_sum)\n" +
        "           and ROWNUM <= FLOOR((select r from remained) / 20000))\n" +
        "     ,t_4 as (\n" +
        "         select *\n" +
        "         from initial_criteria\n" +
        "         where N_WEIGHT = 4000\n" +
        "           and dat > (select DAT from base_on_sum)\n" +
        "           and ROWNUM <= FLOOR(mod((select r from remained), 20000) / 4000)),\n" +
        "     t_2 as (\n" +
        "         select *\n" +
        "         from initial_criteria\n" +
        "         where N_WEIGHT = 2000\n" +
        "           and dat > (select DAT from base_on_sum)\n" +
        "           and ROWNUM <= FLOOR(mod((select r from remained), 4000) / 2000)),\n" +
        "     t_1 as (\n" +
        "         select *\n" +
        "         from initial_criteria\n" +
        "         where N_WEIGHT = 1000\n" +
        "           and dat > (select DAT from base_on_sum)\n" +
        "           and ROWNUM <= FLOOR(mod((select r from remained), 2000) / 1000)),\n" +
        "     t_500_k as (\n" +
        "         select *\n" +
        "         from initial_criteria\n" +
        "         where N_WEIGHT = 500\n" +
        "           and dat > (select DAT from base_on_sum)\n" +
        "           and ROWNUM <= FLOOR(mod((select r from remained), 1000) / 500)),\n" +
        "     under_500_1 as (select *\n" +
        "                     from initial_criteria\n" +
        "                     where dat > (select dat from base_on_sum)\n" +
        "                       and N_WEIGHT <= mod((select r from remained), 500)\n" +
        "                     order by N_WEIGHT desc),\n" +
        "     under_500_2 as (select *\n" +
        "                     from initial_criteria\n" +
        "                     where dat > (select dat from base_on_sum)\n" +
        "                       and ID != (select id from under_500_1 where ROWNUM = 1)\n" +
        "                       and N_WEIGHT <=\n" +
        "                           mod((select r from remained), 500) - (select N_WEIGHT from under_500_1 where ROWNUM = 1)\n" +
        "                     order by N_WEIGHT desc),\n" +
        "     under_500_3 as (select *\n" +
        "                     from initial_criteria\n" +
        "                     where dat > (select dat from base_on_sum)\n" +
        "                       and ID != (select id from under_500_1 where ROWNUM = 1)\n" +
        "                       and ID != (select id from under_500_2 where ROWNUM = 1)\n" +
        "                       and N_WEIGHT <=\n" +
        "                           mod((select r from remained), 500) - (select N_WEIGHT from under_500_1 where ROWNUM = 1) -\n" +
        "                           (select N_WEIGHT from under_500_2 where ROWNUM = 1)\n" +
        "                     order by N_WEIGHT desc)\n" +
        "        ,\n" +
        "     result as (\n" +
        "         select *\n" +
        "         from under_500_1\n" +
        "         where ROWNUM = 1\n" +
        "         union\n" +
        "         select *\n" +
        "         from under_500_2\n" +
        "         where ROWNUM = 1\n" +
        "         union\n" +
        "         select *\n" +
        "         from under_500_3\n" +
        "         where ROWNUM = 1\n" +
        "         union\n" +
        "         select *\n" +
        "         from initial_criteria\n" +
        "         where DAT <= (select dat from base_on_sum)\n" +
        "         union\n" +
        "         select *\n" +
        "         from t_20\n" +
        "         union\n" +
        "         select *\n" +
        "         from t_4\n" +
        "         union\n" +
        "         select *\n" +
        "         from t_2\n" +
        "         union\n" +
        "         select *\n" +
        "         from t_1\n" +
        "         union\n" +
        "         select *\n" +
        "         from t_500_k\n" +
        "     )" +
        "--      id, c_created_by, d_created_date, n_version, n_e_status, b_editable, n_amount,\n" +
        "--      f_depot_id,  f_inventory_id,\n" +
        "--      f_remittance_id,  f_source_tozine_id, f_unit_id, n_weight\n" +
        "select\n" +
        "SEQ_WARH_REMITTANCE_DETAIL.nextval,\n" +
        "       :created_by,current_timestamp,0,1,1,N_AMOUNT,F_DEPOT_ID,id,:remittance_id,:source_tozin_id,F_UNIT_ID,N_WEIGHT\n" +
        "from result\n", nativeQuery = true)
List<List<Long>> remittanceDetailByWeight(@Param("total_weight") Long weight,
                                    @Param("source_id_list") List<Long> sourceIdList,
                                    @Param("target_id") Long targetId,
                                    @Param("created_by") String createdBy,
                                    @Param("remittance_id") Long remittanceId,
                                    @Param("source_tozin_id") Long sourceTozinId

);

当我在控制台中运行它时，它可以正常工作，

insert into TBL_WARH_REMITTANCE_DETAIL
(id, c_created_by, d_created_date, n_version, n_e_status, b_editable, n_amount,
 f_depot_id,  f_inventory_id,
 f_remittance_id,  f_source_tozine_id, f_unit_id, n_weight)
with initial_criteria as (
    select * from TMP_RD_INV_T where TARGETID = 2555  and FIRST_SOURCEID in (1540,1541,1000 )
),
     base_on_sum_tmp as (select SUM_WEIGHT SUM_WEIGHT, DAT DAT
                         from initial_criteria
                         where ROWNUM = 1
                           and dat = (select max(DAT)
                                      from initial_criteria
                                      where SUM_WEIGHT < 39870 )),
     base_on_sum as (
         select SUM_WEIGHT,DAT from base_on_sum_tmp
         union
         select SUM_WEIGHT,dat from (
                                        select 0 as SUM_WEIGHT,'0' as dat from dual) where ROWNUM=1)
        ,remained as (select 39870  - (select SUM_WEIGHT from base_on_sum) r from dual)
        ,t_20 as (
    select *
    from initial_criteria
    where N_WEIGHT = 20000
      and dat > (select DAT from base_on_sum)
      and ROWNUM <= FLOOR((select r from remained) / 20000))
        ,t_4 as (
    select *
    from initial_criteria
    where N_WEIGHT = 4000
      and dat > (select DAT from base_on_sum)
      and ROWNUM <= FLOOR(mod((select r from remained), 20000) / 4000)),
     t_2 as (
         select *
         from initial_criteria
         where N_WEIGHT = 2000
           and dat > (select DAT from base_on_sum)
           and ROWNUM <= FLOOR(mod((select r from remained), 4000) / 2000)),
     t_1 as (
         select *
         from initial_criteria
         where N_WEIGHT = 1000
           and dat > (select DAT from base_on_sum)
           and ROWNUM <= FLOOR(mod((select r from remained), 2000) / 1000)),
     t_500_k as (
         select *
         from initial_criteria
         where N_WEIGHT = 500
           and dat > (select DAT from base_on_sum)
           and ROWNUM <= FLOOR(mod((select r from remained), 1000) / 500)),
     under_500_1 as (select *
                     from initial_criteria
                     where dat > (select dat from base_on_sum)
                       and N_WEIGHT <= mod((select r from remained), 500)
                     order by N_WEIGHT desc),
     under_500_2 as (select *
                     from initial_criteria
                     where dat > (select dat from base_on_sum)
                       and ID != (select id from under_500_1 where ROWNUM = 1)
                       and N_WEIGHT <=
                           mod((select r from remained), 500) - (select N_WEIGHT from under_500_1 where ROWNUM = 1)
                     order by N_WEIGHT desc),
     under_500_3 as (select *
                     from initial_criteria
                     where dat > (select dat from base_on_sum)
                       and ID != (select id from under_500_1 where ROWNUM = 1)
                       and ID != (select id from under_500_2 where ROWNUM = 1)
                       and N_WEIGHT <=
                           mod((select r from remained), 500) - (select N_WEIGHT from under_500_1 where ROWNUM = 1) -
                           (select N_WEIGHT from under_500_2 where ROWNUM = 1)
                     order by N_WEIGHT desc)
        ,
     result as (
         select *
         from under_500_1
         where ROWNUM = 1
         union
         select *
         from under_500_2
         where ROWNUM = 1
         union
         select *
         from under_500_3
         where ROWNUM = 1
         union
         select *
         from initial_criteria
         where DAT <= (select dat from base_on_sum)
         union
         select *
         from t_20
         union
         select *
         from t_4
         union
         select *
         from t_2
         union
         select *
         from t_1
         union
         select *
         from t_500_k
     )--      id, c_created_by, d_created_date, n_version, n_e_status, b_editable, n_amount,
--      f_depot_id,  f_inventory_id,
--      f_remittance_id,  f_source_tozine_id, f_unit_id, n_weight
select
    SEQ_WARH_REMITTANCE_DETAIL.nextval,
    'saebb' ,current_timestamp,0,1,1,N_AMOUNT,F_DEPOT_ID,id,39101 ,57015 ,F_UNIT_ID,N_WEIGHT
from result

它工作没有任何问题 [2021-01-03 11:51:32] 9 rows affected in 4 s 662 ms 但是当我从java运行它时，它不会运行
并显示error msg=ora-01002:fetch out of sequence error

import com.nicico.sales.repository.warehouse.RemittanceDetailDAO;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.ArrayList;

@SpringBootTest
@RunWith(value = SpringRunner.class)
public class BatchRemittanceDetailTest {
@Autowired
    private RemittanceDetailDAO remittanceDetailDAO;
@Test
    public void brdt(){
    final ArrayList<Long> objects = new ArrayList<>();
    objects.add(1541L);
    objects.add(1540L);
    objects.add(1000L);
    remittanceDetailDAO.remittanceDetailByWeightCount(39870L,objects,2555L).get(0).get(1);
    remittanceDetailDAO.remittanceDetailByWeight(39870L,objects,2555L,"saebb",
            39101L,57015L);
}

}
Javaspringspring-bootoracle

1条答案

按热度按时间
ep6jt1vc

ep6jt1vc1#

好吧，我删除了我的存储库方法，改用jdbctemplate，这要感谢@mehdibizhani for solution

jdbcTemplate.update(query, new HashMap() {{
                    put("total_weight", request.getTozin().getVazn());
                    put("source_id_list", request.getSourceList());
                    put("target_id", request.getTargetId());
                    put("created_by", savedRemittance.getCreatedBy());
                    put("remittance_id", savedRemittance.getId());
                    put("source_tozin_id", savedTozin.getId());
                }}
赞(0）分享  回复(0）举报  2021-06-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com