create table dropme_t(id number, action varchar2(50));
Table created
insert into dropme_t values(1,'Initial');
1 row inserted

commit;
Commit complete

create or replace procedure dropme_a(i_param varchar2 default null) as
begin
  insert into dropme_t values(2,'Inserted by a()');
  if initcap(i_param) like 'Explode' then raise invalid_number; end if;
  update dropme_t set action = action||' Updated by a()';
end;
/
Procedure created
sho err

No errors for PROCEDURE DEVELOPER.DROPME_A
create or replace procedure dropme_b(i_param varchar2 default null) as
begin
  insert into dropme_t values(3,'Inserted by b()');
  update dropme_t set action = action||' Updated by b()' where mod(id,2)=1;
end;
/
Procedure created
sho err
No errors for PROCEDURE DEVELOPER.DROPME_B

select * from dropme_t;
        ID ACTION
---------- --------------------------------------------------
         1 Initial

-- Successive run
begin
  insert into dropme_t values(4,'Inserted by Caller');
  dropme_a();
  dropme_b();
end;
/
PL/SQL procedure successfully completed

select * from dropme_t order by id;
        ID ACTION
---------- --------------------------------------------------
         1 Initial Updated by a() Updated by b()
         2 Inserted by a() Updated by a()
         3 Inserted by b() Updated by b()
         4 Inserted by Caller Updated by a()

rollback;
Rollback complete

select * from dropme_t;
        ID ACTION
---------- --------------------------------------------------
         1 Initial

-- Inconsistent exception handling
begin
  insert into dropme_t values(4,'Inserted by Caller');
--  savepoint s1;
  dropme_a('explode');
  dropme_b();
exception
  when invalid_number then null;
--  when invalid_number then rollback to savepoint s1;
end;
/
PL/SQL procedure successfully completed

select * from dropme_t order by id;
        ID ACTION
---------- --------------------------------------------------
         1 Initial
         2 Inserted by a()
         4 Inserted by Caller

rollback;
Rollback complete

select * from dropme_t;
        ID ACTION
---------- --------------------------------------------------
         1 Initial

-- Correct run
begin
  insert into dropme_t values(4,'Inserted by Caller');
  savepoint s1;
  dropme_a('explode');
  dropme_b();
exception
  when invalid_number then rollback to savepoint s1;
end;
/
PL/SQL procedure successfully completed

select * from dropme_t order by id;
        ID ACTION
---------- --------------------------------------------------
         1 Initial
         4 Inserted by Caller

SQL> 

SQL> explain plan for select dummy from dual where dummy like 'X';
Explained

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4017058736
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DUMMY"='X')
13 rows selected

SQL>