If you want a query which returns rows in column containing NULL or empty space then the using NULL alone in where clause will not return empty space rows. So the query should be altered as below

Actual Query

 SELECT COUNT (*) 
   FROM TABLE 
  WHERE COL_NAME IS NULL;

Altered Query

  SELECT COUNT (*) 
    FROM TABLE 
   WHERE trim(COL_NAME) IS NULL

(or)

Altered Query

  SELECT COUNT (*) 
    FROM TABLE  
    WHERE COL_NAME IS NULL OR 
          LENGTH(TRIM (COL_NAME)) = 0

select distinct ac.name,ac.line
  from all_source ac
 where ac.OWNER ='DBOWNERNAME' and
       lower(ac.TEXT) like '%TABLE_NAME%';

While Doing sort in oracle the Normal sort works differently for the following Data

CREATE TABLE TestTable(Col1 VARCHAR2(20));

insert into TestTable values('A11');
insert into TestTable values('A260');
insert into TestTable values('A10');
insert into TestTable values('A5');

A11
A260
A10
A5

  SELECT * 
    FROM TestTable 
   ORDER BY col1;

Output

A10
A11
A260
A5

You may expected A5 before A10 and A11. But It would have been before A10 and A11 when it is A05. The workaround for this is as below.

SELECT * 
  FROM TestTable
 ORDER BY lpad(col1,9999999);

Output

A5
A10
A11
A260

In Oracle if you want to limit the records based on ROWNUM(mostly while doing pagination) the between is not going to work.

Below Doesn’t work

 
 SELECT Column1, Column2, ......., Column N  
   FROM TableName
  WHERE ROWNUM Between 5 AND 10; 

The alternate for this is as Follows

 
SELECT * FROM
 (SELECT rows_to_page.*, ROWNUM rnum FROM
   (SELECT Column1, Column2, ......., Column N  
      FROM TableName
     WHERE ROWNUM Between 5 AND 10) rows_to_page
    WHERE ROWNUM < end_offset) 
 WHERE rnum > start_offset;

When the Column value is NULL it would be replaced by ‘~’ in output

  SELECT NVL(FAX_NO, '~') FROM DUAL;
  SELECT NVL(NULL, '~') FROM DUAL;

The Below query can be used in scenarios where 3 drop downs are used and Location is mandatory and loaded first based on which Area and Pincode should be populated and selection can be made based on that later.

  SELECT Name, Age, PhoneNo
    FROM Person 
   WHERE Location =  p_location and 
         Area =  NVL(p_area, Area) and 
         PinCode =  NVL(p_pin_code, PinCode);

Having more than one input in where clause
i.e

 SELECT * 
  FROM Person 
 WHERE Location IN('North Chennai', 'South Chennai');

When the same query is used in search screen it may have Three possible Values

• NULL
• Single Value
• Multiple Value

The above query works for Single and Multiple value but does not work for NULL.The Above query works for NULL and Single Value but not for Multiple Value.

The below is a simple query which works when PERSON_ID is NULL, Single Value, Multiple Value(CSV).

 SELECT PERSON_ID
  FROM PERSONS P
 WHERE ((CASE
         WHEN 'P101' IS NULL THEN
          NULL
         ELSE
          'P101'
       END) IS NULL OR P.WATERFALL_ID IN ('P101'));

For Multiple Value we need to do slight modification.We need to convert the CSV values into table and give it as input.

 SELECT DISTINCT PERSON_ID
   FROM tblPerson 
  WHERE nvl(PERSON_ID, '~') IN (SELECT column_value
                                  FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))
                                UNION ALL
                                SELECT '~'
                                  FROM dual);

The select query in the where clause will take the value of P_CSV_PERSON_ID (Single or Multiple value) else it will take ~ in case it is NULL

The Other workaround to this is as below

SELECT PERSON_ID
    FROM tblPerson 
   WHERE (((CASE
           WHEN P_CSV_PERSON_ID IS NULL THEN
            NULL
           ELSE
            P_CSV_PERSON_ID
         END)) IS NULL
      OR PERSON_ID IN
         (SELECT *
            FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))));

where

 P_CSV_PERSON_ID= 'P101,P102'

For more details on CSV_TO_TABLE refer Link

Parameter in Where clause may be Null or has Value

SELECT Person_Name
  FROM tblPersons
 WHERE AREA_CODE = pAreaCode
   AND (pRationCardNo IS NULL OR RATION_ID = pRationCardNo)

select * from all_source where lower(text) like '%proc_name%'