Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Oracle Help
- Thread starter jay59
- Start date
I do not know much about the AQ of Oracle, but an autonomous transaction is when you execute a statement independent of other statements within the same session.
For instance... say you have some PL/SQL code which loads in a bunch of data from an external source. The data is non-normalized and your PL/SQL code is supposed to normalize the data into all the appropriate tables. Now say you have a rule which commits the data to the tables only when all the data can be normalized fine... if any of the data cannot go into it's appropriate table, then you want to rollback the entire record and move on.
Throughout your execution of this code however, say you want to write a log entry into an audit/log table indicating each step you have completed with each record. This log entry would need to be committed to the database, but the real code for normalizing the data should not be committed since you have a rule of committing only if all the data of a record can be normalized. These log entry statements should be executed as autonomous transactions so that they are committed to the database, but the rest of the normalization processing is not yet committed.
That was long, but hopefully not too boring to understand.
You will need to look up autonomous transactions in a reference manual to get the exact syntax of how to use it.
For instance... say you have some PL/SQL code which loads in a bunch of data from an external source. The data is non-normalized and your PL/SQL code is supposed to normalize the data into all the appropriate tables. Now say you have a rule which commits the data to the tables only when all the data can be normalized fine... if any of the data cannot go into it's appropriate table, then you want to rollback the entire record and move on.
Throughout your execution of this code however, say you want to write a log entry into an audit/log table indicating each step you have completed with each record. This log entry would need to be committed to the database, but the real code for normalizing the data should not be committed since you have a rule of committing only if all the data of a record can be normalized. These log entry statements should be executed as autonomous transactions so that they are committed to the database, but the rest of the normalization processing is not yet committed.
That was long, but hopefully not too boring to understand.
You will need to look up autonomous transactions in a reference manual to get the exact syntax of how to use it.
- Status
Similar threads
- Locked
- Question