How do I reset a sequence in Oracle APEX or fill my PK automatically without sequence and trigger, starting from number 1 every time i delete my data?

Question

i have this table

TABLE  "KEYWORD_RSLT" 
   (    "ID" NUMBER PRIMARY KEY, 
    "SESSION_MONTH" VARCHAR2(40) NOT NULL ENABLE, 
    "PATIENT_NAME" VARCHAR2(50)

and i have this procedure that imports data to my table KEYWORD_RSLT.

create or replace PROCEDURE "PR_KEYWORD_SEARCH" (v_patient_id NUMBER, v_keyWord varchar2)
IS

BEGIN
   
    delete from KEYWORD_RSLT;

    insert into KEYWORD_RSLT (SESSION_MONTH, PATIENT_NAME)
        select distinct
            to_char(s.SESSION_DATE, 'MM-YYYY') as SESSION_MONTH,
            p.FIRST_NAME ||' '||p.LAST_NAME as PATIENT_NAME
        from SESSIONS s,
            CLIENTS p
        where s.CLIENTS_ID = p.ID
        and (s.CRITICAL_POINT like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id 
        or s.ACTIONS like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id);
END PR_KEYWORD_SEARCH;

I want my primary key "ID" to take automatically the next available number starting from 1, but when my procedure deletes all data from this table, i want to start again from 1.

I tried with sequence ("SQ_KEYWORD_RSLT_INCREAMENT") and trigger but i can not reset this sequence from a new procedure using this code:

alter sequence SQ_KEYWORD_RSLT_INCREAMENT restart start with 1;

How can i fill my ID automatically from the beginning every time i delete all the data?

Answer 1

You say i can not reset this sequence but you don't say why so I'm assuming that you got an error. It is not possible to execute ddl statements in pl/sql directly, but it can be done using EXECUTE IMMEDIATE.

CREATE SEQUENCE koen_s START WITH 1;

Sequence KOEN_S created.

SELECT koen_s.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         1

BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE koen_s RESTART START WITH 1';
END;
/

PL/SQL procedure successfully completed.

SELECT koen_s.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         1