On Apr 27, 8:58 am, Michael Moore <michaeljmo...@gmail.com> wrote: > That's not the way it should work. After you run the INSERT you should be > able to do a SELECT, and see the records in table A. Other sessions will NOT > see those records in TABLE A until you do the commit. > > I am guessing that you've got multiple sessions open (multiple sqlplus > windows or multiple sql developer windows) and that your results are due to > testing from different sessions. > > Make sure you are issuing all of your commands from the same session > (window). > > Mike > > > > On Wed, Apr 27, 2011 at 8:15 AM, Dom <dolivas...@gmail.com> wrote: > > I'm new to Oracle -- I've always used SQL Server, with autocommit > > turned on. My problem is the following two statements: > > > 1. insert into TableA (select * from TableB where rownum < 10); > > 2. commit; > > > After I do the first, I can see the records TableA. Then I do the > > second command, and suddenly the records disappear. Any ideas? > > > -- > > You received this message because you are subscribed to the Google > > Groups "Oracle PL/SQL" group. > > To post to this group, send email to Oracle-PLSQL@googlegroups.com > > To unsubscribe from this group, send email to > > oracle-plsql-unsubscr...@googlegroups.com > > For more options, visit this group at > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - > > - Show quoted text -
I agree with Mike; a commit in the same session as the transaction will preserve the transaction, not erase it: SQL> create table tableb as select * From all_objects where rownum <=500; Table created. SQL> SQL> create table tablea as select * From tableb where 0=1; Table created. SQL> SQL> insert into TableA (select * from TableB where rownum < 10); 9 rows created. SQL> SQL> select owner, object_name, object_id From tablea; OWNER OBJECT_NAME OBJECT_ID ------------------------------ ------------------------------ ---------- SYS DUAL 258 PUBLIC DUAL 259 SYS SYSTEM_PRIVILEGE_MAP 311 PUBLIC SYSTEM_PRIVILEGE_MAP 313 SYS TABLE_PRIVILEGE_MAP 314 PUBLIC TABLE_PRIVILEGE_MAP 316 SYS STMT_AUDIT_OPTION_MAP 317 PUBLIC STMT_AUDIT_OPTION_MAP 319 SYS KOTTD 334 9 rows selected. SQL> SQL> commit; Commit complete. SQL> SQL> select owner, object_name, object_id From tablea; OWNER OBJECT_NAME OBJECT_ID ------------------------------ ------------------------------ ---------- SYS DUAL 258 PUBLIC DUAL 259 SYS SYSTEM_PRIVILEGE_MAP 311 PUBLIC SYSTEM_PRIVILEGE_MAP 313 SYS TABLE_PRIVILEGE_MAP 314 PUBLIC TABLE_PRIVILEGE_MAP 316 SYS STMT_AUDIT_OPTION_MAP 317 PUBLIC STMT_AUDIT_OPTION_MAP 319 SYS KOTTD 334 9 rows selected. SQL> SQL> truncate table tablea; Table truncated. SQL> SQL> insert into TableA (select * from TableB where rownum < 10); 9 rows created. SQL> SQL> select owner, object_name, object_id From tablea; OWNER OBJECT_NAME OBJECT_ID ------------------------------ ------------------------------ ---------- SYS DUAL 258 PUBLIC DUAL 259 SYS SYSTEM_PRIVILEGE_MAP 311 PUBLIC SYSTEM_PRIVILEGE_MAP 313 SYS TABLE_PRIVILEGE_MAP 314 PUBLIC TABLE_PRIVILEGE_MAP 316 SYS STMT_AUDIT_OPTION_MAP 317 PUBLIC STMT_AUDIT_OPTION_MAP 319 SYS KOTTD 334 9 rows selected. SQL> SQL> pause Here we start a second session, query tablea, commit, then query tablea again: SQL> select * From tablea; no rows selected SQL> SQL> commit; Commit complete. SQL> SQL> select * from tablea; no rows selected SQL> We go back to the original session, issue a commit then query tablea: SQL> SQL> commit; Commit complete. SQL> SQL> select owner, object_name, object_id From tablea; OWNER OBJECT_NAME OBJECT_ID ------------------------------ ------------------------------ ---------- SYS DUAL 258 PUBLIC DUAL 259 SYS SYSTEM_PRIVILEGE_MAP 311 PUBLIC SYSTEM_PRIVILEGE_MAP 313 SYS TABLE_PRIVILEGE_MAP 314 PUBLIC TABLE_PRIVILEGE_MAP 316 SYS STMT_AUDIT_OPTION_MAP 317 PUBLIC STMT_AUDIT_OPTION_MAP 319 SYS KOTTD 334 9 rows selected. SQL> We return to the second session and issue the same query as above; this time we see data: SQL> select owner, object_name, object_id From tablea; OWNER OBJECT_NAME OBJECT_ID ------------------------------ ------------------------------ ---------- SYS DUAL 258 PUBLIC DUAL 259 SYS SYSTEM_PRIVILEGE_MAP 311 PUBLIC SYSTEM_PRIVILEGE_MAP 313 SYS TABLE_PRIVILEGE_MAP 314 PUBLIC TABLE_PRIVILEGE_MAP 316 SYS STMT_AUDIT_OPTION_MAP 317 PUBLIC STMT_AUDIT_OPTION_MAP 319 SYS KOTTD 334 9 rows selected. SQL> You're not issuing the commit from the session performing the insert. 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
