Oracle PL/SQL/PL SQL/Program Block

Anonymous block with error handling

SQL> --Anonymous block with error handling:
SQL>
SQL> DECLARE
  2    x  NUMBER;
  3  BEGIN
  4    x := 10/0;   -- this will cause a predefined exception
  5    y := x + 1;  -- this line will never run
  6  EXCEPTION
  7
  8    WHEN ZERO_DIVIDE THEN  -- this is the handler
  9      X := 0;
 10  END;
 11
 12
 13  /
  y := x + 1;  -- this line will never run
  *
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00321: expression "OGC_Y" is inappropriate as the left hand side of an assignment statement
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL>

Basic PL/SQL program block

SQL>
SQL> DECLARE
  2      x NUMBER;
  3  BEGIN
  4      x := 10;
  5
  6      DBMS_OUTPUT.PUT_LINE("X="||x);
  7  END;
  8  /
X=10
PL/SQL procedure successfully completed.
SQL>

PL SQL block for start

SQL>
SQL> -- start PL SQL block
SQL>
SQL>
SQL> set serveroutput on
SQL> declare
  2    l_number number := 1;
  3  begin
  4    l_number := 1 + 1;
  5    dbms_output.put_line( "1 + 1 = " || to_char( l_number ) || "!" );
  6  exception
  7    when others then
  8      dbms_output.put_line( "We encountered an exception!" );
  9  end;
 10  /
1 + 1 = 2!
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Variable scope in different program block

SQL>
SQL>
SQL> -- variable scope
SQL>
SQL>  declare
  2      parent_number number;
  3    begin
  4      -- parent_number is visible and in scope
  5      parent_number := 1;
  6
  7      declare
  8        child_number number := 2;
  9      begin
 10        -- child_number is visible
 11        dbms_output.put_line("parent + child = " ||
 12                              to_char(parent_number + child_number));
 13      end;
 14
 15      -- child_number is now not visible nor in scope:
 16      child_number := 2;
 17      dbms_output.put_line(child_number);
 18
 19    end;
 20    /
    child_number := 2;
    *
ERROR at line 16:
ORA-06550: line 16, column 5:
PLS-00201: identifier "CHILD_NUMBER" must be declared
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored
ORA-06550: line 17, column 26:
PLS-00201: identifier "CHILD_NUMBER" must be declared
ORA-06550: line 17, column 5:
PL/SQL: Statement ignored

SQL>
SQL>
SQL>