Illegal cursor operaton attempt when dynamically creating a temp table

[akenty]

Hi All,
I am dynamically creating a temp table and it's columns and loading a file into it using a stored procedure. Everything is working swimmingly until I do the final select for my result set where I receive an "Illegal cursor operation attempt". Here is the sql for the stored procedure:

CREATE PROCEDURE SP_CREATE_MERGE_TABLE (IN @tab_name varchar(128), @col_string long varchar, @file_name varchar(128))
BEGIN

 /* Create the temptable. */
 EXECUTE IMMEDIATE
	'CREATE GLOBAL TEMPORARY TABLE ' + @tab_name + ' (col_1 long varchar);';
 
 /* Loop through the table and add the column to the temp table. */
 WHILE @loop_position > 0 LOOP	
  
 	SET @col_name = SUBSTR(@col_string, @start_position, (@loop_position - @start_position));
 	
 	/* If the first column, rename the existing to the first column name. */
 	IF @col_count = 1 THEN
    	EXECUTE IMMEDIATE
 	  	'ALTER TABLE ' + @tab_name + ' RENAME col_1 TO ' + @col_name + ';';
	ELSE
    	EXECUTE IMMEDIATE
       	'ALTER TABLE ' + @tab_name + ' ADD ' + @col_name + ' LONG VARCHAR;';
 	END IF;  
	
 	SET @col_count = @col_count + 1;
		
 	/* Get the next position. */
 	SET @start_position = @loop_position + 1;
 	SET @loop_position = LOCATE(@col_string, ',', @start_position);
		
 END LOOP;
 
  /* Now load the passed file into the temp table. */
  EXECUTE IMMEDIATE
        'LOAD TABLE ' + @tab_name + ' FROM '''+ @file_name + ''' FORMAT ASCII' + ' QUOTES ON;';


[b]  This is the final select that is not working

     EXECUTE IMMEDIATE
    	'SELECT lastname FROM ' + @tab_name + ';';    
[/b] 
  
[COLOR=red] This sql worked with no problems (did this as a test).
  /*      EXECUTE IMMEDIATE
    	'UPDATE ' + @tab_name + ' SET lastname = ''Y'';'; */

[/color]

I know the table, columns & data are all loaded correctly. If I were to do a select * from the @tab_name after running the stored procedure, the correct result set would be returned.
Any help is greatly appreciated

 

[akenty]

Found a work around for the problem (developing in Powerbuilder) by excluding the final select from the stored procedure. I set up a dummy DW (in this case, the columns had to be defined as strings) where I modified the select to a SELECT * from the newly created temp table. The table name is known as it is passed to the SP.

SETNULL(ls_mod_string)
ls_mod_string = "DataWindow.Table.Select='SELECT * FROM " + ls_table_name + "'" 

ls_rc = dw_1.Modify(ls_mod_string)

ll_rows = dw_1.Retrieve()

There still may be a way execute the final select from the SP (possibly using a dynamic cursor), but I hunted high & low and could not find anything.
Have fun