How do I create a package that contains a stored procedure with 2 parameters and selects data from a table?

Question

I can't seem to be able to run a package that contains a stored procedure that contains two parameters and outputs a table based on those parameters. The goal is to create a stored procedure that takes in the inputs, new_date and new_entity, and return the data from the table that matches these values.

CREATE OR REPLACE PACKAGE test1 IS
    PROCEDURE stored_proc (new_date IN VARCHAR2, new_entity IN VARCHAR2, output OUT SYS_REFCURSOR);
END;

CREATE OR REPLACE PACKAGE BODY test1 IS
    PROCEDURE stored_proc (new_date IN VARCHAR2, new_entity IN VARCHAR2, output OUT SYS_REFCURSOR) IS
       BEGIN
             OPEN output FOR select * FROM atlas_report_dates
             WHERE report_date =  TO_DATE(new_date,'mm/dd/yyyy') AND entity = new_entity;
       END;
    END;

BEGIN
    test1.stored_proc('6/12/2023', 'MLFS');
END;

I'm getting errors ORA-06550, PLS-00306, ORA-06550

Your procedure does not have two parameters. It has three (two `IN` and one `OUT`). — MT0, Jul 06 '23 at 00:31

MT0 · Answer 1 · 2023-07-06T00:27:17.897

Your procedures are fine (you just need to make sure you terminate the PL/SQL blocks with the / terminator on a new line after the END; statement).

The anonymous block you use to call the procedure is incorrect as the procedure takes three arguments and you have only provided two. You need to supply the cursor argument for the OUT parameter.

CREATE TABLE atlas_report_dates (report_date, entity) AS
SELECT DATE '2023-06-12', 'MLFS' FROM DUAL;

CREATE OR REPLACE PACKAGE test1 IS
  PROCEDURE stored_proc (new_date IN VARCHAR2, new_entity IN VARCHAR2, output OUT SYS_REFCURSOR);
END;
/

CREATE OR REPLACE PACKAGE BODY test1 IS
  PROCEDURE stored_proc (new_date IN VARCHAR2, new_entity IN VARCHAR2, output OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN output FOR select * FROM atlas_report_dates
    WHERE report_date =  TO_DATE(new_date,'mm/dd/yyyy') AND entity = new_entity;
  END;
END;
/

Then:

DECLARE
  v_cur SYS_REFCURSOR;
BEGIN
  test1.stored_proc('6/12/2023', 'MLFS', v_cur);
END;
/

Works (but does not do anything as you have not told it what to do with the cursor).

Better, would be to use the column data types and pass dates as dates (and not strings):

CREATE OR REPLACE PACKAGE test1 IS
  PROCEDURE stored_proc (
    new_date   IN  ATLAS_REPORT_DATES.REPORT_DATE%TYPE,
    new_entity IN  ATLAS_REPORT_DATES.ENTITY%TYPE,
    output     OUT SYS_REFCURSOR
  );
END;
/

CREATE OR REPLACE PACKAGE BODY test1 IS
  PROCEDURE stored_proc (
    new_date   IN  ATLAS_REPORT_DATES.REPORT_DATE%TYPE,
    new_entity IN  ATLAS_REPORT_DATES.ENTITY%TYPE,
    output     OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN output FOR
      SELECT *
      FROM   atlas_report_dates
      WHERE  report_date = new_date
      AND    entity      = new_entity;
  END;
END;
/

Then you can call it using:

DECLARE
  v_cur SYS_REFCURSOR;
  v_row ATLAS_REPORT_DATES%ROWTYPE;
BEGIN
  test1.stored_proc(DATE '2023-06-12', 'MLFS', v_cur);

  LOOP
    FETCH v_cur INTO v_row;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_row.report_date || ', ' || v_row.entity);
  END LOOP;
END;
/

And the output for the sample table is:

2023-06-12 00:00:00, MLFS

fiddle

How do I create a package that contains a stored procedure with 2 parameters and selects data from a table?

1 Answers1