CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle10goracle9i
Deep in Development
Deep in Development

Reputation: 495

Oracle compile error when insert into table with a sequence number

I got a compile error when I compile below code within Oracle plsql package. The error is like:

Compilation errors for PACKAGE BODY tttt_KP_ALLOCATION

Error: PL/SQL: ORA-02287: sequence number not allowed here Line: 3465 Text: all_kp_prior_seq.nextval prior_seq_no,

Error: PL/SQL: SQL Statement ignored Line: 3404 Text: insert into all_pat_list

  tttt_sys_pkg.reset_sequence('ALL_KP_PRIOR_SEQ', 1);

  insert into all_pat_list
    (apl_id,
     all_id,
     prod_id,
     prior_seq_no,
     org_code,
     all_step_no,
     pat_id,
     pat_name,
     sex,
     dob,
     ped_ind,
     blood_typ,
     home_prov_code,
     home_town,
     org_code_registered,
     org_descrip_registered,
     hosp_id,
     mrn,
     oldr_code,
     current_wait_list_stat_code,
     init_wait_list_stat_date,
     dis_code_prim_diag,
     dis_code_sec_diag,
     cadaver_liv_ind,
     weight,
     height,
     abdom_girth,
     chest_circum,
     chest_vert_right,
     chest_vert_left,
     chest_transverse,
     cmv,
     ebv,
     hepatitis_b_surface_ant,
     hepatitis_c,
     prev_trnsplt_no,
     all_step_descrip,
     eff_date,
     hcv_rna,
     dsa_cur_unaccp1,
     dsa_acc_unaccp1,
     dsa_cur_indet1,
     dsa_acc_indet1,
     dsa_cur_unaccp2,
     dsa_acc_unaccp2,
     dsa_cur_indet2,
     dsa_acc_indet2,
     x_match_serum_date,
     x_match_b_cell_res_ind,
     x_match_t_cell_res_ind,
     current_pra,
     current_pra_date,
     peak_pra,
     peak_pra_date,
     kp_allocation_points,
     cpra,
     patr_id)
    select apl_id,
           all_id,
           prod_id,
           all_kp_prior_seq.nextval prior_seq_no,
           org_code,
           all_step_no,
           pat_id,
           pat_name,
           sex,
           dob,
           ped_ind,
           blood_typ,
           home_prov_code,
           home_town,
           org_code_registered,
           org_descrip_registered,
           hosp_id,
           mrn,
           oldr_code,
           current_wait_list_stat_code,
           init_wait_list_stat_date,
           dis_code_prim_diag,
           dis_code_sec_diag,
           cadaver_liv_ind,
           weight,
           height,
           abdom_girth,
           chest_circum,
           chest_vert_right,
           chest_vert_left,
           chest_transverse,
           cmv,
           ebv,
           hepatitis_b_surface_ant,
           hepatitis_c,
           prev_trnsplt_no,
           all_step_descrip,
           eff_date,
           hcv_rna,
           dsa_cur_unaccp1,
           dsa_acc_unaccp1,
           dsa_cur_indet1,
           dsa_acc_indet1,
           dsa_cur_unaccp2,
           dsa_acc_unaccp2,
           dsa_cur_indet2,
           dsa_acc_indet2,
           x_match_serum_date,
           x_match_b_cell_res_ind,
           x_match_t_cell_res_ind,
           current_pra,
           current_pra_date,
           peak_pra,
           peak_pra_date,
           kp_allocation_points,
           cpra,
           patr_id
      from temp_kp_all_pat_list_pool
     order by prior_seq_no;

Who may help me out will be great appreciate!!! Please give me the correct code, many thanks.

After read Ed's post, I modify the code like below, it can pass compile now.

tttt_sys_pkg.reset_sequence('ALL_KP_PRIOR_SEQ', 1);

