Oracle Database Tutorials

In Depth Oracle Database Management Tutorials and Oracle Database Administration Articles
  Title / Author Replies
Views Reverse Sort Order
There are times when duplicate rows somehow creep into the table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Now in Oracle you can delete the duplicate entries by just...
A datatype is a classification of a particular type of information or data. Each value manipulated by Oracle has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another....
Introduction Oracle provides two collection types: nested tables and varying arrays or VARRAYS. A collection is an ordered group of elements of the same type. Each element from the group can be accessed using a unique subscript. The element types of a collection can be either built-in...
I would be doing this in a new user and so first you need to have the administrative priviledges to create new user. By default the Scott user does not have the priviledges and you need to login using the System user. Create New User In Oracle Create User <UserName> Identified by ; ...
If I say Oracle is the best database system available then there will be hardly any one who will disagree with me. Oracle 8 / 8i were the best database systems and the biggest bug in Oracle 8 is, there is no uninstaller and after you install the Oracle 8 personal you donít have an option of...
SQL*Plus (Multi-page thread 1 2)
Introduction SQL*Plus (pronounced "sequel plus") is an interactive tool for the Oracle RDBMS environment. SQL*Plus can be used simply to process SQL statements one at a time, process SQL statements interactively with end users, utilize PL/SQL for procedural processing of SQL statements, list...
PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to enable a procedure to continue after an error...
When you install an Oracle database, you automatically install an Apache 1.3 HTTP server on which the database relies for many of its products and features. It seems to be a waste of resources to install a second Web server for database-driven Web site development on a single machine. It would be...
Data integrity is about enforcing data validation rules such as checking that a percentage amount is between 0 and 100 and to ensure that invalid data does not get into your tables. Historically, these rules were enforced by the application programs themselves and the same rules were checked...
Let us suppose we have a table T_APHA_NUMERIC which consists alphanumeric column as shown below APHA_NUMERIC CDNTK1V0103H 123ABC45NJDK ABC12345 12345 ABC5TEST05 GO4EXPERT 4758501
When you use SELECT in a PL/SQL block, it's important to make sure that exactly one row will always be returned by your query. If more than one row is returned, the TOO_MANY_ROWS exception occurs. There are four ways to make sure your code is safe from this error... Oracle's PL/SQL language...
In below articles I have used both group by and also partition by Difference between rank, dense_rank and row_number function in Oracle Total Scenario of Callerlog Table Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle So I thought to explain the...
PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return...
Let us suppose we have a string like 'GO 4 EXPERT|99999|20130101|20131231' and we have a requirement to split the string based on delimiter. And the required output is as below GO 4 EXPERT 99999 20130101 20131231 We need to write a query to which will split the string.
Materialized Views Stores results not queries Requires Physical memory No auto updates Execution time is less Views Stores queries not data
We will try to understand the difference by looking into below examples. Let us suppose we have a table called rnk_tbl with below data EMP_NO MNG_NO DPT_NO EFF_DT END_DT 123 789 345 01/01/2011 12/31/2011 123 789 345 01/01/2010 12/31/2011 123 789 678 01/01/2012 12/31/2099...
Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle has not covered total scenario and the query is not universal. Let us suppose the callerlog table with below data CALLERID CALLER CALLEDTO CALLER_DT OUTGOING_SEC 1 123 456 02/21/2013 30 2 123 456 02/21/2013 10...
In day to day life we all use mobile phones and make outgoing and incoming calls. Normally our each call will be stored in the caller log in telecommunication tables. We will call to call center to enquire about the no. of incoming calls and outgoing calls we have done. They will enter our no....