Suppose you have a cursor of several integer columns. sa_copy_cursor_to_temp_table creates a temporary table using a statement
in this form:
CREATE LOCAL TEMPORARY TABLE TempTab (
rownum bigint primary key )
sa_copy_cursor_to_temp_table names the columns col1,col2, and so on to avoid duplication of names or difficulty if cursor
columns do not have a well defined name (for example, if they are a complex expression).
Once the temporary table is created, the contents of the open cursor are inserted by moving to the row number indicated by
first_row, and inserting the number of rows indicated by max_rows. After the contents have been inserted into the temporary table, the cursor is re-positioned at its original location.
Copying from the cursor fetches the rows using the cursor's isolation settings. This may acquire locks on rows and have other
effects equivalent to fetching from the cursor.
If concurrent changes are made outside of the current connection and the cursor is not protected from these by materialization
or isolation settings, then it is possible that the cursor will be positioned on a different row after the procedure completes.
For example, if the previous current row of the cursor was deleted, the cursor could be repositioned on the row after the
If an error occurs while copying from the cursor, the cursor enters an invalid state, and further operations on the cursor
fail with an error.
The following batch creates a cursor named myCursor and loads it with data from the Products table. The cursor is then opened
(OPEN statement). A DROP statement drops myTempTable, if it already exists. Calling sa_copy_cursor_to_temp_table creates a
temporary table called myTempTable and copies the contents of myCursor into it. Finally, a SELECT statement returns the data
that was copied into the temporary table from the cursor:
DECLARE myCursor CURSOR FOR
SELECT ID, Name, Description, Color, Quantity FROM Products;
DROP TABLE IF EXISTS myTempTable;
CALL sa_copy_cursor_to_temp_table( 'myCursor', 'myTempTable' );
SELECT * FROM myTempTable;