insert into all_pat_list
  (apl_id,
   all_id,
   prod_id,
   prior_seq_no,
   org_code,
   all_step_no,
   pat_id,
   pat_name,
   sex,
   dob,
   ped_ind,
   blood_typ,
   home_prov_code,
   home_town,
   org_code_registered,
   org_descrip_registered,
   hosp_id,
   mrn,
   oldr_code,
   current_wait_list_stat_code,
   init_wait_list_stat_date,
   dis_code_prim_diag,
   dis_code_sec_diag,
   cadaver_liv_ind,
   weight,
   height,
   abdom_girth,
   chest_circum,
   chest_vert_right,
   chest_vert_left,
   chest_transverse,
   cmv,
   ebv,
   hepatitis_b_surface_ant,
   hepatitis_c,
   prev_trnsplt_no,
   all_step_descrip,
   eff_date,
   hcv_rna,
   dsa_cur_unaccp1,
   dsa_acc_unaccp1,
   dsa_cur_indet1,
   dsa_acc_indet1,
   dsa_cur_unaccp2,
   dsa_acc_unaccp2,
   dsa_cur_indet2,
   dsa_acc_indet2,
   x_match_serum_date,
   x_match_b_cell_res_ind,
   x_match_t_cell_res_ind,
   current_pra,
   current_pra_date,
   peak_pra,
   peak_pra_date,
   kp_allocation_points,
   cpra,
   patr_id)
  select apl_id,
         all_id,
         prod_id,
         all_kp_prior_seq.nextval prior_seq_no,
         org_code,
         all_step_no,
         pat_id,
         pat_name,
         sex,
         dob,
         ped_ind,
         blood_typ,
         home_prov_code,
         home_town,
         org_code_registered,
         org_descrip_registered,
         hosp_id,
         mrn,
         oldr_code,
         current_wait_list_stat_code,
         init_wait_list_stat_date,
         dis_code_prim_diag,
         dis_code_sec_diag,
         cadaver_liv_ind,
         weight,
         height,
         abdom_girth,
         chest_circum,
         chest_vert_right,
         chest_vert_left,
         chest_transverse,
         cmv,
         ebv,
         hepatitis_b_surface_ant,
         hepatitis_c,
         prev_trnsplt_no,
         all_step_descrip,
         eff_date,
         hcv_rna,
         dsa_cur_unaccp1,
         dsa_acc_unaccp1,
         dsa_cur_indet1,
         dsa_acc_indet1,
         dsa_cur_unaccp2,
         dsa_acc_unaccp2,
         dsa_cur_indet2,
         dsa_acc_indet2,
         x_match_serum_date,
         x_match_b_cell_res_ind,
         x_match_t_cell_res_ind,
         current_pra,
         current_pra_date,
         peak_pra,
         peak_pra_date,
         kp_allocation_points,
         cpra,
         patr_id
    from (select apl_id,
                 all_id,
                 prod_id,
                 prior_seq_no,
                 org_code,
                 all_step_no,
                 pat_id,
                 pat_name,
                 sex,
                 dob,
                 ped_ind,
                 blood_typ,
                 home_prov_code,
                 home_town,
                 org_code_registered,
                 org_descrip_registered,
                 hosp_id,
                 mrn,
                 oldr_code,
                 current_wait_list_stat_code,
                 init_wait_list_stat_date,
                 dis_code_prim_diag,
                 dis_code_sec_diag,
                 cadaver_liv_ind,
                 weight,
                 height,
                 abdom_girth,
                 chest_circum,
                 chest_vert_right,
                 chest_vert_left,
                 chest_transverse,
                 cmv,
                 ebv,
                 hepatitis_b_surface_ant,
                 hepatitis_c,
                 prev_trnsplt_no,
                 all_step_descrip,
                 eff_date,
                 hcv_rna,
                 dsa_cur_unaccp1,
                 dsa_acc_unaccp1,
                 dsa_cur_indet1,
                 dsa_acc_indet1,
                 dsa_cur_unaccp2,
                 dsa_acc_unaccp2,
                 dsa_cur_indet2,
                 dsa_acc_indet2,
                 x_match_serum_date,
                 x_match_b_cell_res_ind,
                 x_match_t_cell_res_ind,
                 current_pra,
                 current_pra_date,
                 peak_pra,
                 peak_pra_date,
                 kp_allocation_points,
                 cpra,
                 patr_id
            from temp_kp_all_pat_list_pool
           order by prior_seq_no);

Upvotes: 0

Views: 332

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

I just tried something similar, thinking it was the alias assigned to the sequence value. You don't need the alias, but it turns out that's harmless.

The problem? It's the order by. If you get rid of that the error will go away. When doing an INSERT the order shouldn't really matter anyway.

Upvotes: 1

Related Questions