The rules that govern the initial scope of a cursor differ, depending on whether the cursor is static or dynamic. However, after a static cursor is opened or a dynamic cursor is declared, the scoping rules for both types of cursors are the same. The rules are as follows:
Until a static cursor is open, its scope is limited to the file where the cursor was declared. Any statement that opens the static cursor must be in this file. After a static cursor is open, its scope is limited to the connection on which the cursor was opened.
A dynamic cursor does not exist until it is declared. After it is declared, its scope is limited to the connection on which it was declared.
A cursor name can be open on more than one connection at a time.
Statements that fetch, update, delete, or close a cursor can appear in files other than the one where the cursor is declared. Such statements, however, must execute on the connection where the cursor was opened.
If you declare a static cursor in an Embedded SQL/C, open the cursor, use it to fetch data, close the cursor, and then do not deallocate it, subsequently declaring a cursor with the same name and DML will not result in an error. At the second declaration, the Embedded SQL/C program simply notices that a cursor of the same name and DML already exists, disregards the second declaration, and reopens the existing cursor. However, if you redeclare a cursor of the same name but with different DML, you may receive an error. Also, if you attempt to open an existing static cursor that has not been closed, you receive the following error:
SQLCODE=(-16843032)
Adaptive Server Error
ct_cursor(OPEN): user api layer: external error: The cursor on this command structure has already been opened.
If you declare a static cursor using the isql utility, open the cursor, use it to fetch data, close the cursor, and then do not deallocate it, subsequently declaring a cursor with the same name and DML results in an isql error. In isql, you must deallocate the existing cursor before you can redeclare it.
If you declare a dynamic cursor in an Embedded SQL/C program or using the isql utility, open the cursor, use it to fetch data, close the cursor, and then do not deallocate it, subsequently declaring a cursor with the same name and DML will result in an error. In an Embedded SQL/C program or in isql, you must first deallocate the existing dynamic cursor before you can redeclare it. If you attempt to declare a dynamic cursor that has already been declared and not yet deallocated, you receive the following error:
SQLCODE=(-16843030)
Adaptive Server Error
ct_dynamic(CURSOR_DECLARE): user api layer: external error: A cursor has already been declared on this command structure.