SQL & PL/SQL

Hi all,

How would I display a result set in Oracle using Dynamic SQL? Reason being, that the table from which I wish to retrieve and display the result set is a GLOBAL TEMP TABLE that has been created in a stored procedure. If I try using loop as per usual, the compiler complains that the table does not exist. This makes sense because the compiler doesn't recognise the table seeing that it is created dynamically. Following is an example:

create or replace PROCEDURE maketemptab IS
sql_stmt VARCHAR2(500);
outputstring VARCHAR2(50);

BEGIN
-- create temp table
sql_stmt := 'CREATE GLOBAL TEMPORARY TABLE globtemptab(id NUMBER, col1 VARCHAR2(50))';
EXECUTE IMMEDIATE sql_stmt;
dbms_output.put_line('...table created');

-- insert row into temp table
sql_stmt := 'INSERT INTO globtemptab values (1, ''some test data'')';
EXECUTE IMMEDIATE sql_stmt;
dbms_output.put_line('...row inserted');

-- insert row into temp table
sql_stmt := 'INSERT INTO globtemptab values (2, ''some more test data'')';
EXECUTE IMMEDIATE sql_stmt;
dbms_output.put_line('...row inserted');

-- select row from temp table
sql_stmt := 'SELECT col1 FROM globtemptab WHERE id=1';
EXECUTE IMMEDIATE sql_stmt INTO outputstring;
dbms_output.put_line('...row selected: ' || outputstring);

-- drop temp table
sql_stmt := 'DROP TABLE globtemptab';
EXECUTE IMMEDIATE sql_stmt;
dbms_output.put_line('...table dropped');

-- display result set
for tabdata in (select col1 from globtemptab) loop
dbms_output.put_line('...test data retrieved is' || tabdata.col1 )
end loop;
end;


In summary, how do I rewrite the sql below the "display result set" comment using dynamic sql?

Thanks,
Amedeo.