【Oracle】PLS-00302の原因と解決方法｜component must be declared

CREATE OR REPLACE PACKAGE pkg_order AS
  PROCEDURE create_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;

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

-- NG: close_orderは仕様部にないため外部から見えない
BEGIN
  pkg_order.close_order(1001);
END;
/
-- PLS-00302

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;
/

DEFINE owner_name   = 'APP_SCHEMA'
DEFINE package_name = 'PKG_ORDER'

-- 参照可能なパッケージ内サブプログラムを確認
SELECT owner,
       object_name AS package_name,
       procedure_name,
       object_type
FROM all_procedures
WHERE owner = UPPER('&owner_name')
  AND object_name = UPPER('&package_name')
ORDER BY procedure_name;

-- 仕様部の宣言をソースで確認
SELECT line, text
FROM all_source
WHERE owner = UPPER('&owner_name')
  AND name = UPPER('&package_name')
  AND type = 'PACKAGE'
ORDER BY line;

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_textになっている
  PROCEDURE export_text(p_report_id IN NUMBER) AS
  BEGIN
    NULL;
  END;
END pkg_report;
/

DECLARE
  TYPE t_customer IS RECORD (
    customer_id NUMBER,
    full_name   VARCHAR2(100)
  );
  v_customer t_customer;
BEGIN
  -- NG: nameというフィールドはない
  DBMS_OUTPUT.PUT_LINE(v_customer.name);

  -- OK
  DBMS_OUTPUT.PUT_LINE(v_customer.full_name);
END;
/

-- テーブルの%ROWTYPEを使っている場合は、実列名を確認
SELECT column_name, data_type, data_length
FROM all_tab_columns
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'CUSTOMERS'
ORDER BY column_id;

-- カーソルや独自RECORDの場合は、SELECT句やTYPE定義の別名が正
-- テーブル列名と同じとは限らない点に注意

DECLARE
  CURSOR c_orders IS
    SELECT order_id,
           amount * tax_rate AS tax_amount
    FROM orders;
BEGIN
  FOR r IN c_orders LOOP
    -- NG: taxという列名はカーソルにない
    DBMS_OUTPUT.PUT_LINE(r.tax);

    -- OK: SELECT句の別名を使う
    DBMS_OUTPUT.PUT_LINE(r.tax_amount);
  END LOOP;
END;
/

CREATE OR REPLACE TYPE order_obj AS OBJECT (
  order_id NUMBER,
  status   VARCHAR2(20)
);
/

DECLARE
  v_order order_obj := order_obj(1001, 'DONE');
BEGIN
  -- NG: status_nameという属性はない
  DBMS_OUTPUT.PUT_LINE(v_order.status_name);

  -- OK
  DBMS_OUTPUT.PUT_LINE(v_order.status);
END;
/

SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name = 'PKG_ORDER'
ORDER BY owner, object_type;

SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'PKG_ORDER';

SELECT owner, table_name, grantee, privilege
FROM all_tab_privs
WHERE table_name = 'PKG_ORDER'
  AND privilege = 'EXECUTE';

DEFINE owner_name   = 'APP_SCHEMA'
DEFINE package_name = 'PKG_ORDER'

-- パッケージの存在と状態
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE owner = UPPER('&owner_name')
  AND object_name = UPPER('&package_name')
ORDER BY object_type;

-- 公開サブプログラムと引数
SELECT owner,
       object_name AS package_name,
       procedure_name,
       object_type
FROM all_procedures
WHERE owner = UPPER('&owner_name')
  AND object_name = UPPER('&package_name')
ORDER BY procedure_name;

-- 引数情報
SELECT owner,
       package_name,
       object_name,
       overload,
       argument_name,
       position,
       data_type,
       in_out
FROM all_arguments
WHERE owner = UPPER('&owner_name')
  AND package_name = UPPER('&package_name')
ORDER BY object_name, overload, sequence;

-- コンパイルエラー
SELECT name, type, line, position, text
FROM all_errors
WHERE owner = UPPER('&owner_name')
  AND name = UPPER('&package_name')
ORDER BY sequence;