Some like that is possible in MSSQL but not in Oracle. Simply PL/SQL is compiled language, but not scripting one. Oracle "silently" compiles it into bytecode on background. Your procedure's sp_update_acounts bytecode depends on definition of the table crm_account_stage (in fact it depends on it's object_id). Your "eval" - dynamic sql - drops this object thus it invalidates all the depending objects. They have to be recompiled. But they can not be, because the procedure is running.

This approach is wrong from Oracle's perspective.

For Oracle the rule of thumb is: "Avoid using DDL for business logic".

Try to use: alter table instead of drop and create.

Some like that is possible in MSSQL but not in Oracle. Simply PL/SQL is compiled language, but not scripting one. Oracle "silently" compiles it into bytecode on background. Your procedure's sp_update_acounts bytecode depends on definition of the table crm_account_stage (in fact it depends on it's object_id). Your "eval" - dynamic sql - drops this object thus it invalidates all the depending objects. They have to be recompiled. But they can not be, because the procedure is running.

This approach is wrong from Oracle's perspective.

For Oracle the rule of thumb is: "Avoid using DDL for business logic".

Try to use: alter table instead of drop and create. Or use execute immediate also for merge statement.