Oracle SQL - Alternative for dynamic query

Question

I have a procedure (see below) which is using dynamic query. I wanted to rewrite this procedure with out using dynamic query. How do I write the conditions below?

PROCEDURE DemoProcedure(p_firstname  IN VARCHAR2,
                       p_lastname   IN VARCHAR2,
                       p_phone        IN VARCHAR2
                       o_Cursor     OUT t_Cursor) IS

SQLString VARCHAR2(4000);

BEGIN

    SQLString := 
        'SELECT * FROM 
        SCHEMA.TABLENAME_a A
        INNER JOIN SCHEMA.TABLENAME_b B
        ON A.ID = B.ID
        WHERE 
        A.TYPE = 1  ';

    IF p_firstname IS NOT NULL THEN
        SQLString := SQLString || ' and UPPER(A.FIRST_NAME) like UPPER( ''' || p_firstname || ''')';
    END IF;

    IF p_lastname IS NOT NULL THEN
        SQLString := SQLString || ' and UPPER(A.LAST_NAME) like UPPER( ''' || p_lastname || ''')';
    END IF;

    IF p_phone IS NOT NULL THEN
        SQLString := SQLString || ' and UPPER(A.PHONE) = ''' ||
                            p_phone || '''';
    END IF;

        SQLString := SQLString || ' order by  a.id ';

        OPEN o_Cursor FOR SQLString;

END DemoProcedure;

can you try the old " and (p_phone is not null and UPPER(A.PHONE) = p_phone)"? — jean
– jean, Commented Mar 5, 2014 at 17:00
It would be a good idea to either store the first name and last name in upper case or define a function-based index on the columns in question. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Mar 5, 2014 at 17:03
If this is a large table it may be worthwhile having this as a dynamic query, or having multiple SQL statements rather than a single catch-all. The reason being that Oracle will have trouble finding the right index to use if you have a set of xx IS NULL OR xx = col_xx statements. The right index for a last_name only search will be different to a first_name only search. However, you most definitely should learn how to use bind variables in your dynamic sql. — Rob Baillie
– Rob Baillie, Commented Mar 5, 2014 at 22:09

Justin Cave · Accepted Answer · 2014-03-05 17:00:40Z

3

It looks like you just want

OPEN o_cursor 
 FOR SELECT ...
      WHERE A.TYPE = 1
        AND (p_firstname IS NULL or upper(a.first_name) = upper(p_firstname))
        AND (p_lastname  IS NULL or upper(a.last_name)  = upper(p_lastname))
        AND (p_phone     IS NULL or upper(a.phone)      = p_phone)
      ORDER BY a.id

I'm not sure why you'd want to bother upper-casing a phone number-- do you have character data in a phone number?

answered Mar 5, 2014 at 17:00

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CoolArchTek Over a year ago

sorry that was mistake while preparing the SQL for post.

Sandeep · Accepted Answer · 2014-03-05 21:48:07Z

3

Different way writing the same SQL as @JustinCave suggested -

OPEN o_cursor 
 FOR SELECT ...
      WHERE A.TYPE = 1
        AND upper(a.first_name) = nvl(upper(p_firstname), upper(a.first_name))
        AND upper(a.last_name)  = nvl(upper(p_lastname),upper(a.last_name))
        AND upper(a.phone)      = nvl(p_phone,upper(a.phone))
      ORDER BY a.id

answered Mar 5, 2014 at 21:48

Sandeep

3492 gold badges3 silver badges9 bronze badges

1 Comment

Jon Heller Over a year ago

+1 This method may run much faster than the other answer because of some Oracle optimizations around NVL. See this Jonathan Lewis article for details.

Collectives™ on Stack Overflow

Oracle SQL - Alternative for dynamic query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related