Oracle PL/sql if input varchar param is empty, how to return all the records?

Question

I have below query. If the input parameter is not null and passed a value, it will return all the matching records for that URL. if the Input parameter is empty, it should return all the records. How to fix that Update condition?

CREATE OR REPLACE PROCEDURE GetDatesList (
p_URL               IN varchar2,
P_RECORDSET         OUT SYS_REFCURSOR 
)
as

begin

OPEN P_RECORDSET FOR
select 
      PCBS.KeyId as Key, PCBS.PublishDate
from (select 
      Serlog.*, PS.ServerType, row_number() over (partition by Serlog.KeyId order by Serlog.PublishDate desc) as RowNu
      from PConfigByServerLog Serlog 
      join PServer PS on PS.ServerName = Serlog.ServerName 
      and Serlog.URL = PS.URL
      where Serlog.URL = p_URL
      --Here if we pass a value f p_podURL, we get those matching records back. If it is empty, then it should bring all the records back 
      ) PCBS 
where PCBS.RowNu = 1 and PCBS.IsActive = 'T';

end;

Matthew McPeak · Accepted Answer · 2015-09-04 03:27:49Z

3

 where Serlog.URL = p_URL OR p_URL is null

or

 where Serlog.URL = nvl(p_URL, Serlog.URL)

Note about performance

One of the other answers warned about performance.

If Serlog.URL is indexed, Oracle will be smart enough to use it if p_URL is not null.

Consider a SQL like this:

SELECT * FROM sys.obj$
where obj# = nvl(:b1, obj# )

The plan would be:

7 SELECT STATEMENT
    6 CONCATENATION  
        2 FILTER  
            1 TABLE ACCESS FULL SYS.OBJ$
        5 FILTER  
            4 TABLE ACCESS BY INDEX ROWID SYS.OBJ$
                3 INDEX RANGE SCAN SYS.I_OBJ1

The two filter operations (#2 and #5) are :b1 is null and :b1 is not null, respectively. So, Oracle will only execute whichever branch of the plan make sense, depending on whether the parameter has been given.

edited Sep 4, 2015 at 3:27

answered Sep 3, 2015 at 19:48

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

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

Comments

Carlo Sirna · Accepted Answer · 2015-09-03 19:47:43Z

2

Just write

     where (Serlog.URL = p_URL) OR p_URL is null

Instead of

       where Serlog.URL = p_URL

Keep in mind that this would give you suboptimal ececution plan: if the url parameter is indexed it might not use the index.

If performance is something to be concerned, you'd better handle separately the two cases with two different cursors

answered Sep 3, 2015 at 19:47

Carlo Sirna

1,2517 silver badges12 bronze badges

Comments

Gary_W · Accepted Answer · 2015-09-03 19:58:22Z

0

You could also set a default value for p_URL in the procedure definition if that is appropriate:

CREATE OR REPLACE PROCEDURE GetDatesList (
p_URL               IN varchar2 DEFAULT '//svr1/dir1/folder2/etc',
P_RECORDSET         OUT SYS_REFCURSOR 
)
as

So if NULL is passed in this value will be used instead.

answered Sep 3, 2015 at 19:58

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Collectives™ on Stack Overflow

Oracle PL/sql if input varchar param is empty, how to return all the records?

3 Answers 3

Note about performance

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Note about performance

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related