How to write an SQL statement that runs only if condition is fulfilled?

I want to write a procedure that copies records from SYS.AUD$ to AUDIT_USER.AUDIT_HISTORY_TABLE and then TRUNCATE SYS.AUD$. I want Truncate to be executed ONLY if copy / insert went well.

So something like this:

CREATE OR REPLACE PROCEDURE proc_copy_records_and_then_delete
    IS
    BEGIN
     DECLARE number_of_records NUMBER(900000000) :=SELECT COUNT(*) FROM SYS.AUD$;
     insert into AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; 
     IF number_of_records <= rows_inserted THEN
     COMMIT;
     TRUNCATE TABLE SYS.AUD$;
     /

So I just want to TRUNCATE TABLE SYS.AUD$ only if records were INSERTED into the other table.

Answer

you can try to do it that way. i didn’t test this solution, but it should give you an idea how you could do it

CREATE OR REPLACE PROCEDURE proc_copy_records_and_then_delete
IS
  v_row_count number;
BEGIN
  insert into AUDIT_USER.AUDIT_HISTORY_TABLE 
   select * from SYS.AUD$; 
  v_row_count := SQL%ROWCOUNT ;
  IF v_row_count > 0 THEN -- check if some rows where inserted
    INSERT INTO LOG_TABLE (TEXT) VALUES (v_row_count); 
    COMMIT;
    execute immediate 'truncate table SYS.AUD$';

  end if;
end;
/