Automatically Generating Lists of Numbers in Oracle with Connect By

You want to generate a list of numbers for a one-time calculation or SQL query. For instance, you need a
table or view with the numbers 1 to 1000 for ranking and position calculations, but you don’t want to
create a sequence and call it 1000 times to populate your table.
Oracle provides several techniques for spontaneously generating sets of numbers. Our recipe will use
Oracle’s traditional hierarchy support via the CONNECT BY clause and the ROWNUM pseudocolumn, to build
a ready-made list of numbers.
The next SQL statement uses CONNECT BY LEVEL against the dual table, to build a list of the numbers
1 to 1000.
select rownum as my_numbers
from dual
connect by level <= 1000;
The results are hopefully obvious, but just in case you’re wondering, we return the numbers 1 to
1000 (with abridged results shown here to save space).
1000 rows selected.

Xmlelement function Oracle usage example:

Select Xmlelement("Empname", Ename) from Emp where ecode = 7654;

It will return xmltype data in the following format:


If you want this result in character datatype:

Select Xmlelement("Empname", Ename).getstringval() from Emp where ecode = 7654;

Result will be the same as above but in character string.

Conditional Sorting and Sorting By Function Problem

While querying some data, you need to sort by an optional value, and where that value is not present,
you’d like to change the sorting condition to another column.
Oracle supports the use of almost all of its expressions and functions in the
ORDER BY clause. This
includes the ability to use the
CASE statement and simple and complex functions like arithmetic
operators to dynamically control ordering. For our recipe, we’ll tackle a situation where we want to show
employees ordered by highest-paid to lowest-paid.
For those with a commission, we want to assume the commission is earned but don’t want to
actually calculate and show this value; we simply want to order on the implied result. The following SQL
leverages the
CASE statement in the ORDER BY clause to conditionally branch sorting logic for those with
and without a
select employee_id, last_name, salary, commission_pct
from hr.employees
order by
when commission_pct is null then salary
else salary * (1+commission_pct)
end desc;
We can see from just the first few rows of results how the conditional branching while sorting has
----------- --------- ------ --------------
100 King 24000
145 Russell 14000 .4
146 Partners 13500 .3
101 Kochhar 17000
102 De Haan 7000
Even though employees 101 and 102 have a higher base salary, the
ORDER BY clause using CASE hascorrectly positioned employees 145 and 146 based on their included commission percentage.

Generating Random Data Problem

You need to generate random numbers to simulate real-world events that do not follow a discernible

Use the Oracle built-in PL/SQL package DBMS_RANDOM. The RANDOM function returns an integer in the range
[-231,231) (-231 can be returned, but 231 will not), and the VALUE function returns a decimal number in the
range [0,1) with 38 digits of precision.
For example, the merchandising department wants to lower pricing below list price on catalog items
on a daily basis to potentially stimulate sales from customers who perceive a bargain when the price is at
least 10 percent below list price. However, the merchandising analysts want to vary the discount from 10
percent to 20 percent on a random basis. To do this, first create a DAILY_PRICE column in the
PRODUCT_INFORMATION table as follows:

alter table product_information add (daily_price number);

Next, use the DBMS_RANDOM.V
ALUE function to adjust the DAILY_PRICE to a value between 10 percent
and 20 percent below the list price:
update product_information
set daily_price =
288 rows updated.
Here is the query to retrieve the calculated daily price:
select product_id, list_price, daily_price
from product_information
---------------------- ---------------------- ----------------------
1772 456 380
2414 454 379
2415 359 293
2395 123 99
1755 121 104
2406 223 200
2404 221 182
. . .
Running the UPDATE statement a second time will adjust the prices randomly again, with a different
discount for each product, but still ranging from 10 to 20 percent off:
update product_information
set daily_price =

select product_id, list_price, daily_price
from product_information;

---------------------- ---------------------- ----------------------
1772 456 383
2414 454 378
2415 359 319
2395 123 111
1755 121 107
2406 223 193
2404 221 184
. . .
The random number value returned is multiplied by 0.1 (10 percent), subtracted from 0.9 (90
percent of the list price is 10 percent off), resulting in a discount of between 10 and 20 percent. The final
result is rounded to the nearest dollar.

