Your Pro*COBOL program must log on to Oracle before querying or manipulating data. To log on, you use the CONNECT statement, as in

     EXEC SQL 
         CONNECT :USERNAME IDENTIFIED BY :PASSWD
     END-EXEC. 

where USERNAME and PASSWD are PIC X(n) or PIC X(n) VARYING host variables. Alternatively, you can use the statement:

     EXEC SQL 
         CONNECT :USR-PWD 
     END-EXEC. 

where the host variable USR-PWD contains your username and password separated by a slash (/) followed by an optional tnsnames.ora alias (@TNSALIAS).

The syntax for the CONNECT statement has an optional ALTER AUTHORIZATION clause. The complete syntax for CONNECT is shown here:

     EXEC SQL 
        CONNECT { :user IDENTIFIED BY :oldpswd | :usr_psw }
        [[AT { dbname | :host_variable }] USING :connect_string ]
        [ {ALTER AUTHORIZATION :newpswd  | IN {SYSDBA | SYSOPER} MODE} ]
     END-EXEC.

The CONNECT statement must be the first SQL statement executed by the program. That is, other executable SQL statements can positionally, but not logically, precede the CONNECT statement. If the precompiler option AUTO_CONNECT=YES, a CONNECT statement is not needed.)

To supply the username and password separately, you define two host variables as character strings or VARCHAR variables. If you supply a userid containing both username and password, only one host variable is needed.

Make sure to set the username and password variables before the CONNECT is executed or it will fail. Your program can prompt for the values or you can hard-code them, as follows:

 WORKING STORAGE SECTION. 
     ... 
 01  USERNAME  PIC X(10). 
 01  PASSWD    PIC X(10). 
         ... 
     ... 
 PROCEDURE DIVISION. 
 LOGON. 
     EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC. 
     MOVE "SCOTT" TO USERNAME. 
     MOVE "TIGER" TO PASSWD. 
     EXEC SQL 
         CONNECT :USERNAME IDENTIFIED BY :PASSWD 
     END-EXEC. 

However, you cannot hard-code a username and password into the CONNECT statement or use quoted literals. For example, the following statements are invalid:

     EXEC SQL 
         CONNECT SCOTT IDENTIFIED BY TIGER 
     END-EXEC. 

     EXEC SQL 
         CONNECT "SCOTT" IDENTIFIED BY "TIGER" 
     END-EXEC.

It is possible within a Pro*COBOL program to maintain more than one database connection at the same time.

Before delving into the subject of transactions, you should know the terms defined in this section.

The jobs or tasks that the database manages are called sessions. A user session is started when you run an application program or a tool such as Oracle Forms and connect to the database. Oracle enables user sessions to work simultaneously and share computer resources. To do this, Oracle must control concurrence, the accessing of the same data by many users. Without adequate concurrence controls, there might be a loss of data integrity. That is, changes to data or structures might be made in the wrong order.

Oracle uses locks to control concurrent access to data. A lock gives you temporary ownership of a database resource such as a table or row of data. Thus, data cannot be changed by other users until you finish with it. You need never explicitly lock a resource, because default locking mechanisms protect table data and structures. However, you can request data locks on tables or rows when it is to your advantage to override default locking. You can choose from several modes of locking such as row share and exclusive.

A deadlock can occur when two or more users try to access the same database object. For example, two users updating the same table might wait if each tries to update a row currently locked by the other. Because each user is waiting for resources held by another user, neither can continue until the server breaks the deadlock. The server signals an error to the participating transaction that had completed the least amount of work, and the "deadlock detected while waiting for resource" error code is returned to SQLCODE in the SQLCA.

When a table is queried by one user and updated by another at the same time, the database generates a read consistent view of the table's data for the query. That is, once a query begins (and proceeds), the data read by the query does not change. As update activity continues, the database takes snapshots of the table's data and records changes in a rollback segment. The database uses information in the rollback segment to build read consistent query results and to undo changes if necessary.

The database is transaction oriented; it uses transactions to ensure data integrity. A transaction is a series of one or more logically related SQL statements you define to accomplish some task. The database treats the series of SQL statements as a unit so that all the changes brought about by the statements are either committed (made permanent) or rolled back (undone) at the same time. If your application program fails in the middle of a transaction, the database is automatically restored to its former (pre-transaction) state.

The coming sections show you how to define and control transactions. Specifically, it shows how to:

• Begin and end transactions

• Use the COMMIT statement to make transactions permanent

• Use the SAVEPOINT statement with the ROLLBACK TO statement to undo parts of transactions

• Use the ROLLBACK statement to undo whole transactions

• Specify the RELEASE option to free resources and log off the database

