I have function like below:FUNCTION
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE WRK_TABLE';
INSERT INTO WRK_TABLE (8 COLUMNS)
SELECT 8 COLUMNS
FROM SRC_TABLE
WHERE CONDITIONS ;
COMMIT;
FOR LOOP
SOME DML OPERATIONS WITH OTHER TABLES
UPDATE WRK_TABLE
SET ONECOLUUMN BASED ON SOME CONDITION
INSERT INTO WRK_TABLE (8 COLUMNS)
SELECT 8 COLUMNS
FROM SRC_TABLE
WHERE CONDITIONS
END LOOP;
FOR
LOOP
UPDATE WRK_TABLE
SET ONECOLUUMN BASED ON SOME CONDITION
INSERT INTO WRK_TABLE (8 COLUMNS)
SELECT 8 COLUMNS
FROM VALUES
END LOOP;
COMMIT;
TRGT_TABLE UPDATION;
TRGT_TABLE INSERTION;
COMMIT;
EXCEPTION
END
While execute the above function,i am getting mentioned error.
WRK_TABLE has public synonym with same name of table and 3 indexes. WRK_TABLE dealing with around 3L record every time.
I know the problem is in WRK_TABLE.
before execute the function ,if i gather stats statement,i am not getting that error.
exec dbms_stats.gather_table_stats('psd_cm','WRK_TABLE',method_opt=>'for all columns size 1')
Can anyone please tell me why this error coming.What's the root cause?