Finding Sequence Gaps in a Table
You want to find all gaps in the sequence of numbers or in dates and times in your data. The gaps could
be in dates recorded for a given action, or in some other data with a logically consecutive nature.
LAG and LEAD OLAP functions let you compare the current row of results with a preceding row.
The general format of
LAG looks like this
Lag (column or expression, preceding row offset, default for first row)
column or expression is the value to be compared with lagging (preceding) values. The preceding

row offset
indicates how many rows prior to the current row the LAG should act against. We’ve used ‘1’ in
the following listing to mean the row one prior to the current row. The default for
LAG indicates what
value to use as a precedent for the first row, as there is no row zero in a table or result. We instruct Oracle
to use 0 as the default anchor value, to handle the case where we look for the day prior to the first of the
WITH query alias approach can be used in almost all situations where a subquery is used, to
relocate the subquery details ahead of the main query. This aids readability and refactoring of the code if
required at a later date.
This recipe looks for gaps in the sequence of days on which orders were made for the month of
November 1999:

with salesdays as
(select extract(day from order_date) next_sale,
lag(extract(day from order_date),1,0)
over (order by extract(day from order_date)) prev_sale
from oe.orders
where order_date between '01-NOV-1999' and '30-NOV-1999')
select prev_sale, next_sale
from salesdays
where next_sale - prev_sale > 1
order by prev_sale;

Our query exposes the gaps, in days, between sales for the month of November 1999.
---------- ----------
1 10
10 14
15 19
20 22
The results indicate that after an order was recorded on the first of the month, no subsequent order
was recorded until the 10
th. Then a four-day gap followed to the 14th, and so on. An astute sales manager

might well use this data to ask what the sales team was doing on those gap days, and why no orders
came in!

Removing Duplicate Rows Based on a Subset of Columns

Data needs to be cleansed from a table based on duplicate values that are present only in a subset of

Historically there were Oracle-specific solutions for this problem that used the ROWNUM feature. However,
this can become awkward and complex if you have multiple groups of duplicates and want to remove
the excess data in one pass. Instead, you can use Oracle’s ROW_NUMBER OLAP function with a DELETE
statement to efficiently remove all duplicates in one pass.
To illustrate our recipe in action, we’ll first introduce several new staff members that have the same
FIRST_NAME and LAST_NAME as some existing employees. These INSERT statements create our
problematic duplicates.

insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
(210, 'Janette', 'King', 'JKING2', '650.555.8880', '25-MAR-2009', 'SA_REP',
3500, 0.25, 145, 80);
Insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
(211, 'Patrick', 'Sully', 'PSULLY2', '650.555.8881', '25-MAR-2009', 'SA_REP',
3500, 0.25, 145, 80);

Insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
(212, 'Allen', 'McEwen', 'AMCEWEN2', '650.555.8882', '25-MAR-2009', 'SA_REP',
3500, 0.25, 145, 80);


To show that we do indeed have some duplicates, a quick SELECT shows the rows in question.

select employee_id, first_name, last_name
from hr.employees
where first_name in ('Janette','Patrick','Allan')
and last_name in ('King','Sully','McEwen')
order by first_name, last_name;

----------- ----------- ----------
158 Allan McEwen
212 Allan McEwen
210 Janette King
156 Janette King
211 Patrick Sully
157 Patrick Sully

If you worked in HR, or were one of these people, you might be concerned with the unpredictable
consequences and want to see the duplicates removed. With our problematic data in place, we can
introduce the SQL to remove the “extra” Janette King, Patrick Sully, and Allen McEwen.

delete from hr.employees
where rowid in
(select rowid
(select first_name, last_name, rowid,
row_number() over
(partition by first_name, last_name order by employee_id)
from hr.employees)
where staff_row > 1);

When run, this code does indeed claim to remove three rows, presumably our duplicates. To check,
we can repeat our quick query to see which rows match those three names. We see this set of results.

----------- ----------- -------------------------
158 Allan McEwen
156 Janette King
157 Patrick Sully
Our DELETE has succeeded, based on finding duplicates for a subset of columns only.

Assigning Ranking Values to Rows in a Query Result


The results from a query need to be allocated an ordinal number representing their positions in the
result. You do not want to have to insert and track these numbers in the source data.
Oracle provides the RANK analytic function to generate a ranking number for rows in a result set. RANK is
applied as a normal OLAP-style function to a column or derived expression. For the purposes of this
recipe, we’ll assume that the business would like to rank employees by salary, from highest-paid down.
The following SELECT statement uses the rank function to assign these values.

