Comments embedded.
On Apr 27, 10:17 am, Dom <[email protected]> wrote:
> "You're not issuing the commit from the session performing the
> insert."
>
> Yep, I am. I'm really stymied.
>
> I'm using SQL Developer. And when I click on TableA, and hit the SQL
> tab, I get the following:
>
> CREATE GLOBAL TEMPORARY TABLE "XXX"."TABLEA"
> (
> ...
> ) ON COMMIT DELETE ROWS ;
>
You've created a global temporary table to store your data, and the
"ON COMMIT DELETE ROWS" does just that -- deletes the temporary rows
you've created because you've told Oracle you're done with them once a
commit is issued:
SQL> create table tableb(
2 myid number,
3 myval varchar2(40));
Table created.
SQL>
SQL> create global temporary table tablea(
2 myid number,
3 myval varchar2(40))
4 on commit delete rows;
Table created.
SQL>
SQL> begin
2 for i in 1..40 loop
3 insert into tableb values (i, 'Testing value '||
i);
4 end loop;
5
6 commit;
7
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from tableb;
MYID MYVAL
---------- ----------------------------------------
1 Testing value 1
2 Testing value 2
3 Testing value 3
4 Testing value 4
5 Testing value 5
6 Testing value 6
7 Testing value 7
8 Testing value 8
9 Testing value 9
10 Testing value 10
11 Testing value 11
MYID MYVAL
---------- ----------------------------------------
12 Testing value 12
13 Testing value 13
14 Testing value 14
15 Testing value 15
16 Testing value 16
17 Testing value 17
18 Testing value 18
19 Testing value 19
20 Testing value 20
21 Testing value 21
22 Testing value 22
MYID MYVAL
---------- ----------------------------------------
23 Testing value 23
24 Testing value 24
25 Testing value 25
26 Testing value 26
27 Testing value 27
28 Testing value 28
29 Testing value 29
30 Testing value 30
31 Testing value 31
32 Testing value 32
33 Testing value 33
MYID MYVAL
---------- ----------------------------------------
34 Testing value 34
35 Testing value 35
36 Testing value 36
37 Testing value 37
38 Testing value 38
39 Testing value 39
40 Testing value 40
40 rows selected.
SQL>
SQL> insert into tablea select * From tableb where rownum <= 10;
10 rows created.
SQL>
SQL> select * From tablea;
MYID MYVAL
---------- ----------------------------------------
1 Testing value 1
2 Testing value 2
3 Testing value 3
4 Testing value 4
5 Testing value 5
6 Testing value 6
7 Testing value 7
8 Testing value 8
9 Testing value 9
10 Testing value 10
10 rows selected.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from tablea;
no rows selected
SQL>
we'll drop the temporary table and recreate it with a different
option:
SQL> drop table tablea;
Table dropped.
SQL>
SQL> create global temporary table tablea(
2 myid number,
3 myval varchar2(40))
4 on commit preserve rows;
Table created.
SQL>
SQL> insert into tablea select * From tableb where rownum <= 10;
10 rows created.
SQL>
SQL> select * From tablea;
MYID MYVAL
---------- ----------------------------------------
1 Testing value 1
2 Testing value 2
3 Testing value 3
4 Testing value 4
5 Testing value 5
6 Testing value 6
7 Testing value 7
8 Testing value 8
9 Testing value 9
10 Testing value 10
10 rows selected.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from tablea;
MYID MYVAL
---------- ----------------------------------------
1 Testing value 1
2 Testing value 2
3 Testing value 3
4 Testing value 4
5 Testing value 5
6 Testing value 6
7 Testing value 7
8 Testing value 8
9 Testing value 9
10 Testing value 10
10 rows selected.
SQL>
> That last line, "ON COMMIT DELETE ROWS", looks suspicious to me. A
> colleague made the table. Should I just remake the table, using
> "create table tablea as select * From tableb where 0=1;" ?
Unless you want a global temporary table then, yes, you should
probably create it as a 'normal' table. Even with the "ON COMMIT
PRESERVE ROWS" option the data will only exist as long as the session
that created it. When the session disconnects the data is gone.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
