I have a procedure inside a package and I want to implement a logic, which will not insert the temp table into the main table if the temp table has 0 rows, and then go to the next procedure of the package.
IF (not exists(select 1 from temp)) THEN
RETURN;
ELSE
EXECUTE IMMEDIATE 'TRUNCATE TABLE main';
INSERT --+APPEND
INTO main
Select * from temp;
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp';
END IF;
With this solution, the package is compiled with error.
Can anyone give me some tips?
The easiest is to use a variable to check:
--- suggested edit: add condition to select 1 row at most and avoid
-- counting big table.
select count(1) into v_count from temp where rownum <=1;
IF (v_count=0) THEN
RETURN;
ELSE
EXECUTE IMMEDIATE 'TRUNCATE TABLE main';
INSERT --+APPEND
INTO main
Select * from temp;
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp';
END IF;