sa_copy_cursor_to_temp_table system procedure

Creates a temporary table and copies the result set of an open cursor to it.

Syntax

sa_copy_cursor_to_temp_table( 
cursor_name 
, table_name 
[, first_row
[, max_rows ] ]
)

Parameters

cursor_name
Use this VARCHAR(256) parameter to specify the name of the open cursor.
table_name
Use this VARCHAR(256) parameter to specify the name of the temporary table.
first_row
Use this BIGINT parameter to specify the number of the first row to copy to the temporary table. The default is 1.
max_rows
Use this BIGINT parameter to specify the maximum number of rows to copy to the temporary table. The default is 9223372036854775807 (all rows).

Remarks

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:

BEGIN
  CREATE LOCAL TEMPORARY TABLE TempTab (
      col1 INT,
      col2 INT,
  ...
rownum bigint primary key )
END;

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.

Privileges

You must have EXECUTE privilege on the system procedure.

Side effects

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 original position.

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.

Example

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:

BEGIN 
  DECLARE myCursor CURSOR FOR 
      SELECT ID, Name, Description, Color, Quantity FROM Products;
  OPEN myCursor;
  DROP TABLE IF EXISTS myTempTable;
  CALL sa_copy_cursor_to_temp_table( 'myCursor', 'myTempTable' );
  CLOSE myCursor;
  SELECT * FROM myTempTable;
END