Kirtan Desai

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 [...]

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, [...]

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. [...]

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;          A          [...]

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” [...]

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 ddl_change_tracker (    stamp     DATE,    username  VARCHAR2(30),    osuser    VARCHAR2(30),    machine   VARCHAR2(30),    terminal  VARCHAR2(30),    operation VARCHAR2(30),    objtype   VARCHAR2(30),    objname   VARCHAR2(30)) / [...]

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 [...]

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 [...]