alter statements failing on oracle

Question

I'm new to Oracle (have a SQL Server background). I'm trying to execute this anonymous block but it is failing with ORA-06550. I feel like that I may not understand what can be done and not done in an anonymous block. If someone would be so kind to educate me, I'd appreciate it.

BEGIN
     ALTER TABLE <SCHEMA_NAME>.<TABLE_1_NAME> MODIFY <FIELD_NAME> VARCHAR2(50);
     ALTER TABLE <SCHEMA_NAME>.<TABLE_2_NAME> MODIFY <FIELD_NAME> VARCHAR2(50);
     ALTER TABLE <SCHEMA_NAME>.<TABLE_3_NAME> MODIFY <FIELD_NAME> VARCHAR2(50);
END;

Error:

ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma
raise return select update while with
<<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
json_exists json_value json_query json_object json_array

The symbol "lock was inserted before "ALTER" to continue.

ORA-06550: line 2, column 49:
PLS-00103: Encountered the symbol "" when expecting one of the following:

, in
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Answer 1

If it, for some reason, has to be PL/SQL, you'd need to execute is as dynamic SQL, using execute immediate. For example:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FNAME                                              VARCHAR2(10)     --> 10
 LNAME                                              VARCHAR2(10)

SQL> begin
  2    execute immediate 'alter table test modify fname varchar2(20)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FNAME                                              VARCHAR2(20)     --> 20
 LNAME                                              VARCHAR2(10)

SQL>

Answer 2

You cannot execute a DDL statement inside a PL/SQL block. Remove the BEGIN-END statements, and execute the alter statements in plain SQL:

ALTER TABLE <SCHEMA_NAME>.<TABLE_1_NAME> MODIFY <FIELD_NAME> VARCHAR2(50);
ALTER TABLE <SCHEMA_NAME>.<TABLE_2_NAME> MODIFY <FIELD_NAME> VARCHAR2(50);
ALTER TABLE <SCHEMA_NAME>.<TABLE_3_NAME> MODIFY <FIELD_NAME> VARCHAR2(50);

As a best practice, you should avoid PL/SQL for DDL statements. PL/SQL is a procedural language, while you don't regularly create/drop/modify objects on the fly. There could be specific scenarios where you might have to do it in PL/SQL as scripts, you could use dynamic SQL inside a PL/SQL block as suggested by @Littlefoot.