I am building my first function in PLSQL, it needs to return a single summed value of spending by a user based off parameters username and fiscal year

Question

The function compiles, but when I run this block:

 select sumyUserSpending('ALCraft', 15) from infor.credit_card_transaction

It throws error ORA-06575: Package or function sumyuserspending is in an invalid state.

When I run this block:

 declare
    answer number;
    begin
      answer := sumyUserSpending('ALCraft', 15);
      dbms_output.put_line(answer);
      end;

It throws errors ORA-65550 and PLS-00905: object sumyuserspending is invalid.

Here is my function. When I only run the function, it throws no errors, so I am lost as to what it could need to run smoothly. When the query is taken out of the function and I run the query alone, it returns the value I want based on the placeholder values I put in for the parameters. I am working in oracle 12c.

create or replace function sumyUserSpending (userN in varchar2, fiscYear in number)
  return number
  is
  total number;
  
  cursor search1 is
  select sum(infor.credit_card_transaction.due_cc_co_amount) into total from infor.credit_card_transaction
    inner join infor.credit_card using (credit_card_id)
    inner join infor.ext_user using (user_id)
    where infor.credit_card_transaction.transaction_date > concat('01-JUL-', (fiscYear - 1))
    and infor.credit_card_transaction.transaction_date < concat('30-JUNE-', fiscYear) 
    and NVL(SUBSTR(infor.ext_user.email_address, 0, INSTR(infor.ext_user.email_address, '@')-1), infor.ext_user.email_address) = userN;
    
    begin
      open search1
      fetch search1 into total;
      
      if search1%notfound then
        total := 0;
        end if;
        
        close search1;
        
        return total;
        end;

Answer 1

• You need to refer to the tables by their just table name or an alias outside the FROM clause and not by schema_name.table_name.
• Do not rely on implicit conversion of strings to dates; explicitly convert them using TO_DATE.
• If you are using a cursor then a INTO clause is not syntactically valid.
• However, you do not need a cursor.

Fixing all that gives you:

create or replace function sumyUserSpending (
  userN    in ext_user.email_address%TYPE,
  fiscYear in number
) RETURN number
IS
  total number;
BEGIN  
  select COALESCE(sum(cct.due_cc_co_amount), 0)
  into total
  from infor.credit_card_transaction cct
       inner join infor.credit_card cc using (credit_card_id)
       inner join infor.ext_user eu    using (user_id)
  where cct.transaction_date >= TO_DATE((fiscyear - 1) || '07-01', 'RR-MM-DD')
  and   cct.transaction_date <  TO_DATE(fiscyear || '07-01', 'RR-MM-DD')
  and   NVL(SUBSTR(eu.email_address, 0, INSTR(eu.email_address, '@')-1), eu.email_address)
          = userN;

  return total;
end;
/

db<>fiddle here