Oracle Database 12c Enterprise Edition Release 220.127.116.11.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.