Temporary table processing

[stmontgo]

Good day,
Stuck here...

Why does this work

set serveroutput on
declare my_count INT;
begin
select count(table_name) INTO MY_COUNT from all_tables where table_name = 'MY_TABLE';
dbms_output.put_line(my_count);
IF my_count = 0 then
execute immediate ('create global temporary table my_table(name varchar2(10))');
END IF;
end;
/

BUT then I try and populate the tempoary table from the same procedure i get errors like below

SQL> set serveroutput on
SQL> declare my_count INT;
2 begin
3 select count(table_name) INTO MY_COUNT from all_tables where table_name = 'MY_TABLE';
4 dbms_output.put_line(my_count);
5 IF my_count = 0 then
6 execute immediate ('create global temporary table my_table(name varchar2(10))');
7 commit;
8 END IF;
9 insert into my_table values ('Steve');
10 insert into my_table values ('Mike');
11 insert into my_table values ('Gary');
12 FOR i in (select name from my_table)
13 loop
14 dbms_output.put_line(i.name);
15 end loop;
16 end;
17 /
insert into my_table values ('Steve');
*
ERROR at line 9:
ORA-06550: line 9, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 11, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 27:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 12, column 10:
PL/SQL: SQL Statement ignored
ORA-06550: line 14, column 22:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 14, column 1:
PL/SQL: Statement ignored


Thanks

Steve

[slimdave]

The PL/SQL is being parsed, and is failing because at the time of parsing the temporary table does not exist ... to get this to work you'll either have to pre-create the temp table prior to the insert statements being parsed, or make the sql statements dynamic so that they are not parsed until execution time.

[raghud]

I think you are going out of scope when you are using tmp table..

[gandolf989]

Have you thought about using PL/SQL collections instead?

[stmontgo]

thls dave for the explanation and gandolf989 for the suggestion

regards

steve