PL/SQL Record TYPE in Anonymous Block

Question

I wanted a function to return multiple values for my program. So I wrote a function that returns value in a record type. I can compile the package without issues. And also it runs fine when called from code. But when I use an anonymous block to retrieve the values returned by the function I get error. What's the issue?

[Error] Execution (60: 20): ORA-06550: line 11, column 20: PLS-00382: expression is of wrong type ORA-06550: line 11, column 3: PL/SQL: Statement ignored

PACKAGE SPEC

CREATE OR REPLACE package cust_wfm_budget
is
  TYPE cap_details_rec IS RECORD (
      cap_month VARCHAR2(55),
      overcap_per NUMBER,
      undercap_per NUMBER
  );

  function get_cap_month_fringes (
      p_sal_distribution_id IN NUMBER,
      p_cap_limit           IN NUMBER
  )
  return cap_details_rec;

end cust_wfm_budget;

PACKAGE BODY

CREATE OR REPLACE package body cust_wfm_budget
is

  l_cap_details cap_details_rec;

  FUNCTION get_cap_month_fringes (
      p_sal_distribution_id IN NUMBER,
      p_cap_limit           IN NUMBER
  )
  RETURN cap_details_rec
  IS
      lv_return           VARCHAR2(55):='TEST'; -- Keep as string
      l_overcap_per       NUMBER := 0; -- Assign numeric values directly
      l_undercap_per       NUMBER := 0;
  BEGIN
      -- Replace this logic with actual calculations for overcap_per and undercap_per
    --  l_overcap_per := /* Calculate overcap percentage */;
    --  l_undercap_per := /* Calculate undercap percentage */;

      RETURN cap_details_rec(lv_return, l_overcap_per, l_undercap_per);
  END get_cap_month_fringes;

end cust_wfm_budget;

/

COMMIT;

ANONYMOUS BLOCK

DECLARE
  
     TYPE cap_details_rec IS RECORD (
                                cap_month VARCHAR2(55),
                                overcap_per NUMBER,
                                undercap_per NUMBER
                               );

  l_cap_details cap_details_rec;
BEGIN
  l_cap_details := cust_wfm_budget.get_cap_month_fringes(1, 1);
  -- Do something with l_cap_details
END;

Answer 1

The issue is that you're not using the same type that your package expects. Just because you declared the type in your anonymous block with the same structure as the one in the package does not mean that you can interchange them.

Instead, all you need to do is use the type you declared in your package spec instead of creating a new type, e.g.:

DECLARE
  l_cap_details cust_wfm_budget.cap_details_rec;
BEGIN
  l_cap_details := cust_wfm_budget.get_cap_month_fringes(1, 1);
  -- Do something with l_cap_details
END;
/

This is demonstrated in this db<>fiddle.