2 Replies Latest reply: Jul 15, 2014 6:04 AM by John O'Toole RSS

    Error creating sdo_pc point cloud: "ORA-02289: sequence does not exist"

    John O'Toole

      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0

       

      I've come across a problem when creating point clouds - every so often I'll get an error about a sequence not existing.

       

      ERROR at line 1:
      ORA-13199: Invalid Parameters for Partition_Table
      ORA-13199: Invalid Parameters for Partition_Table
      ORA-13249: Stmt-Execute Failure: DROP SEQUENCE "MDPCS_a_17745$$$"
      ORA-29400: data cartridge error
      ORA-02289: sequence does not exist
      ORA-06512: at "MDSYS.PRVT_PC", line 3
      ORA-06512: at "MDSYS.PRVT_PC", line 171
      ORA-06512: at "MDSYS.SDO_PC_PKG", line 179
      ORA-06512: at line 17
      
      

       

      I've got a test case which creates a table where we'll put a sdo_pc, a block table and then a loading XYZ table.  We then loop a bunch of times and create a PC for each.

       

       

      Test case setup:

      create table pc_test (
          id integer primary key,
          pc sdo_pc);
      
      create sequence pc_test_id;
         
      create table pc_test_blocks as select * from mdsys.sdo_pc_blk_table;
      
      create table xyz (
          rid number,
          val_d1 number,
          val_d2 number,
          val_d3 number);
      
      insert /*+ append */ into xyz (
          select
              rownum,
              round(dbms_random.value(1,10000),2),
              round(dbms_random.value(1,10000),2),
              round(dbms_random.value(20,50),2)
          from dual
          connect by level <= 1000);
      commit;
      
      
      

       

      Run test case:

      declare
        l_pc sdo_pc;
          l_id pls_integer;   
      begin
          for i in 1..50 loop
              l_pc := sdo_pc_pkg.init(
                  'PC_TEST', 'PC', 'PC_TEST_BLOCKS', 'blk_capacity=500',
                  sdo_geometry(2003, 2157, NULL, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(400000, 500000, 800000, 1000000)),
                  0.005, 3, null);
              l_id := pc_test_id.nextval;
              dbms_output.put_line('PC_TEST id=' || l_id);
              insert into pc_test (id, pc) values (l_id, l_pc);
              sdo_pc_pkg.create_pc(l_pc, 'XYZ');
          end loop;
      end;
      /
      
      

       

      Some of the PCs will create, but eventually it'll blow up with

      ORA-02289: sequence does not exist
      
      

       

      In so far as I can tell, once I call sdo_pc_pkg.create_pc(), Oracle is creating a sequence with a name such as MDPCS_a_1751F$$$.  When it is done creating the blocks, it then tries to drop the sequence.  However the statement to drop the sequence is wrapped in double quotes - so if the sequence name contained any lowercase letters then it will error out when trying to drop it.  The values used between the underscores seem to be hexadecimal - sometimes they're numbers only, sometimes numbers and uppercase letters, sometimes numbers and lowercase letters.

       

      Has anybody else come across this issue?

       

      The issue isn't critical as the PC blocks do seem to be created correctly, so the issues are: (a) my pl/sql block errors out and (b) I'm left with sequences lying around.

      For the former I'll catch the exception and ignore it, for the latter I'll just drop the sequences myself afterwards.