【Oracle】PLS-00323の原因と解決方法｜package specification and bodyの不一致

CREATE OR REPLACE PACKAGE pkg_order AS
  PROCEDURE create_order(p_order_id IN NUMBER);
  PROCEDURE close_order(p_order_id IN NUMBER);
END pkg_order;
/

CREATE OR REPLACE PACKAGE BODY pkg_order AS
  PROCEDURE create_order(p_order_id IN NUMBER) AS
  BEGIN
    NULL;
  END create_order;
END pkg_order;
/
-- PLS-00323: close_order is declared in a package specification
-- and must be defined in the package body

CREATE OR REPLACE PACKAGE BODY pkg_order AS
  PROCEDURE create_order(p_order_id IN NUMBER) AS
  BEGIN
    NULL;
  END create_order;

  PROCEDURE close_order(p_order_id IN NUMBER) AS
  BEGIN
    NULL;
  END close_order;
END pkg_order;
/

CREATE OR REPLACE PACKAGE pkg_report AS
  PROCEDURE export_csv(p_report_id IN NUMBER);
END pkg_report;
/

CREATE OR REPLACE PACKAGE BODY pkg_report AS
  -- NG: 仕様部はexport_csv、本体はexport_csv_file
  PROCEDURE export_csv_file(p_report_id IN NUMBER) AS
  BEGIN
    NULL;
  END export_csv_file;
END pkg_report;
/

CREATE OR REPLACE PACKAGE emp_bonus AS
  PROCEDURE calc_bonus(p_hired employees.hire_date%TYPE);
END emp_bonus;
/

CREATE OR REPLACE PACKAGE BODY emp_bonus AS
  -- NG: 実体はDATEでも、仕様部の%TYPEと見出しが一致しない
  PROCEDURE calc_bonus(p_hired DATE) AS
  BEGIN
    NULL;
  END calc_bonus;
END emp_bonus;
/

CREATE OR REPLACE PACKAGE BODY emp_bonus AS
  PROCEDURE calc_bonus(p_hired employees.hire_date%TYPE) AS
  BEGIN
    NULL;
  END calc_bonus;
END emp_bonus;
/

CREATE OR REPLACE PACKAGE pkg_price AS
  FUNCTION get_total(p_order_id IN NUMBER) RETURN NUMBER;
END pkg_price;
/

CREATE OR REPLACE PACKAGE BODY pkg_price AS
  -- NG: 仕様部はNUMBER、本体はVARCHAR2
  FUNCTION get_total(p_order_id IN NUMBER) RETURN VARCHAR2 AS
  BEGIN
    RETURN '1000';
  END get_total;
END pkg_price;
/

CREATE OR REPLACE PACKAGE pkg_emp AS
  CURSOR c_high_salary RETURN employees%ROWTYPE;
END pkg_emp;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp AS
  -- NG: c_high_salaryの定義がない
  PROCEDURE dummy AS
  BEGIN
    NULL;
  END dummy;
END pkg_emp;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp AS
  CURSOR c_high_salary RETURN employees%ROWTYPE IS
    SELECT *
    FROM employees
    WHERE salary > 2500;
END pkg_emp;
/

CREATE OR REPLACE PACKAGE pkg_notify AS
  PROCEDURE send_mail(
    p_user_id IN NUMBER,
    p_force   IN BOOLEAN DEFAULT FALSE
  );
END pkg_notify;
/

CREATE OR REPLACE PACKAGE BODY pkg_notify AS
  -- OKに寄せる: 仕様部と同じ見出しにする
  PROCEDURE send_mail(
    p_user_id IN NUMBER,
    p_force   IN BOOLEAN DEFAULT FALSE
  ) AS
  BEGIN
    NULL;
  END send_mail;
END pkg_notify;
/

SELECT name,
       type,
       line,
       position,
       text
FROM user_errors
WHERE name = 'PKG_ORDER'
  AND type IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY sequence;

SELECT owner,
       name,
       type,
       line,
       position,
       text
FROM all_errors
WHERE owner = 'APP_SCHEMA'
  AND name = 'PKG_ORDER'
ORDER BY sequence;

DEFINE owner_name   = 'APP_SCHEMA'
DEFINE package_name = 'PKG_ORDER'
DEFINE member_name  = 'CLOSE_ORDER'

SELECT type,
       line,
       text
FROM all_source
WHERE owner = UPPER('&owner_name')
  AND name = UPPER('&package_name')
  AND UPPER(text) LIKE '%' || UPPER('&member_name') || '%'
ORDER BY type, line;

SELECT owner,
       object_name AS package_name,
       procedure_name
FROM all_procedures
WHERE owner = 'APP_SCHEMA'
  AND object_name = 'PKG_ORDER'
ORDER BY procedure_name;

SELECT package_name,
       object_name,
       argument_name,
       position,
       data_type,
       in_out
FROM all_arguments
WHERE owner = 'APP_SCHEMA'
  AND package_name = 'PKG_ORDER'
ORDER BY object_name, overload, sequence;

SELECT object_name,
       object_type,
       status,
       last_ddl_time
FROM user_objects
WHERE object_name = 'PKG_ORDER'
  AND object_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY object_type;

SELECT name, type, line, position, text
FROM user_errors
WHERE name = 'PKG_ORDER'
ORDER BY sequence;