Update within anonymous block fails but works out of it

Question

I have this data patch :

ALTER TABLE MY_TABLE ADD new_id number;

DECLARE
  MAX_ID NUMBER;
BEGIN   
  SELECT max(id) INTO MAX_ID FROM some_table;
  EXECUTE IMMEDIATE 'CREATE sequence temp_seq start WITH ' || MAX_ID || ' increment by 1';
  UPDATE MY_TABLE SET new_id = temp_seq.nextval;
  EXECUTE IMMEDIATE 'DROP sequence temp_seq';  
END;
/

This gives me

UPDATE MY_TABLE SET new_id = temp_seq.nextval;
                                       *
ERROR at line 8:
ORA-06550: line 8, column 40:
PL/SQL: ORA-02289: sequence does not exist
ORA-06550: line 8, column 3:
PL/SQL: SQL Statement ignored

But if I move the update outside it works:

DECLARE
  MAX_ID NUMBER;
BEGIN   
  SELECT max(id) INTO MAX_ID FROM some_table;
  EXECUTE IMMEDIATE 'CREATE sequence temp_seq start WITH ' || MAX_ID || ' increment by 1';  
END;
/

UPDATE MY_TABLE SET new_id = temp_seq.nextval;

I can use the second approach but I am curious why update within the block fails.

Answer 1

Because at compilation time that sequence doesn't exist. If you want to use it within the PL/SQL block, you'll have to do that dynamically, just as you created the sequence.