Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
SECTION 14 LESSON 1 Database Transactions
What Will I Learn?
In this lesson, you will learn to:
• Define the terms COMMIT, ROLLBACK, and SAVEPOINT as they relate to data transactions
• List three advantages of the COMMIT, ROLLBACK and SAVEPOINT statements•
Explain why it is important, from a business perspective, to be able to control the flow of transaction processing
Why Learn It?
What if a bank had no systematic process for recording deposits and withdrawals?
How would you know if a deposit was credited to your account before you needed to withdraw money?
You can imagine the confusion it would cause.
Fortunately, banks control transaction processes to ensure data consistency.
In this lesson you will learn how the process of changing data is managed and how changes to a database are committed or cancelled.
TRANSACTIONS
Transactions are a fundamental concept of all database systems. Transactions allow users to make changes to data then decide whether to save or discard the work.
Database transactions bundle multiple steps into one logical unit of work. A transaction consists of one of the following:
• DML statements which constitute one consistent change to the data. The DML statements include INSERT, UPDATE, DELETE and MERGE
• One DDL statement such as CREATE, ALTER, DROP, RENAME or TRUNCATE
• One DCL statement such as GRANT or REVOKE TRANSACTION ANALOGY
A bank database contains balances for various customer accounts, as well as total deposit balances for other branches. Suppose a customer wants to withdraw and transfer money from their account and deposit it into another customer‟s account at a different branch.
There are several separate steps involved to accomplish this rather simple operation. Both bank branches want to be assured that either all steps in the transaction happen, or none of them happen and if the system crashes, the transaction is not left partially complete. Grouping the withdrawal and deposit steps into a transaction provides this guarantee.
A transaction either happens completely or not at all.
Transactions
Transactions are controlled using the following statements:
- COMMIT: Represents the point in time where the user has made all the changes he or she wants to have logically grouped together, and because no mistakes have been made, the user is ready to save the work. When a COMMIT statement is issued, the current transaction ends making all pending changes permanent.
- ROLLBACK: Enables the user to discard changes made to the database. When a ROLLBACK statement is issued, all pending changes are discarded.
- SAVEPOINT: Creates a marker in a transaction, which divides the transaction into smaller pieces.
- ROLLBACK TO SAVEPOINT: Allows the user to roll back the current transaction to a specified a savepoint. If an error was made, the user can issue a ROLLBACK TO SAVEPOINT statement discarding only those changes made after the SAVEPOINT was established.
clause was not included in the last UPDATE. To remedy the mistake, the user issued a ROLLBACK TO SAVEPOINT one.
The data is now restored to its state at SAVEPOINT one.
Exp:
UPDATE d_cds SET cd_number = 96
WHERE title = 'Graduation Songbook';
SAVEPOINT one;
INSERT INTO d_cds(cd_number, title, producer, year) VALUES(100, 'Go For It', 'The Music Man', 2004) );
UPDATE d_cds
SET cd_number = 101;
ROLLBACK TO SAVEPOINT one;
COMMIT;
When does a transaction start or end?
A transaction begins with the first DML (INSERT, UPDATE, DELETE or MERGE) statement.
A transaction ends when one of the following occurs:
• A COMMIT or ROLLBACK statement is issued
• A DDL(CREATE, ALTER, DROP, RENAME or TRUNCATE) statement is issued
• A DCL(GRANT or REVOKE) statement is issued
• The user exits iSQL*Plus or SQL*Plus
• A machine fails or the system crashes
After one transaction ends, the next executable SQL statement automatically starts the next transaction.
A DDL statement or a DCL statement is automatically committed and therefore implicitly ends a transaction.
Every data change made during a transaction is temporary until the transaction is committed.
DATA CONSISTENCY
Imagine spending several hours making changes to employee data only to find out that someone else was entering information that conflicted with your changes!
To prevent such disruptions or conflicts and to allow multiple users to access the database at the same time, database systems employ an automatic implementation called "read consistency."
Read consistency guarantees a consistent view of the data by all users at all times. Readers do not view data that is in the process of being changed. Writers are ensured that the changes to the database are done in a consistent way. Changes made by one writer do not destroy or conflict with changes another writer is making.
Read consistency is an automatic implementation. A partial copy of the database is kept in undo segments. When User A issues an insert, update or delete operation to the database, the Oracle server takes a snapshot (copy) of the data before it is changed and writes it to an undo (rollback) segment.User B still sees the database as it existed before the changes started; s/he views the undo segment‟s snapshot of the data.
Before changes are committed to the database, only the user who is changing the data sees the changes;
everyone else sees the snapshot in the undo segment. This guarantees that readers of the data see consistent data that is not currently undergoing change.
When a DML statement is committed, the change made to the database becomes visible to anyone executing a SELECT statement. If the transaction is rolled back, the changes are undone:
• The original, older version of the data in the undo segment is written back to the table.
• All users see the database as it existed before the transaction began.
COMMIT, ROLLBACK and SAVEPOINT
COMMIT and ROLLBACK ensure data consistency, making it possible to preview data changes before making changes permanent and a way to group logically related operations.
SAVEPOINT allows to create a point in a transaction to which we can rollback without having to undo the entire transaction.
However, SAVEPOINT is not supported in Oracle Application Express, due to the way Oracle Application Express manages connections to the database.
In the transaction shown in the graphic, a DELETE statement was issued and then SAVEPOINT A
was established. This SAVEPOINT acts like a marker that will allow the user to rollback any subsequent changes made to the data back to the state of the data as it existed at this point.
In the example, following SAVEPOINT A, the user issues some INSERT and UPDATE statements, then establishes another rollback marker at SAVEPOINT B. If for some reason the user does not want these INSERT and/or UPDATE statements to occur, the user can issue a ROLLBACK TO SAVEPOINT A statement. This will rollback to the state of the data as it was at the SAVEPOINT A marker.
Adding other SAVEPOINTS creates additional markers for rollback points. If a user issues a ROLLBACK without a ROLLBACK TO SAVEPOINT statement, the entire transaction is ended and all pending data changes are discarded.
IMPLICIT TRANSACTION PROCESSING
Automatic commit of data changes occurs under the following circumstances:
• a DDL statement is issued
• a DCL statement is issued
• a normal exit from iSQL*Plus or SQL*Plus without explicitly issuing COMMIT or ROLLBACK statements
• When the Autocommit box in ticked in Oracle Application Express
Automatic rollback occurs under an abnormal termination of iSQL*Plus, SQL*Plus or when a system failure occurs.
This prevents any errors in the data from causing unwanted changes to the underlying tables. The integrity of the data is therefore protected.
LOCKING
It is important to prevent data from being changed by more than one user at a time. Oracle uses locks thatprevent destructive interaction between transactions accessing the same resource, either a user object (such as tables or rows) or a system object not visible to users (such as shared data structures and data dictionary rows).
How the Oracle Database Locks Data
Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested. Implicit locking occurs for all SQL statements except SELECT.
The users can also lock data manually, which is called explicit locking.
When a COMMIT or ROLLBACK statement is issued, locks on the affected rows are released.
Key terms used in this lesson include:
In this lesson you have learned to:
• Define the terms COMMIT, ROLLBACK, and SAVEPOINT as they relate to data transactions
• List three advantages of the COMMIT, ROLLBACK, and SAVEPOINT statements
• Explain why it is important, from a business perspective, to be able to control the flow of transaction processing
LESSON 2
Certification Exam Preparation and iSQL*Plus
What Will I Learn?
In this lesson, you will learn to:
To prepare for the OCA Exam, you will need to review the concepts learned in the Database Programming course as well as learn the following:
1. How to include iSQL*Plus commands to produce more readable SQL output. The iSQL*Plus Oracle product contains its own command language and can be used to create script files. iSQL*Plus is an Oracle proprietary environment that runs on a browser and is used to submit SQL statements to an Oracle server for execution.
iSQL*Plus User's Guide and Reference iSQL*Plus PowerPoint Slides
2. Database Transactions 3. Controlling User Access
Introducing a new way to get started on your path to certification and career success!
The Introduction to Oracle10g: SQL online exam satisfies your first requirement for the Oracle Certified Associate credential.
By achieving Oracle Certified Associate (OCA) status, you will gain added credibility for your foundation-level knowledge and commitment to learning Oracle technologies.
You will also be halfway completed with your Oracle Certified Associate credential, which gains you access to a wealth of opportunity worldwide.
Oracle Certified Associate
The OCA exam has a higher level of difficulty than the Academy SQL Final Exam. However, your commitment and success in passing the Academy Database Programming with SQL Final Exam makes you a good candidate for the certification exam. The following information will help you get started toward your goal.
Basic and Mastery Sections
Questions in the Basic section of your exam cover the fundamental concepts taught in the Database Programming with SQL course.
Questions in the Mastery section are also based on these same materials, but they are somewhat more difficult than the questions in the Basic section.
These questions may draw more extensively on a candidate's practice or experience. You must pass both sections in order to pass the exam.
OCA Exam Passing Scores
Oracle routinely publishes new versions of Oracle Certification exams, and the passing score across versions may differ. The passing score for each exam is set independently to maintain a consistent standard across versions. For example, to maintain a consistent standard for success, a new version of an exam, which
contains more difficult questions than the prior version, will be assigned a somewhat lower required passing score.
For this reason, passing scores may vary between different exams in your certification track, and between later and earlier versions of the same exam.
The passing scores provided on the Oracle Certification Program website are for informational purposes only. Oracle does not recommend an exam preparation strategy targeting the passing score, because passing scores are subject to change.
For the latest information check the Oracle University certification website at http://www.oracle.com/education/certification/
HINTS FOR TAKING THE EXAM
• Be careful to select all the right answers on the test; don't stop looking when you find one.
• Spend only 30 seconds evaluating a question on your first pass, use the review page to go back and review the ones you didn't know.
• There is no penalty for guessing, answer every question.
• The timer does not count down as you work. It will only display the accurate time remaining when you refresh the screen. For example, after submitting an answer a new time will be displayed or if you press the refresh button in your browser window.
Producing Readable Output with iSQL*Plus
The iSQL*Plus Reference Guide is a comprehensive document for iSQL*Plus (iSQLPlusRef.pdf).
Review the Notes pages of the iSQL*Plus PowerPoint slides (iSQLPlusSlides.ppt). These notes explain iSQL*Plus concepts needed for the exam.
Using iSQL*Plus you can create reports that prompt the user to supply their own values to restrict the range of data returned by using substitution variables. These are the same substitution variables as was covered in Section 1, Lesson 1 of this course. But, the character used to identify the substituition variable is different in SQL*Plus and iSQL*Plus. Both these environments use the & instead of :.
Substitution variables can be used anywhere in the SELECT statement such as in the WHERE clause, a text string, column or table name. Substitution variables cannot be used as the first word entered at the command prompt.
Effects of substitution variables
• report figures of current time period
• report data only relevant to the use
• display personnel only within a given department
• dynamically alter headers and footers
• obtain input values from a file instead of a person
• pass values from one SQL statement to another Defining Substitution Variables
To predefine a variable use the DEFINE command.
DEFINE job_title = IT_PROG DEFINE job_title = 'President Elect'
DEFINE job_title this command will verify the variable, its value and its data type.
UNDEFINE job_title will clear the DEFINE command/or exit iSQL*Plus.
Using the DEFINE command
1.Create the substitution variable with the DEFINE command 2.Use the variable in a SELECT statement
Exp:
DEFINE employee_num = 200
SELECT employee_id, last_name, first_name FROM employees
WHERE employee_id = &employee_num;
Exp:
SELECT employee_id, last_name, Job_id, &&column_name FROM employees
ORDER BY &column_name;
Use the VERIFY command to show the variable before and after substitution.
Exp:
SET VERIFY ON
SELECT employee_id, last_name, Job_id, &&column_name FROM employees
ORDER BY &column_name;
Customizing the iSQL*Plus Environment The SET commandSET system_variable value SET ECHO ON
SHOW ECHO - Used to verify what you have set SHOW ALL - Shows all the SET variable values ARRAYSIZE {20 | n } sets the database fetch size
FEEDBACK {6 | n | OFF | ON } sets the number of records to return HEADING {OFF |ON} determines if column headings are displayed LONG {80 | n} sets the maximum width for displaying LONG values
Format Commands COLUMN - column name|alias if used CLEAR - clears the column formats
HEADING - sets the column heading | for line feed FORMAT - changes the display
9 single zero-suppression (e.g., 999999 looks like 19342) 0 enforces leading zero
$ floating dollar sign L local currency
. decimal point position , thousand separator
NOPRINT - hides the column PRINT - shows the column
NULL - text to be displayed for NULL values
COLUMN column name - displays the current setting for the specified column COLUMN - displays the current setting for all columns
COLUMN column name CLEAR - clears the column settings for a specified column CLEAR column - clears settings for all columns
TTITLE [text |OFF|ON] - page header use the | to split the header across multiple lines and use ' ' for titles of more than one word
BTITLE [text |OFF|ON] - page footer
BREAK [ON column|alias] - suppresses duplicate values and divides rows of data into sctions using breaks ORDER BY - use this clause with BREAK to order the columns you are breaking on
CLEAR BREAK - Clear all BREAK settings by using the CLEAR command Summary
In this lesson you have learned to:
• Learn to prepare for the OCA Exam
• Learn to become aware of how to include iSQLPlus commands to produce more readable SQL output
• Learn how to gain an understanding of Database Transactions
• Learn how to gain an understanding of Controlling User AccessIf you have passed the Database Programming with SQL Final Exam you are now prepared to take the Oracle 1Z0-007 OCA Exam.