-
Temporary table processing
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
I'm stmontgo and I approve of this message
-
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.
-
I think you are going out of scope when you are using tmp table..
Raghu
-
Have you thought about using PL/SQL collections instead?
-
thls dave for the explanation and gandolf989 for the suggestion
regards
steve
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|