Kde…

I got an email from someone a couple of days ago. The email said

############## 

hi,
      i am having a problem here pls help me
 
this is my i/p and o/p pls tell me logic to develop this
 
input table or file
101,abc,5000
101,abc,2000
101,abc,1000
101,abc,1500
102,aaa,2000
102,aaa,1500
102,aaa,500
103,asa,1000
103,asa,300
103,asa,600
 
o/p table or file
0,101,abc,5000
1,101,abc,3000(i.e.,5000-2000)
2,101,abc,2000(i.e.,3000-1000)
3,101,abc,500(i.e.,2000-1500)
0,102,aaa,2000
1,102,aaa,500(i.e.,2000-1500)
2,102,aaa,0(i.e.,500-500)
0,103,asa,1000
1,103,asa,700(i.e.,1000-300)
2,103,asa,100(i.e.,700-600)
 
thanks and regards
******

##################

Oracle analytic functions were on my mind just like Ray charles (he had Georgia). Even though I knew how to use analytic functions I had to do some research. But I finally got the answer.

First of all the input data didn’t make sense because there was no spelled out requirements provided. After my research, I took a close look at it and found that by adding a sequence number to the table this can be easily done. So I created a test case and assigned a sequence number to the table along with other columns.

This is what I could come up with.

SQL> select banner from v$version;

10 rows selected.
Don’t write code for something that can be done so easily if you have the right tools.
hope this helps

cheers
Kirtan

In Oracle/PLSQL, the NVL function allows you to substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL(a_value, replace_it_with )

a_value is the string to test for a null value.
replace_it_with is the value returned if string1 is null.

Examples,

select NVL(product_descr, product_name) from products;

This statement would return product_name everytime it encounters a null value in the product_descr field. If it finds a value (i.e. any value other than null value) in product_descr field than it will return that value.

select NVL(numeric_field,0) from a_table;

This statement would return 0 everytime it finds a null value in the numeric_field.

You can not use NVL like,

Select NVL(distinct(product_name),’not found’),price, category from a_table;

One possible solution for this is to use distinct before NVL in the statement.

Select distinct NVL(product_name,’not found’),price, category  from a_table;

If you wanted to get distinct values before using NVL function, you can do this.

Select

from ( select distinct product_name, price, category from a_table; )

Also not like, (NVL does not function like DECODE.)

NVL(field1, replace_value1,field2, replace_value2,DEFAULT_FOR_ALL)

You will need to make separate NVL function calls for each column.

select NVL(product_name, ‘not found’), NVL(product_descr, ‘not found’) from a_table;

Hope this helps.

Kirtan

This is the basic explanation of what SQL/XML functions provide in Oracle (since 9iR2) Database as per SQL 2003 standards.

• XMLElement takes an element name, an optional collection of attributes for the element, and arguments that make up the element’s content and returns an instance of type XMLType.
• XMLForest converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments.
• XMLConcat takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat is the inverse of XMLSequence.
• XMLAgg takes a collection of XML fragments and returns an aggregated XML document

I am going to write a couple of examples on how to use these functions in next day or two.

Top-N queries provide a method for limiting the number of rows returned from sets (mostly ordered) of data.

NOTE: Oracle does not guarantee that it will return the result of an SQL statement in the order the data was inserted into the table(s). Only way of achieving that is to explicitly uses the ORDER BY clause. Let’s see.

Let’s prepare the table first.

Create table test(a number);

INSERT INTO test (a) VALUES (6);
INSERT INTO test (a) VALUES (5);
INSERT INTO test (a) VALUES (4);
INSERT INTO test (a) VALUES (3);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (1);
COMMIT;

We want the smallest 3 records, so we limit the rows returned using the rownum and order the data by the column (A number).

SQL> select * from test
  2  where rownum<=3
  3  order by a;

NOT what we wanted, right?

What happens is that the rownum <=3 check is performed prior to the order by clause.

So let's use a "Top-N" style query which utilizes an ordered inline view to force the data into the correct order, then performs the rownum<=3 check to limit the data returned.

SQL> select a from (select a from test order by a)
  2  where rownum<=3;

Now if we wanted 5 biggest values out of the table, all we have to do is change the order of data in the inline view.

SQL> select a from (select a from test order by a desc)
  2  where rownum<=3;