select employee_id, salary, rank() over (order by salary desc) as Salary_Rank
from hr.employees;

Our query produces results from the highest earner at 24000 per month, right down to the employee
in 107th place earning 2100 per month, as these abridged results show.

----------- ---------- -----------
100 24000 1
101 17000 2
102 17000 2
145 14000 4
146 13500 5
201 13000 6
205 12000 7
108 12000 7
147 12000 7
132 2100 107

107 rows selected.

Accessing Values from Subsequent or Preceding Rows
You would like to query data to produce an ordered result, but you want to include calculations based on
preceding and following rows in the result set. For instance, you want to perform calculations on eventstyle
data based on events that occurred earlier and later in time.
Oracle supports the
LAG and LEAD analytical functions to provide access to multiple rows in a table or
expression, utilizing preceding/following logic—and you won’t need to resort to joining the source data
to itself. Our recipe assumes you are trying to tackle the business problem of visualizing the trend in
hiring of staff over time. The
LAG function can be used to see which employee’s hiring followed another,
and also to calculate the elapsed time between hiring.

select first_name, last_name, hire_date,
lag(hire_date, 1, '01-JUN-1987') over (order by hire_date) as Prev_Hire_Date,
hire_date - lag(hire_date, 1, '01-JUN-1987') over (order by hire_date)
as Days_Between_Hires
from hr.employees
order by hire_date;

Our query returns 107 rows, linking the employees in the order they were hired (though not
necessarily preserving the implicit sort for display or other purposes), and showing the time delta
between each joining the organization.

----------- ---------- --------- --------- ------------
Steven King 17-JUN-87 01-JUN-87 16
Jennifer Whalen 17-SEP-87 17-JUN-87 92
Neena Kochhar 21-SEP-89 17-SEP-87 735
Alexander Hunold 03-JAN-90 21-SEP-89 104
Bruce Ernst 21-MAY-91 03-JAN-90 503
David Lee 23-FEB-00 06-FEB-00 17
Steven Markle 08-MAR-00 23-FEB-00 14
Sundar Ande 24-MAR-00 08-MAR-00 16
Amit Banda 21-APR-00 24-MAR-00 28
Sundita Kumar 21-APR-00 21-APR-00 0

107 rows selected.
You can calculate for yourself the day differences to confirm the
LAG function and difference
arithmetic are indeed working as claimed. For instance, there really are 503 days between January 3,
1990 and May 21, 1991.

Finding Duplicates and Unique Values in a Table

You need to test if a given data value is unique in a table—that is, it appears only once.

Oracle supports the standard
HAVING clause for SELECT statements, and the COUNT function, which
together can identify single instances of data in a table or result. The following
SELECT statement solves
the problem of finding if the surname Fay is unique in the
select last_name, count(*)
from hr.employees
where last_name = 'Fay'
group by last_name
having count(*) = 1;
With this recipe, we receive these results:
------------------------- ----------
Fay 1
Because there is exactly one LAST_NAME value of Fay, we get a count of 1 and therefore see results.

Contact to get scripts for replication of Oracle database. email

Replicate Oracle database real time from live server to target standby server.

Data Replication is literally the act of accomplishing data object changes throughout a distributed system. Period. Replication can be manual, or it can be automated.  
Why would we want to replicate?There are a number of reasons to replicate data, but it is a good bet that they all boil down to increased availability. This means that the same data is available at different sites, and the flow of data between those sites is automated. Replication supports increased availability by providing the following: Change consistency: Ensures that all sites get the same change.
Mass deployment/disconnected computing: Data can be sent to secondary computers (laptops, desktops) so that it is available when these devices might be offline.Faster access: Load balancing is the art of distributing client connections over multiple databases. This comes in really handy when the system has a large number of users, and even more so if those users are geographically separated from the system databases. The user just connects the geographically closest database. Network load can also be reduced by directing traffic over different routers for different database sites.Survivability: Data is still accessible if one site fails.
Contact to get scripts for replication of Oracle database. email

V. Kapoor

{picture#} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#} {twitter#} {google#}
Fox Infotech. Powered by Blogger.