Calling procedure in oracle apex

Question

I have created a package with two in inputs as shown below, however I am having trouble with testing the package to see if it works. I want to call the procedure and test it works with the inputs being 1 and 1000. Could anyone suggest a way of testing in Oracle Apex please? I'm not sure if I'm producing the output in the correct way inside the package, if I test the code outside of being a procedure and package it works fine. At the minute I just want to test it in the SQL Command.

Package

CREATE OR REPLACE PACKAGE pl_work_allocation_pkg
IS
  PROCEDURE pl_work_allocation_pp(lv_crime_id       IN  NUMBER,
                                  lv_emp_no         IN  NUMBER,
                                  lv_end_date       OUT DATE,
                                  lv_work_desc      OUT VARCHAR,
                                  lv_police_officer OUT NUMBER,
                                  lv_work_days      OUT NUMBER,
                                  lv_status         OUT VARCHAR);

  FUNCTION GET_WORK_DAYS1(p_work_end_date IN DATE)
    RETURN NUMBER;

  FUNCTION OVERDUE_DAYS1(p_work_end_date IN DATE)
    RETURN VARCHAR;
END;

Package body

CREATE OR REPLACE PACKAGE BODY pl_work_allocation_pkg
IS
  FUNCTION GET_WORK_DAYS1(p_work_end_date IN DATE) RETURN NUMBER
  IS
    lv_work_days NUMBER(5);
    BEGIN
      lv_work_days := ROUND(p_work_end_date - SYSDATE);
      RETURN lv_work_days;
    END get_work_days1;

  FUNCTION OVERDUE_DAYS1(p_work_end_date IN DATE) RETURN VARCHAR
  IS
    lv_status    VARCHAR(10);
    lv_work_days NUMBER(5);
    BEGIN
      lv_work_days := ROUND(p_work_end_date - SYSDATE);
      IF lv_work_days > 1
      THEN lv_status := 'DUE';
      ELSIF lv_work_days < 1
        THEN lv_status := 'OVERDUE';
      END IF;
      RETURN lv_status;
    END overdue_days1;

  PROCEDURE pl_work_allocation_pp(lv_crime_id       IN  NUMBER,
                                  lv_emp_no         IN  NUMBER,
                                  lv_end_date       OUT DATE,
                                  lv_work_desc      OUT VARCHAR,
                                  lv_police_officer OUT NUMBER,
                                  lv_work_days      OUT NUMBER,
                                  lv_status         OUT VARCHAR)
  IS
    BEGIN
      SELECT
        work_desc,
        lead_police_officer,
        work_end_date
      INTO lv_work_desc, lv_police_officer, lv_end_date
      FROM pl_work_allocation
      WHERE s_reported_crime_id = lv_crime_id
            AND d_emp_id = lv_emp_no;
      lv_work_days := GET_WORK_DAYS(lv_end_date);
      lv_status := OVERDUE_DAYS(lv_end_date);

      dbms_output.PUT_LINE(lv_emp_no || ' is ' || lv_status || ' on case no: ' ||
                           lv_crime_id || ' by ' || lv_work_days || '. Report to ' || lv_police_officer ||
                           ' for ' || lv_work_desc || ' details');

    END pl_work_allocation_pp;
END;

A package does not have inputs. You appear to have created a package with two public functions and one public procedure. The procedure has two input parameters along with 5 output parameters. Are you asking how to call that procedure from the SQL*Plus command line? — Justin Cave
Yes I want to call the procedure but not from SQL*Plus from Oracle Apex SQL Commands. — user1643333
What does "Oracle Apex SQL Commands" mean? Are you talking about the interactive SQL command window that is in the APEX builder environment? Or are you trying to do something in the context of an actual APEX application? — Justin Cave
In the interactive SQL command window. I basically want to provide the two input values to get the correct output, I have tried a few queries put no of them seem to work. I'm not sure if I have set the output correctly in the procedure. — user1643333

Justin Cave Justin Cave · Accepted Answer · 2014-05-09T21:04:58

It sounds like you want

DECLARE
  lv_end_date       date;
  -- Guessing at the length.  You'll be better served using anchored
  -- types everywhere
  lv_work_desc      varchar2(100);  
  lv_police_officer number;
  lv_work_days      number;
  lv_staus          varchar2(10);
BEGIN
 PL_WORK_ALLOCATION_PKG.PL_WORK_ALLOCATION_PP(
    <<first parameter>>,
    <<second parameter>>,
    LV_END_DATE,
    LV_WORK_DESC,
    LV_POLICE_OFFICER,
    LV_WORK_DAYS,
    LV_STATUS );
END;

Of course, you probably want to do something with the values that are returned from your call.

Calling procedure in oracle apex

1 Answers