CODEWITHSUNDEEP
sqloracle-databaseplsql
Ram
Ram

Reputation: 21

Get all records if table type parameter is null

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)
)

Upvotes: 0

Views: 48

Answers (1)

William Robertson
William Robertson

Reputation: 16001

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;

Upvotes: 1

Related Questions