Hope this helps.

Kirtan Desai

Query to find oracle version details on your system.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

This question is asked more times than one would imagine.

Question: How to detect duplicates in a table?

Answer:

There are multiple ways of doing things. Here are some of them.

Use of group by

select keys, count(*) from mytable
group by keys
having count(*) > 1

For Example:

SQL> select * from t1;

Use of ROWID

One way is to join the table to itself if it is a small table.

select * from t1 a, t1 b
where a.key = b.key and a.rowid != b.rowid

For Example:

SQL> create table t1 (a number, b number);

This query however shows same record twice, i.e. one from “t1 a”  and the other from “t1 b”.

The other way of doing it is by doing a sub-select on the same table. This query can be used in two ways. One way is to find all the rows that arrived into the table after the first one. The other way is to find all the rows that arrived into the table before the last one. The key is to switch between < and > signs.

select x.rowid, x.
from t1 x
where x.rowid > any (
                             select y.rowid 
                             from t1 y
                             where x. = y.
                             )
For Example:

Notice that Oracle left out the row with AAAGVNAB2AAAAQcAAE rowid first time and AAAGVNAB2AAAAQcAAA rowid second time.

A simple way of spelling out numbers to words. This could be handy for printing reciepts, cheques etc.

KDE>select to_char( to_date( abs(&num),'J'),'Jsp') "NUMBER TO TEXT" from dual
  2  /
Enter value for num: 345
old   1: select to_char( to_date( abs(&num),'J'),'Jsp') "NUMBER TO TEXT" from dual
new   1: select to_char( to_date( abs(345),'J'),'Jsp') "NUMBER TO TEXT" from dual

NUMBER TO TEXT
------------------------
Three Hundred Forty-Five

Or You can use it on a column like

SELECT sal, TO_CHAR(TO_DATE(sal,'j'), 'jsp') FROM emp;

800.00        eight hundred
2,850.00     two thousand eight hundred fifty
2,450.00     two thousand four hundred fifty
5,000.00     five thousand
3,000.00     three thousand
2,975.00     two thousand nine hundred seventy-five
1,250.00     one thousand two hundred fifty
…

…

hope this helps…

Here is a way of keeping track of all the changes made to a schema by DDL statements.

DROP TRIGGER DDL_CHANGE_TRACKER
/
DROP TABLE   dll_audit_log
/

CREATE TABLE my_test (a number)
/
DROP TABLE my_test
/
set pages 50000
SELECT * FROM DDL_CHANGE_TRACKER
/

I have been dealing with ora-01555 all day. I guess I was one of the victims of misconception about the rollback segments. Here is a small description of what I understand of “01555” now.
There are three situations that can cause the ORA-01555 error:
1. An active database with an insufficient number of small-sized rollback segments
2. A rollback segment corruption that prevents a consistent read requested by the query
3. A fetch across commits while your cursor is open

Here are some simple rollback segment rules, as per Oracle’s documentation:

1. A transaction can only use one rollback segment to store all of its undo records.

2. Multiple transactions can write to the same extent.

3. The head of the rollback segment never moves into a rollback extent currently occupied by the tail.

4. Extents in the ring are never skipped over and used out of order as the head tries to advance.

5. If the head can’t use the next extent, it allocates another extent and inserts it into the ring.

Two issues that you need to consider when deciding whether your segment is large enough to fit the transaction:
1. Make sure that transactions will not cause the head to wrap around too fast and catch the tail.
2. If you have long-running queries that access frequently changing data, make sure that the rollback segment doesn’t wrap around and prevent the construction of a read-consistent view.

From a developer’s perspective, you can restructure your PL/SQL code to avoid fetching across commits that cause the ORA-01555 error.

these are general guidelines. one must read the oracle documentation to implement/follow these guidelinee.

I also tried Oracle Raptor yesterday. It’s neat, but has a few issues.
The bugs and issues are also listed on http://forums.oracle.com/forums/forum.jspa?forumID=260 . It will do a preety good job as far as helping the developers are concerned. I must point out that the GUI is a lil bit slower.

A free download is now available http://www.oracle.com/technology/products/database/project_raptor/index.html
Found a new ORACLE Q/A site. I think concept is preety neat. Found it on Tom Kyte’s blog.
Check it out at ORA Q/A.