Page 159
FIG. 7.1
Truncate table syntax.
Listing 7.9L_07_09.SQLTruncating a Table
SQL> truncate table wge_maint; Table truncated
The DML commands do not permanently change database table records until the commit command is issued. Commit causes the database manager to make the changes permanent. Previous to the commit, the records are marked and the changes wait in temporary storage for the commit to occur. The reason the changes are not made permanent is because Oracle7 gives you the opportunity to undo or eliminate the changes using the rollback command. All changes caused by the DMS commands are temporary between database commits.
NOTE |
I once was modifying records in a table. I was working on a Unix platform and had two different sessions going at the same time. I modified a table on one of the sessions. When I ran a select command on the other session against the modified table, I didn't see any of the changes. My first thought was that the update statement did not work. I checked the statement and executed it again. When I ran the select again on the second session, there were no changes. This really had me scratching my head. After a while, I ran the select on the session on which I had performed the update statement. The select statement listing showed the records were all modified. |
I was dumbfounded at this turn of events until I remembered I am never issued a commit to save the records permanently. The point is that when you execute DML commands against a table and do not commit the changes, it may appear to you that the table has changed, but it will the changes will not appear to other sessions that may be looking at the table. In addition, if your session is abnormally terminated (such as shutting off your machine before logging off of Oracle7) the changes will not be permanently retained.n
Page 160
To make changes permanent, issue the commit command. Listing 7.10 illustrates the command. It consists of the word commit followed by a semicolon. This causes all records modified by your session since the last commit issued to be permanently recorded. The second way to permanently save the changes is to log off the session. You can autocommit the changes by two more means: issue a create or drop command.
Listing 7.10L_07_10.SQLSaving Changes Permanently by Using the Commit Command
SQL> commit; Commit complete.
You can undo the changes by using the rollback command. This command reverses all changes that have occurred since the last commit. The command consists of the word rollback followed by a semicolon. If the commit procedure is inconvenient, you can use the autocommit set command to permanently record the changes every time the DML commands are issued. The normal setting is off. Changing the setting to on makes the changes permanent without having to use the commit.
Records are added to tables by using the insert command. The command consists of a columns clause and a values clause. You can add multiple records to the table by using a select statement in the values clause. You use the update command to modify records. The command has two parts. The first part is the items to be updated and the new values. The second section is a where clause that identifies the records to be updated. The delete and truncate commands delete records. Delete removes the records from the table and stores a copy in temporary storage for restoration purposes. It also has an optional where clause that identifies the records to be deleted. Truncate removes all the records in the table. You cannot restore these records. Changes to the database are temporary until you issue the commit command or you log off the database. To restore the database, use the rollback command. Rollback restores records since the last commit was issued.
This concludes part II of the book. The next section covers Oracle's procedure language called PL/SQL and Oracle's table loader called SQL*LOADER. PL/SQL is an excellent tool used in all the Oracle products to perform special functions. It is used extensively in Oracle Forms to fine-tune the form mechanics. It is also used for data conversion purposes. The SQL*LOADER product loads Oracle tables with records from flat text files. It is used when converting data from an older system into the new Oracle database If you are anxious to get into learning Oracle Forms, you could skip this next section and start with part IV. You need to return to this
Page 161
section, however, at some point. Without an understanding of PL/SQL, you do not fully understand the power of Oracle Forms.