Oracle PLSQL problems creating a procedure

Question

I am trying to wrap some SQL into a PLSQL procedure so a user can pass parameters instead of manually editing a WHERE clause, which would give them the potential to break the working code. The SQL code, which I'm porting is embedded in the PROCEDURE with the exception of the INTO clause.

I know in PLSQL in order to SELECT rows there needs to be an INTO clause. After looking around I saw an example, which creates an object and table type. Something I didn't want to do and seems overly complicated to me. If possible I want to keep everything local to the procedure.

I'm also open to perhaps using BULK collect on the access_history table if that would be a more efficient method.

When I try creating the procedure below It doesn't work and this is where I can use some help and PLSQL expertise to guide me in the best direction to produce the desired data.

Secondly, is there a way to use a DEFAULT value to determine the number of rows to be retrieved.

If the procedure is called like this:

EXEC LAST_EMPLOYEE_HISTORY(1) this means get the last 20 (DEFAULT) rows for employee_id=1, where 20 is the default value.

If the procedure is called like this:

EXEC LAST_EMPLOYEE_HISTORY(1, 50) means get the last 50 rows for employee_id=1.

Any help and expertise in explaininf and helping me fix my issues would be greatly appreciated. Thanks in advance to all who answer.

Below is my test CASE.

    ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


    CREATE OR REPLACE TYPE access_history_obj AS OBJECT(
    employee_id    NUMBER(6), 
    first_name       VARCHAR2(20),
     last_name         VARCHAR2(20),
     card_num         VARCHAR2(10),
     location_id        NUMBER(6),
    location_name  VARCHAR2(30),
    access_date      DATE
    );
 

    CREATE OR REPLACE TYPE access_history_table IS TABLE OF access_history_obj;

    Create table employees(
      employee_id NUMBER(6), 
      first_name VARCHAR2(20),
      last_name VARCHAR2(20),
     card_num VARCHAR2(10),
      work_days VARCHAR2(7)
   );

    INSERT INTO employees (
     employee_id,
     first_name, 
     last_name,
     card_num,
     work_days
    )
    WITH names AS   ( 
      SELECT 1, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
      SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
    SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
     SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' FROM dual 
   ) SELECT * FROM names; 

  CREATE TABLE locations AS
      SELECT level AS location_id,
         'Door ' || level AS location_name,

      CASE round(dbms_random.value(1,3)) 
              WHEN 1 THEN 'A' 
              WHEN 2 THEN 'T' 
              WHEN 3 THEN 'T' 
           END AS location_type

      FROM   dual
      CONNECT BY level <= 5;
       
      create table access_history(
        seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
         employee_id NUMBER(6), 
         card_num varchar2(10),
         location_id number(4),
         access_date date,
         processed NUMBER(1) default 0
        );

   INSERT INTO       access_history(
      employee_id,
       card_num,
       location_id,
       access_date
     )
 WITH rws AS   ( 
      SELECT 1,'J11111',2,TO_DATE('2021/08/15 08:30:25', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL
    SELECT 1,'J11111',3,TO_DATE('2021/08/15 18:30:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL
   SELECT 2,'E11111',2,TO_DATE('2021/08/15 11:20:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual) SELECT * FROM rws; 

            CREATE OR REPLACE PROCEDURE LAST_EMPLOYEE_HISTORY( 
    p_employee_id IN NUMBER,
     p_rws IN number)
     AS
    BEGIN
     with rws as (
         select e.employee_id, 
              e.first_name,
              e.last_name,
              e.card_num,
              l.location_id,
              l.location_name,
              a.access_date,
              row_number () over 
          (
           partition by e.employee_id
            order by a.access_date DESC
          ) rn  
         FROM  employees    e
        JOIN     access_history a ON a.employee_id = e.employee_id
 JOIN  locations     l ON l.location_id = a.location_id
         )

        select employee_id, 
                first_name,
                last_name,
                card_num,
                location_id,
                location_name,
               access_date INTO access_history_table
      from rws 
      where 
         employee_id = p_employee_id AND
         rn <= p_rws
         order by employee_id,  access_date desc;
      END;

    EXEC LAST_EMPLOYEE_HISTORY(1)

Answer 1

Have a cursor as an OUT parameter and use DEFAULT in the signature of the procedure:

CREATE PROCEDURE LAST_EMPLOYEE_HISTORY( 
  i_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
  i_rws         IN PLS_INTEGER DEFAULT 20,
  o_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN o_cursor FOR
    SELECT e.employee_id, 
           e.first_name,
           e.last_name,
           e.card_num,
           l.location_id,
           l.location_name,
           a.access_date
    FROM   employees    e
           INNER JOIN access_history a
           ON a.employee_id = e.employee_id
           INNER JOIN locations l
           ON l.location_id = a.location_id
    WHERE  e.employee_id = i_employee_id
    ORDER BY access_date DESC
    FETCH FIRST i_rws ROWS ONLY;
END;
/

Then in SQL/Plus or SQL Developer:

VARIABLE cur REFCURSOR;
EXECUTE LAST_EMPLOYEE_HISTORY(1, 50, :cur);
PRINT cur;

db<>fiddle here

Note: From Oracle 12, you can the use FETCH FIRST n ROWS ONLY syntax.