Re: Temporary Tables

From: GovindanK <gkatteri_at_fastmail.fm>
Date: Fri, 26 Aug 2005 11:10:30 -0700
Message-Id: <1125079830.12494.241566281@webmail.messagingengine.com>

One needs to be aware of the default behaviour of global temporary table is on commit delete rows. We had an application requirement where in we needed to select after we inserted rows (in our case we had to user dynamic sql) and ended up with no data found within the same session

SQL>create global temporary table my_log_table 2 (ind number(02) 3 ,log_date date 4 )5 --on commit preserve rows
5 /

Table created.

SQL>DECLARE

  2    l_date  date; 3  begin 4    EXECUTE IMMEDIATE 'insert into
  my_log_table values(03,sysdate)' ; 5    commit; 6    SELECT log_date
  into l_date 7               FROM my_log_table 8              WHERE
  ind=03 and rownum < 2 9            ; 10

dbms_output.put_line(l_date); 11 null; 12 end; 6 /DECLARE * ERROR at line 1: ORA-01403: no data found ORA-

06512: at line 6

SQL>
On Fri, 26 Aug 2005 11:52:23 -0400, "Aggarwal, Meenakshi" <Meenakshi.Aggarwal_at_fishersci.com> said:
> Does anybody know any reason of not using temporary tables in
> Oracle 9i.
>
> Thanks
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 26 2005 - 13:12:33 CDT

Re: Temporary Tables - 1 of 3