Oracle Tutorials, Tips & Tricks

Auditing DML changes in Oracle

Auditing DML changes in Oracle
We are often faced with a situation when every DML change (Inserts/Updates/Deletes) made in Oracle/SQL tables must be audited. Banking Softwares and other similar applications have a strict requirement to maintain the audit trail of every single change made to the database.The DML changes must be audited irrespective of whether it was made from ...
Tags:

Compound Triggers in Oracle 11g – Tutorial with example

In depth tutorial on Compound Triggers in Oracle 11g with examples. Also we see an example of using Compound Triggers in Auditing of some data.

How to pass CLOB argument in EXECUTE IMMEDIATE

Since Oracle 11g it is possible to pass CLOB argument in EXECUTE IMMEDIATE statements. This allows to overcome default 32k size limit of passing parameters.

45 Useful Oracle Queries

45 Useful Oracle Queries
Here's a list of 40+ Useful Oracle queries that every Oracle developer must bookmark. These queries range from date manipulation, getting server info, get execution status, calculate database size etc.Date / Time related queriesGet the first day of the month Quickly returns the first day of current month. Instead of current month you want to find first day ...
Tags: , ,

Oracle XMLTable Tutorial with Example

Oracle XMLTable Tutorial with Example
Oracle XMLTable: Learn how to parse XML in Oracle using XMLTable in 11g. Use XPath to query XML nodes in Oracle.

Java: Passing Array to Oracle Stored Procedure

This tutorial guides us on how to pass Array objects from Java to stored procedures in Oracle and also, how to retrieve an array object in Java.All PLSQL arrays can not be called from java. An array needs to be created as TYPE, at SCHEMA level in the database and then it can be used ...

Oracle Skip Locked

Oracle Skip Locked
Oracle 11g introduced SKIP LOCKED clause to query the records from the table which are not locked in any other active session of the database.This looks quite similar to exclusive mode of locking.The select for update statement has always been problematic for large updates because it the default is to wait for locks and using select for ...

Index Skip Scan in Oracle

Index Skip Scan in Oracle
With Oracle 9i, the Cost-Based Optimizer (CBO) is equipped with many useful features, one of them is "Index skip scan". In previous releases a composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement. In Oracle 9i this restriction is removed ...

Pagination in Oracle using ROWNUM and Limiting Result Set

Pagination in Oracle using ROWNUM and Limiting Result Set
ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:To perform top- N processing. This is similar to using the LIMIT clause, available in some other databases. To paginate through a ...

Index usage with LIKE operator in Oracle & Domain Indexes

Index usage with LIKE operator in Oracle & Domain Indexes
A lot of developers might be confused about index selectivity while using %LIKE% operator. So please find below how index worked when you use LIKE operator.Problem StatementWhile optimizing high CPU consuming queries on 3rd party application, verified that most of the queries are using '%LIKE%' operator. Interestingly enough, while some of these queries are going for "INDEX RANGE" while others are going for "FULL TABLE ...
Page 1 of 212