【Oracle】PLS-00306の原因と解決方法｜wrong number or types of arguments in call

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

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

CREATE OR REPLACE PROCEDURE p_sync_order(
  p_order_id IN NUMBER,
  p_status   IN VARCHAR2
) AS
BEGIN
  NULL;
END;
/

-- NG: 第2引数が足りない
BEGIN
  p_sync_order(1001);
END;
/
-- PLS-00306

-- OK
BEGIN
  p_sync_order(1001, 'DONE');
END;
/

CREATE OR REPLACE PROCEDURE p_set_due_date(
  p_due_date IN DATE
) AS
BEGIN
  NULL;
END;
/

-- NGになりやすい: 文字列を日付として渡している
BEGIN
  p_set_due_date('2026-05-14');
END;
/

-- OK: DATEリテラルまたは明示変換
BEGIN
  p_set_due_date(DATE '2026-05-14');
END;
/

CREATE OR REPLACE PROCEDURE p_get_status(
  p_order_id IN  NUMBER,
  p_status   OUT VARCHAR2
) AS
BEGIN
  p_status := 'DONE';
END;
/

-- NG: OUT引数に文字列リテラルは渡せない
BEGIN
  p_get_status(1001, '');
END;
/

-- OK: 変数を渡す
DECLARE
  v_status VARCHAR2(20);
BEGIN
  p_get_status(1001, v_status);
  DBMS_OUTPUT.PUT_LINE(v_status);
END;
/

CREATE OR REPLACE PROCEDURE p_create_user(
  p_user_id IN NUMBER,
  p_email   IN VARCHAR2,
  p_status  IN VARCHAR2 DEFAULT 'ACTIVE'
) AS
BEGIN
  NULL;
END;
/

-- OK: 名前付き記法
BEGIN
  p_create_user(
    p_user_id => 10,
    p_email   => 'a@example.com'
  );
END;
/

-- NG: パラメータ名が違う
BEGIN
  p_create_user(
    user_id => 10,
    email   => 'a@example.com'
  );
END;
/

CREATE OR REPLACE PROCEDURE p_search_order(
  p_customer_id IN NUMBER,
  p_status      IN VARCHAR2 DEFAULT 'ACTIVE',
  p_limit       IN NUMBER   DEFAULT 100
) AS
BEGIN
  NULL;
END;
/

-- OK: 末尾側のデフォルト引数を省略
BEGIN
  p_search_order(10);
END;
/

-- OK: 途中を飛ばすなら名前付き記法
BEGIN
  p_search_order(
    p_customer_id => 10,
    p_limit       => 50
  );
END;
/

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

CREATE OR REPLACE PACKAGE pkg_notify AS
  PROCEDURE send(p_user_id IN NUMBER);
  PROCEDURE send(p_email   IN VARCHAR2);
END pkg_notify;
/

-- NGになりやすい: NULLだけではどちらのsendか決められない
BEGIN
  pkg_notify.send(NULL);
END;
/

-- OK: 呼びたい定義の型を明示する
BEGIN
  pkg_notify.send(CAST(NULL AS VARCHAR2));
END;
/

CREATE OR REPLACE PACKAGE pkg_order AS
  TYPE t_order_ids IS TABLE OF NUMBER;
  PROCEDURE close_orders(p_ids IN t_order_ids);
END pkg_order;
/

-- OK: パッケージで定義された型を使う
DECLARE
  v_ids pkg_order.t_order_ids := pkg_order.t_order_ids(1, 2, 3);
BEGIN
  pkg_order.close_orders(v_ids);
END;
/

BEGIN
  p_sync_order(1001);
END;
/

-- ORA-06550: line 2, column 3:
-- PLS-00306: wrong number or types of arguments in call to 'P_SYNC_ORDER'
-- ORA-06550: line 2, column 3:
-- PL/SQL: Statement ignored

DEFINE owner_name = 'APP_SCHEMA'
DEFINE proc_name  = 'SYNC_ORDER'

SELECT owner,
       package_name,
       object_name,
       overload,
       position,
       argument_name,
       data_type,
       type_owner,
       type_name,
       in_out,
       defaulted
FROM all_arguments
WHERE owner = UPPER('&owner_name')
  AND object_name = UPPER('&proc_name')
ORDER BY package_name, object_name, overload, sequence;