Thread: Global temporary table
05-23-11, 12:16 #1Registered User
- Join Date
- Apr 2011
Unanswered: Global temporary table
I need to insert address (street name, city, zip) lines in a table each column(street_name, city, zip_code) is pull depending on the particular code from (characteristics) table. I have decided to use temp table. in this case i wil insert all values in temp table and then populate the temp table to insert in another table.
I am trying to compile below oracle block. it gives an error as shown Could please help me on below
1 declare 2 type mycur is ref cursor; 3 l_cursor mycur; 4 cursor charc is 5 select attr_id, name from characteristics where attr_id = 1168; 6 stmt varchar2(2000); 7 addr varchar2(30); 8 l_id pe_characteristics.attr_id%type; 9 begin 10 for charc_rec in charc loop 11 select rc.code_long_desc into addr 12 from ref_code rc, 13 ref_char irc 14 where rc.attr_id = charc_rec.attr_id 15 and irc.client_cd = rc.client_cd 16 and irc.attr_id = rc.attr_id 17 and irc.inv_ref_id = 'PICO1000011207' 18 and irc.value = rc.code_value 19 and rc.data_Set_id = 99999 20 and irc.data_set_id = rc.data_set_id ; 21 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID varChar2(15), name varchar2(30)) ON COMMIT delete rows'; 22 stmt := 'INSERT INTO tt_Local SELECT charc_rec.arrt_id, addr from dual'; 23 execute immediate stmt; 24 ---select * from tt_local; 25 end loop; 26 open l_cursor for 'select id from tt_Local'; 27 loop 28 fetch l_cursor into l_id; 29 exit when l_cursor%notfound; 30 dbms_output.put_line(l_id); 31 end loop; 32 close l_cursor; 33* end; SQL> / Declare ERROR at line 1: ORA-00942: table or view does not exist ORA- 06512: at line 26
Last edited by andrewst; 05-24-11 at 07:49.
05-23-11, 13:54 #2Lost Boy
Provided Answers: 4
- Join Date
- Jan 2004
- Croatia, Europe
Why did you choose to create a global temporary table (and, above all, using it that way)? You should create it once and use many times (i.e. no create - use - drop - create - use - drop etc. but create - use - use - use - etc.). Doing so, you'd avoid awkward situations, errors and doubts.
If your previous background is different from Oracle (such as MS SQL Server), well, Oracle works differently. It might be that you don't need a temporary table at all.
Reading your code, I'd say that you don't need PL/SQL as well. It can be rewritten into a single SQL INSERT statement (which would most probably beat your PL/SQLs performance by far). Something like (pseudocode)Code:
insert into target_table (col1, col2, ...) select col_1, col_2 from source_table where ...
05-24-11, 13:35 #3Registered User
- Join Date
- Mar 2010
- Vienna, Austria
first of all, Littlefoot is absolutely right in everything he wrote in his post, including the advise not to use PL/SQL when you can do it in SQL.
But aside from that, your code could serve as example for how NOT to do it:
1.) As Littlefoot pointed out, the DROP/CREATE of temporary tables is a performance hog
2.) doing DDL in a program unit (and, even worse: in a loop) should be avoided when possible because it causes the current transaction to commit (which causes the records in the temp table to be deleted according to your create statement).
3.) Think before you code:
3.a.) What, if your "charc" cursor doesn't return any row (which probably caused your ORA-00942) ? In line 26 you are referencing a table, that was never created
3.b.) What, if your "charc" cursor returns more than 1 row (your program will terminate in row 21 with ORA-00955) ?
4.) You are generating unnecessary context switches between the PL/SQL and the SQL machine when doing your insert by "EXECUTE IMMEDIATE" instead of just using an INSERT statement.
5.) If you expect more than a couple of rows from your "charc" cursor, you should avoid looping over a cursor and use "BULK COLLECT" and "FOR ALL" statements instead."There is always an easy solution to every problem - neat, plausible, and wrong."
-- H.L. Mencken