Example 1: Illustrating DECLARE and SET TRANSACTION differences

    In the following example, the first executable statement
    following the DECLARE TRANSACTION statement starts a transaction.
    In contrast, the subsequent SET TRANSACTION statement itself
    starts a transaction.

    SQL> DECLARE TRANSACTION READ WRITE NOWAIT;
    SQL> --
    SQL> -- Notice the "no transaction is in progress" message:
    SQL> --
    SQL> SHOW TRANSACTION
    Transaction information:
        Statement constraint evaluation is off

    On the default alias
    Transaction characteristics:
            Nowait
            Read Write
    Transaction information returned by base system:
    no transaction is in progress
      - session ID number is 80
    SQL> --
    SQL> -- The first executable statement following the
    SQL> -- DECLARE TRANSACTION statement starts the transaction.
    SQL> -- In this case, SELECT is the first executable statement.
    SQL> --
    SQL> SELECT LAST_NAME FROM CURRENT_SALARY;
     LAST_NAME
     Toliver
     Smith
     Dietrich
       .
       .
       .
    SQL> --
    SQL> -- Note the transaction inherits the read/write characteristics
    SQL> -- specified in the DECLARE TRANSACTION statement:
    SQL> --
    SQL> SHOW TRANSACTION;

    Transaction information:
        Statement constraint evaluation is off

    On the default alias
    Transaction characteristics:
            Nowait
            Read Write
    Transaction information returned by base system:
    a read-write transaction is in progress
      - updates have not been performed
      - transaction sequence number (TSN) is 416
      - snapshot space for TSNs less than 416 can be reclaimed
      - session ID number is 80
    SQL> --
    SQL> ROLLBACK;
    SQL> --
    SQL> -- Again, notice the "no transaction is in progress" message:
    SQL> --
    SQL> SHOW TRANSACTION;

    Transaction information:
        Statement constraint evaluation is off

    On the default alias
    Transaction characteristics:
            Nowait
            Read Write
    Transaction information returned by base system:
    no transaction is in progress
      - transaction sequence number (TSN) 416 is reserved
      - snapshot space for TSNs less than 416 can be reclaimed
      - session ID number is 80
    SQL> --
    SQL> -- Unlike DECLARE TRANSACTION, the SET TRANSACTION statement
    SQL> -- immediately starts a transaction:
    SQL> --
    SQL> SET TRANSACTION READ ONLY WAIT;
    SQL> --
    SQL> -- Note the transaction characteristics show the
    SQL> -- read-only characteristics:
    SQL> --
    SQL> SHOW TRANSACTION;
    Transaction information:
        Statement constraint evaluation is off

    On the default alias
    Transaction characteristics:
            Wait
            Read only
    Transaction information returned by base system:
    a snapshot transaction is in progress
      - all transaction sequence numbers (TSNs) less than 416 are visible
      - TSN 416 is invisible
      - all TSNs greater than or equal to 417 are invisible
      - session ID number is 80

    Example 2: Using a DECLARE TRANSACTION statement in a context
    file

    The following example shows a context file, test_declares.sql,
    that contains declarations for precompiling source file test.sco:

    DECLARE ALIAS FOR FILENAME personnel;
    DECLARE TRANSACTION READ WRITE
            RESERVING EMPLOYEES FOR PROTECTED WRITE,
                      JOB_HISTORY FOR PROTECTED WRITE,
                      DEPARTMENTS FOR SHARED READ,
                      JOBS FOR SHARED READ;

    The section in the Oracle Rdb Guide to SQL Programming about
    program transportability explains when you may need an SQL
    context file to support a program that includes SQL statements.

    Example 3: Explicitly setting the isolation level in a DECLARE
    TRANSACTION statement

    In this example, you declare the default characteristics for
    a read/write transaction, which includes changing the default
    ISOLATION LEVEL SERIALIZABLE to ISOLATION LEVEL REPEATABLE READ.

    SQL> DECLARE TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;

    Example 4: Reserving a Partition

    SQL> -- Determine the ordinal position of the EMPLOYEES
    SQL> -- partitions.
    SQL> SELECT RDB$MAP_NAME, RDB$AREA_NAME, RDB$ORDINAL_POSITION
    cont> FROM RDB$STORAGE_MAP_AREAS
    cont> WHERE RDB$MAP_NAME='EMPLOYEES_MAP';
     RDB$MAP_NAME                      RDB$AREA_NAME
       RDB$ORDINAL_POSITION
     EMPLOYEES_MAP                     EMPIDS_LOW
                          1
     EMPLOYEES_MAP                     EMPIDS_MID
                          2
     EMPLOYEES_MAP                     EMPIDS_OVER
                          3
    3 rows selected
    SQL> --
    SQL> -- Reserve EMPIDS_MID and EMPIDS_OVER for
    SQL> -- exclusive write.
    SQL> --
    SQL> DECLARE TRANSACTION
    cont> RESERVING EMPLOYEES PARTITION (2,3)
    cont> FOR EXCLUSIVE WRITE;