• Use the SET TRANSACTION statement to set read-only transactions

• Use the FOR UPDATE clause or LOCK TABLE statement to override default locking

You begin a transaction with the first executable SQL statement (other than CONNECT) in your program. When one transaction ends, the next executable SQL statement automatically begins another transaction. Thus, every executable statement is part of a transaction. Because they cannot be rolled back and need not be committed, declarative SQL statements are not considered part of a transaction.

You end a transaction in one of the following ways:

• Code a COMMIT or ROLLBACK statement, with or without the RELEASE option. This explicitly makes permanent or undoes changes to the database.

• Code a data definition statement (ALTER, CREATE, or GRANT, for example) that issues an automatic commit before and after executing. This implicitly makes permanent changes to the database.

A transaction also ends when there is a system failure or your user session stops unexpectedly because of software problems, hardware problems, or a forced interrupt.

If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction. If your operating system fails, Oracle restores the database to its former (pre-transaction) state.

The COMMIT statement is used to make changes to the database permanent. Until changes are committed, other users cannot access the changed data; they see it as it was before your transaction began. The COMMIT statement has no effect on the values of host variables or on the flow of control in your program. Specifically, the COMMIT statement:

• Makes permanent all changes made to the database during the current transaction.

• Makes these changes visible to other users.

• Erases all savepoints (see the next section).

• Releases all row and table locks, but not parse locks.

• Closes cursors declared using the FOR UPDATE clause or referenced elsewhere in the code with the CURRENT OF clause. If MODE=ANSI | ANSI14 or CLOSE_ON_COMMIT=YES is used, then all explicit cursors are closed.

• Ends the transaction.

When MODE={ANSI13 | ORACLE}, explicit cursors not referenced in a CURRENT OF clause remain open across commits. This can boost performance. For an example, see "Fetching Across Commits".

Because they are part of normal processing, COMMIT statements should be placed inline, on the main path through your program. Before your program terminates, it must explicitly commit pending changes. Otherwise, Oracle rolls them back. In the following example, you commit your transaction and disconnect:

    EXEC SQL COMMIT WORK RELEASE END-EXEC.

The optional keyword WORK provides ANSI compatibility. The RELEASE option frees all resources (locks and cursors) held by your program and logs off the database.

You need not follow a data definition statement with a COMMIT statement because data definition statements issue an automatic commit before and after executing. So, whether they succeed or fail, the prior transaction is committed.

You use the ROLLBACK statement to undo pending changes made to the database. For example, if you make a mistake, such as deleting the wrong row from a table, you can use ROLLBACK to restore the original data. The ROLLBACK statement has no effect on the values of host variables or on the flow of control in your program. Specifically, the ROLLBACK statement

• Undoes all changes made to the database during the current transaction

• Erases all savepoints

• Ends the transaction

• Releases all row and table locks, but not parse locks

• Closes cursors declared using the FOR UPDATE clause or referenced elsewhere in the code with the CURRENT OF clause. If MODE={ANSI | ANSI14}, then all explicit cursors are closed.

When MODE={ANSI13 | ORACLE}, explicit cursors not referenced in a CURRENT OF clause remain open across rollbacks.

Because they are part of exception processing, ROLLBACK statements should be placed in error handling routines, off the main path through your program. In the following example, you roll back your transaction and disconnect:

    EXEC SQL ROLLBACK WORK RELEASE END-EXEC.

The optional keyword WORK provides ANSI compatibility. The RELEASE option frees all resources held by your program and logs off the database.

If a WHENEVER SQLERROR GOTO statement branches to an error handling routine that includes a ROLLBACK statement, your program might enter an infinite loop if the rollback fails with an error. You can avoid this by coding WHENEVER SQLERROR CONTINUE before the ROLLBACK statement.

For example, consider the following:

     EXEC SQL
         WHENEVER SQLERROR GOTO SQL-ERROR
     END-EXEC.
     ...
     DISPLAY 'Employee number? '.
     ACCEPT EMP-NUMBER.
     DISPLAY 'Employee name? '.
     ACCEPT EMP-NAME.
     EXEC SQL INSERT INTO EMP (EMPNO, ENAME)
        VALUES (:EMP-NUMBER, :EMP-NAME)
     END-EXEC.
     ...
 SQL-ERROR.
     EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
     DISPLAY 'PROCESSING ERROR.'.
     DISPLAY 'ERROR CODE : ', SQLCODE.
     DISPLAY 'MESSAGE :', SQLERRMC.
     EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
     STOP RUN.

Oracle rolls back transactions if your program terminates abnormally.

