Get all records if table type parameter is null

Question

In Oracle, I want to get all values of column if the table valued parameter is null otherwise only the matched records.

create or replace PROCEDURE pr_Employees (
    lastnames IN LastName,
    rowCursor OUT SYS_REFCURSOR) IS
BEGIN
Select * from emp where lastname in (
    SELECT COLUMN_VALUE FROM TABLE(lastnames)
)

William Robertson · Accepted Answer · 2018-07-11 16:16:58Z

1

Try this:

create or replace procedure pr_employees
    ( lastnames in lastname
    , rowcursor out sys_refcursor )
is
begin
    open rowcursor for
        select * from emp
        where  lastname in
               ( select column_value from table(lastnames) )
        or     lastnames is empty;
end pr_employees;

answered Jul 11, 2018 at 16:16

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

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

2 Comments

Ram Over a year ago

Thank you for your help. I tried above one, If I passed lastnames is null it is returning 0 records.

William Robertson Over a year ago

How is last name defined? I made mine a table of varchar2(60). How are you setting the parameter value when you call it? I've handled the case where you pass an empty collection, not a collection of null values.

Collectives™ on Stack Overflow

Get all records if table type parameter is null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related