PL/SQL via APEX Refresh Primary Column

Question

I have a query to refresh the data in the table; i.e. purge completely all the data, and update the data. I noticed that the Primary Column number continue to increase. How do I rebuild this Column so that the number starts from 1 every time I refresh the data.

Answer 1

If you are not on 12c, assuming that your primary key is populated via sequence using a trigger. What you can do is :

• Create a trigger with the logic to reset the sequence back to normal, i.e. after every time you purge the table, the sequence would START WITH 1 and INCREMENT BY 1. using ALTER SEQUENCE.

• The sequence logic part using alter statement (Thanks to Tom Kyte for this) :

create or replace
procedure reset_sequence(p_seq in varchar2)
is
    l_value number;
begin
-- Select the next value of the sequence

    execute immediate
    'select ' || p_seq || 
    '.nextval from dual' INTO l_value;

-- Set a negative increment for the sequence, 
-- with value = the current value of the sequence

    execute immediate
    'alter sequence ' || p_seq || 
    ' increment by -' || l_value || ' minvalue 0';

-- Select once from the sequence, to 
-- take its current value back to 0

    execute immediate
    'select ' || p_seq || 
    '.nextval from dual' INTO l_value;

-- Set the increment back to 1

    execute immediate
    'alter sequence ' || p_seq || 
    ' increment by 1 minvalue 0';
end;
/

It is just to answer your question. However, IMHO, I would never like do that in any production system.