The SAVEPOINT embedded SQL statement marks and names the current point in processing a transaction. Each marked point is called a savepoint. For example, the following statement marks a savepoint named start_delete:

    EXEC SQL SAVEPOINT start_delete END-EXEC.

Savepoints let you divide long transactions, giving you more control over complex procedures. For example, if a transaction performs several functions, you can mark a savepoint before each function. Then, if a function fails, you can easily restore the data to its former state, recover, and then reexecute the function.

To undo part of a transaction, you can use savepoints with the ROLLBACK statement and its TO SAVEPOINT clause. The TO SAVEPOINT clause lets you roll back to an intermediate statement in the current transaction. With it, you do not have to undo all your changes. Specifically, the ROLLBACK TO SAVEPOINT statement:

• Undoes changes made to the database since the specified savepoint was marked

• Erases all savepoints marked after the specified savepoint

• Releases all row and table locks acquired since the specified savepoint was marked

In the following example, you access the table MAIL_LIST to insert new listings, update old listings, and delete (a few) inactive listings. After the delete, you check SQLERRD(3) in the SQLCA for the number of rows deleted. If the number is unexpectedly large, you roll back to the savepoint start_delete, undoing just the delete.

* -- For each new customer
     DISPLAY 'New customer number? '.
     ACCEPT CUST-NUMBER.
     IF CUST-NUMBER = 0
          GO TO REV-STATUS
     END-IF.
     DISPLAY 'New customer name? '.
          ACCEPT  CUST-NAME.
     EXEC SQL INSERT INTO MAIL-LIST (CUSTNO, CNAME, STAT)
         VALUES (:CUST-NUMBER, :CUST-NAME, 'ACTIVE').
     END-EXEC.
     ...
* -- For each revised status
 REV-STATUS.
     DISPLAY 'Customer number to revise status? '.
     ACCEPT CUST-NUMBER.
     IF CUST-NUMBER = 0
         GO TO SAVE-POINT
     END-IF.
     DISPLAY 'New status? '.
     ACCEPT NEW-STATUS.
     EXEC SQL UPDATE MAIL-LIST
        SET STAT = :NEW-STATUS WHERE CUSTNO = :CUST-NUMBER
     END-EXEC.
     ...
* -- mark savepoint
 SAVE-POINT.
     EXEC SQL SAVEPOINT START-DELETE END-EXEC.
     EXEC SQL DELETE FROM MAIL-LIST WHERE STAT = 'INACTIVE'
     END-EXEC.
     IF SQLERRD(3) < 25 
* -- check number of rows deleted
         DISPLAY 'Number of rows deleted is ', SQLERRD(3)
     ELSE
         DISPLAY 'Undoing deletion of ', SQLERRD(3), ' rows'
         EXEC SQL
             WHENEVER SQLERROR GOTO SQL-ERROR
         END-EXEC
         EXEC SQL
             ROLLBACK TO SAVEPOINT START-DELETE
         END-EXEC
     END-IF.
     EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
     EXEC SQL COMMIT WORK RELEASE END-EXEC.
     STOP RUN.
* -- exit program.
     ...
 SQL-ERROR.
     EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
     EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
     DISPLAY 'Processing error'.
* -- exit program with an error.
     STOP RUN.

Note that you cannot specify the RELEASE option in a ROLLBACK TO SAVEPOINT statement.

Rolling back to a savepoint erases any savepoints marked after that savepoint. The savepoint to which you roll back, however, is not erased. For example, if you mark five savepoints, then roll back to the third, only the fourth and fifth are erased. A COMMIT or ROLLBACK statement erases all savepoints.

Oracle rolls back changes automatically if your program terminates abnormally. Abnormal termination occurs when your program does not explicitly commit or roll back work and disconnect using the RELEASE embedded SQL statement.

Normal termination occurs when your program runs its course, closes open cursors, explicitly commits or rolls back work, disconnects, and returns control to the user. Your program will exit gracefully if the last SQL statement it executes is either

     EXEC SQL COMMIT WORK RELEASE END-EXEC. 

or

     EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
 

where the token WORK is optional. Otherwise, locks and cursors acquired by your user session are held after program termination until Oracle recognizes that the user session is no longer active. This might cause other users in a multiuser environment to wait longer than necessary for the locked resources.

You can use the SET TRANSACTION statement to begin a read-only or read/write transaction, or to assign your current transaction to a specified rollback segment. A COMMIT, ROLLBACK, or data definition statement ends a read-only transaction.

Because they allow "repeatable reads," read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. During a read-only transaction, all queries refer to the same snapshot of the database, providing a multitable, multiquery, read-consistent view. Other users can continue to query or update data as usual. An example of the SET TRANSACTION statement follows:

     EXEC SQL SET TRANSACTION READ ONLY END-EXEC.
 

