RPAD/LPAD with NULL

Use of RPAD.

sql>Select rpad('A',5) from dual;

----------------

'A    '

But if NULL is one of the Argument then RPAD will not work.

sql> set NULL *
sql> Select rpad(NULL,5) from dual;

----------------

*

Solution:

sql> set NULL *
sql> Select rpad(NVL(NULL,' '),4) from dual;

----------------

'    '  

We will have to use NVL with rpad.

Thanks,

Soham Khot

Oracle NULL Functions

Following are the impoertant oracle sql functions to handle NULLs.
1> NVL
The NVL function lets you substitute a value when a null value is encountered.

NVL( string, replace_with )

If String is NULL then return replace_with.

select NVL('a','b') from dual;
-- Return 'a'

select NVL(NULL,'b') from dual;
-- Return 'b'


2> NVL2
NVL2 extends functionality of NVL.

NVL2( string1, value_if_NOT_null, value_if_null )


3> NULLIF
NULLIF( val1, val2 )

IF val1 = val2 then return NULL
IF val1 != val2 then return val1

4> COALESCE

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

coalesce( expr1, expr2, ... expr_n )

Return first non NULL expression.

5> LNNVL

LNNVL function is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.

select name, comm from emp
where LNNVL(comm >0)

name1 100
name2 200
name3 NULL

Same statement can be written as follows:

select name, comm from emp
where NVL(comm,100) > 0

NVL2

NVL2 extend the functionality of NVL.

Following is the syntax.

NVL2(Expression, value1, value2)

If expression is not NULL then return value1

If expression is NULL then return value2.

Thanks,
Soham Khot

Oracle 9i:Constraints on views

Following design will not work:

Create table a (a number);

create view a_v as select * from a;

alter view a_v add constraint au unique(a) disable novalidate;

insert into a_v values(1);

insert into a_v values(1);

insert into a_v values(1);

commit;

All the values are entered in table and no constraints are validated.

Reason:

Constraints on views are not enforced but just stored in meta-data for more information. It is also used by optimizer to improve the performance.

Thanks,

Soham Khot