Checking Null Parameters in Oracle Stored Procedure

Posted on 2007-07-26
Last Modified: 2013-12-18
I am trying to create an Oracle Stored Procedure in 10G for a search page.

My problem is I do not know how to do a check in the where clause for Null parameters being passed in.

When a Null parameter is passed in then the user doesnt want to search on that field and so all records should be returned.

This code below will work correctly BUT doesnt include the check for Null parameters.

create or replace package body searchRecords is
procedure searchRecord(o_rpt out resultset, i_firstname in varchar2, i_lastname in varchar2) is
  open o_rpt for
    select * from users where users.firstname = i_firstname and users.lastname = i_lastname;
end searchRecords;

I am able to do this in SQL Server e.g.

Where (users.firstname =
CASE WHEN (i_firstname IS NULL) THEN
Question by:Rosscowie
    LVL 20

    Expert Comment

    this should work

    Where users.firstname =
    ( CASE WHEN i_firstname IS NULL THEN  users.firstname
        ELSE  i_firstname

    Author Comment

    Thanks. This is very much like the SQL Server equivalent.
    One final part is how to check Null values in the table. In SQL Server I would normally use a IsNull function but it doesn't seem to work in Oracle.

    Example from SQL Server:-

    Where IsNull(users.firstname, 0) =
    ( CASE WHEN i_firstname IS NULL THEN  IsNull(users.firstname,0)
        ELSE  i_firstname

    This just check null in the table.

    Is there a IsNull function in Oracle
    LVL 20

    Accepted Solution

    The equivalent is nvl(value, default)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle sql question 9 58
    Value of 0's not appearing. 9 40
    Out of Sequence numbers for today 25 34
    Need help with Oracle syntax 4 31
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to recover a database from a user managed backup

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now