Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Posts
    20

    Angry Unanswered: Problem with table data disappearing

    I'm using Oracle 9i and Developer Suite. When using SQL Plus, I run a script to create some tables and as long as I keep SQL Plus open, I can work with the tables just fine, but if I close SQL Plus and reopen it, the data in the tables is apparently gone. If I type SELECT * FROM 'TABLE NAME' it comes back with 'no rows selected' every time. I have one bunch of tables that always stays the same, but this other group loses its data every time.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Dumb question but are you sure you are committing the data after inserting it?
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Aug 2004
    Posts
    20
    Quote Originally Posted by ss659
    Dumb question but are you sure you are committing the data after inserting it?
    I guess it wasn't such a dumb question, committing the data seems to have worked. I hadn't thought about it because one other script I ran to create some other tables kept the data without committing. Oh well, thanks for the help.

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Glad it helped!
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The reason could be that once you created and inserted the data of a given table, the next time you submitted a new create table statement again, it would have committed the changes of the previously created/inserted table.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    right, for you information when you do a command like CREATE,DROP, ALTER... it does an implicint commit
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Doesn't closing a SQL session also do an implicit commit?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If the session closes, the default action is to rollback any uncommitted transactions unless you use alter session set (I forget the parameter name) to change default behavor.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Bill,

    My Oracle 9i DBA book states:

    "If a program successfully completes & the user exits gracefully from SQL*Plus, the results are implicitly committed, even without the 'commit' command."

    Does this, then, apply only to DDL statements (and not DML Statements)? The book makes no distinction between the two.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    commit does not apply to ddl - ddl's are self committing. in fact, they commit everything that happened before them (update a table, alter a different table, rollback - the updates are already committed, so the rollback has no effect).

    the manual refers to dml. again, it's when you exit gracefully from sqlplus. Did you type exit, or just close the window? What about the "script" you use? Does the script do a rollback a commit? Does it use the WHENEVER command (as in whenever sqlerror rollback)?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Shoblock,

    I was querying what Bill said because I thought that by typing 'exit' an implicit 'commit' command is issued - if that's not the case then I've been doing things wrong (& will be writing to to Mr Sam R Alapati to complain about his book)!
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I found the clause. If you are in sql*plus and "SET AUTOCOMMIT ON" then when you type EXIT, it will commit. If it is "SET AUTOCOMMIT OFF" then it will rollback, and if the script if closed without an EXIT, then I suspect that it treats it like a failure.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Actually, just after verifying it by myself the actual process is this: if you set AUTOCOMMIT ON, which is OFF by default, every time you submit a DML statement in SQL*Plus, it will commit it (that's so dangerous), whereas if you type EXIT, it will commit the changes of the last transaction you had active. If you just hit the X of SQL plus, it will rollback the transaction.

Posting Permissions

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