The SET TRANSACTION statement must be the first SQL statement in a read-only transaction and can appear only once in a transaction. The READ ONLY parameter is required. Its use does not affect other transactions. Only the SELECT (without FOR UPDATE), LOCK TABLE, SET ROLE, ALTER SESSION, ALTER SYSTEM, COMMIT, and ROLLBACK statements are allowed in a read-only transaction.

In the following example, a store manager checks sales activity for the day, the past week, and the past month by using a read-only transaction to generate a summary report. The report is unaffected by other users updating the database during the transaction.

     EXEC SQL SET TRANSACTION READ ONLY END-EXEC. 
     EXEC SQL SELECT SUM(SALEAMT) INTO :DAILY FROM SALES 
         WHERE SALEDATE = SYSDATE END-EXEC.
     EXEC SQL SELECT SUM(SALEAMT) INTO :WEEKLY FROM SALES
         WHERE SALEDATE > SYSDATE - 7 END-EXEC.
     EXEC SQL SELECT SUM(SALEAMT) INTO :MONTHLY FROM SALES 
         WHERE SALEDATE > SYSDATE - 30 END-EXEC. 
     EXEC SQL COMMIT WORK END-EXEC. 
* --  simply ends the transaction since there are no changes 
* --  to make permanent 
* --  format and print report 

By default, Oracle implicitly (automatically) locks many data structures for you. However, you can request specific data locks on rows or tables when it is to your advantage to override default locking. Explicit locking lets you share or deny access to a table for the duration of a transaction or ensure multitable and multiquery read consistency.

With the SELECT FOR UPDATE OF statement, you can explicitly lock specific rows of a table to make sure they do not change before an update or delete is executed. However, Oracle automatically obtains row-level locks at update or delete time. So, use the FOR UPDATE OF clause only if you want to lock the rows before the update or delete.

You can explicitly lock entire tables using the LOCK TABLE statement.

If you want to mix commits and fetches, do not use the CURRENT OF clause. Instead, select the ROWID of each row, and then use that value to identify the current row during the update or delete. Consider the following example:

     EXEC SQL DECLARE emp_cursor CURSOR FOR
         SELECT ENAME, SAL, ROWID FROM EMP WHERE JOB = 'CLERK'
     END-EXEC.
     ...
     EXEC SQL OPEN emp_cursor END-EXEC.
     EXEC SQL WHENEVER NOT FOUND GOTO ...
     PERFORM
     EXEC SQL
         FETCH emp_cursor INTO :EMP_NAME, :SALARY, :ROW-ID
     END-EXEC
     ... 
         EXEC SQL UPDATE EMP SET SAL = :NEW-SALARY
             WHERE ROWID = :ROW-ID
         END-EXEC
         EXEC SQL COMMIT END-EXEC
     END-PERFORM.

Note, however, that the fetched rows are not locked. So, you can receive inconsistent results if another user modifies a row after you read it but before you update or delete it.

A distributed database is a single logical database comprising multiple physical databases at different nodes. A distributed statement is any SQL statement that accesses a remote node using a database link. A distributed transaction includes at least one distributed statement that updates data at multiple nodes of a distributed database. If the update affects only one node, the transaction is non-distributed.

When you issue a commit, changes to each database affected by the distributed transaction are made permanent. If instead you issue a rollback, all the changes are undone. However, if a network or machine fails during the commit or rollback, the state of the distributed transaction might be unknown or in doubt. In such cases, if you have FORCE TRANSACTION system privileges, you can manually commit or roll back the transaction at your local database by using the FORCE clause. The transaction must be identified by a quoted literal containing the transaction ID, which can be found in the data dictionary view DBA_2PC_PENDING. Some examples follow:

     EXEC SQL COMMIT FORCE '22.31.83' END-EXEC.
     ...
     EXEC SQL ROLLBACK FORCE '25.33.86'END-EXEC.

FORCE commits or rolls back only the specified transaction and does not affect your current transaction. Note that you cannot manually roll back in-doubt transactions to a savepoint.

The COMMENT clause in the COMMIT statement lets you specify a Comment to be associated with a distributed transaction. If ever the transaction is in doubt, the server stores the text specified by COMMENT in the data dictionary view DBA_2PC_PENDING along with the transaction ID. The text must be a quoted literal of no more than  50 characters in length. An example follows:

     EXEC SQL
         COMMIT COMMENT 'In-doubt trans; notify Order Entry'
     END-EXEC.

The following guidelines will help you avoid some common problems.