Set Up Table

CREATE TABLE plch_flowers
(
   id   INTEGER PRIMARY KEY,
   nm   VARCHAR2 (100) UNIQUE
)

BEGIN
   INSERT INTO plch_flowers
        VALUES (1, 'Orchid');

   INSERT INTO plch_flowers
        VALUES (2, 'Rose');

   COMMIT;
END;

DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id
     INTO l_id
     FROM plch_flowers
    WHERE nm = 'Orchid';

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;

ROLLBACK

DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id INTO l_id FROM plch_flowers;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;

ROLLBACK

BEGIN
   INSERT INTO plch_flowers
      SELECT id * 3, UPPER (nm) FROM plch_flowers;

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;

ROLLBACK

DECLARE
   l_id   INTEGER;
BEGIN
   INSERT INTO plch_flowers
        VALUES (3, 'Tulip');

   SAVEPOINT inserted_row;

   INSERT INTO plch_flowers
        VALUES (3, 'Lotus');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK TO inserted_row;
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;

ROLLBACK

DECLARE
   l_id   INTEGER;

   PROCEDURE insert_and_save
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO plch_flowers
         SELECT id * 3, UPPER (nm) FROM plch_flowers;

      COMMIT;
   END;
BEGIN
   INSERT INTO plch_flowers
        VALUES (10, 'Ambrosia');

   insert_and_save;
   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;