Results 1 to 3 of 3
  1. #1
    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> /
    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 08:49.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    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)
    insert into target_table (col1, col2, ...)
      select col_1, col_2
        from source_table
        where ...

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts