【Oracle】PLS-00201の原因と解決方法｜identifier must be declared・識別子を宣言してください

SELECT name,
       type,
       sequence,
       line,
       position,
       text
FROM user_errors
WHERE text LIKE '%PLS-00201%'
ORDER BY name, type, sequence;

-- NG: v_nameを宣言していない
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
-- PLS-00201: identifier 'V_NAME' must be declared

-- OK: DECLARE部で宣言する
DECLARE
  v_name VARCHAR2(100) := 'Alice';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

DECLARE
  v_outer NUMBER := 10;
BEGIN
  DECLARE
    v_inner NUMBER := 20;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_outer + v_inner); -- OK
  END;

  DBMS_OUTPUT.PUT_LINE(v_inner); -- NG: ここでは見えない
END;
/
-- PLS-00201: identifier 'V_INNER' must be declared

-- 呼び出し先が見えるか確認
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name = 'UTIL_PKG'
ORDER BY owner, object_type;

-- EXECUTE権限があるか確認
SELECT owner, table_name, privilege, grantee
FROM all_tab_privs
WHERE table_name = 'UTIL_PKG'
ORDER BY owner, grantee, privilege;

-- 付与する例（所有者側で実行）
GRANT EXECUTE ON master_schema.util_pkg TO app_schema;

-- 型が存在するか確認
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name IN ('ORDER_REC_TYPE', 'ORDER_TAB_TYPE')
  AND object_type IN ('TYPE', 'TYPE BODY')
ORDER BY owner, object_type, object_name;

-- TYPEへのEXECUTE権限を確認
SELECT owner, table_name, privilege, grantee
FROM all_tab_privs
WHERE table_name IN ('ORDER_REC_TYPE', 'ORDER_TAB_TYPE')
ORDER BY owner, table_name, grantee;

-- 付与例
GRANT EXECUTE ON master_schema.order_rec_type TO app_schema;
GRANT EXECUTE ON master_schema.order_tab_type TO app_schema;

-- NGになりやすい例: 表が見えない、または列名が違う
CREATE OR REPLACE PROCEDURE p_sync_customer(
  p_customer_id IN master_schema.customers.customer_id%TYPE
) AS
BEGIN
  NULL;
END;
/

-- 確認: 表と列が見えるか
SELECT owner, table_name, column_name, data_type
FROM all_tab_columns
WHERE owner = 'MASTER_SCHEMA'
  AND table_name = 'CUSTOMERS'
  AND column_name = 'CUSTOMER_ID';

-- 権限付与例
GRANT SELECT ON master_schema.customers TO app_schema;

-- Invoker Rightsの例
CREATE OR REPLACE PROCEDURE p_list_customers
AUTHID CURRENT_USER
AS
BEGIN
  FOR r IN (SELECT customer_id FROM customers) LOOP
    NULL;
  END LOOP;
END;
/

-- 実行ユーザーごとに customers の解決先や権限が変わる点に注意

-- 仕様部: sync_orderだけ公開
CREATE OR REPLACE PACKAGE pkg_order AS
  PROCEDURE sync_order;
END pkg_order;
/

-- 本体: private_helperは本体内だけで使える
CREATE OR REPLACE PACKAGE BODY pkg_order AS
  PROCEDURE private_helper IS
  BEGIN
    NULL;
  END;

  PROCEDURE sync_order IS
  BEGIN
    private_helper;
  END;
END pkg_order;
/

-- 外部から pkg_order.private_helper は呼べない
-- PLS-00201 になる

-- 表やビューが見えるか確認
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name = 'CUSTOMERS'
  AND object_type IN ('TABLE', 'VIEW')
ORDER BY owner, object_type;

-- 権限付与例
GRANT SELECT ON master_schema.customers TO app_schema;

-- PL/SQL内ではスキーマ修飾して参照
SELECT customer_name
INTO v_name
FROM master_schema.customers
WHERE customer_id = p_customer_id;

SELECT owner,
       synonym_name,
       table_owner,
       table_name,
       db_link
FROM all_synonyms
WHERE synonym_name IN ('UTIL_PKG', 'CUSTOMERS')
ORDER BY owner, synonym_name;

-- シノニムを作成する例
CREATE SYNONYM app_schema.util_pkg FOR master_schema.util_pkg;

-- ただしEXECUTE権限も必要
GRANT EXECUTE ON master_schema.util_pkg TO app_schema;

-- スペル確認: DBMS_OUTPUT.PUT_LINE が正しい
BEGIN
  DBMS_OUTPUT.PUT_LINE('test');
END;
/

-- 権限確認
SELECT owner, table_name, privilege, grantee
FROM all_tab_privs
WHERE table_name = 'DBMS_OUTPUT'
ORDER BY grantee, privilege;

BEGIN
  unknown_pkg.run;
END;
/

-- ORA-06550: line 2, column 3:
-- PLS-00201: identifier 'UNKNOWN_PKG.RUN' must be declared
-- ORA-06550: line 2, column 3:
-- PL/SQL: Statement ignored

DEFINE obj_name = 'UTIL_PKG'

SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name = UPPER('&obj_name')
ORDER BY owner, object_type;

SELECT owner, table_name, privilege, grantee
FROM all_tab_privs
WHERE table_name = UPPER('&obj_name')
ORDER BY owner, grantee, privilege;

SELECT owner, synonym_name, table_owner, table_name, db_link
FROM all_synonyms
WHERE synonym_name = UPPER('&obj_name')
ORDER BY owner, synonym_name;