call oracle function with parameters

I started to learn pl/sql.I have problem with functions.I created a function like this

create or replace
 FUNCTION              GET_RANK_INFO(
 p_no               IN     VARCHAR2,
 p_date     IN     DATE,
 p_count   IN  NUMBER,
 p_cycle          OUT VARCHAR2,
 p_return_code         OUT INTEGER,
 p_return_desc         OUT VARCHAR2)
 RETURN SYS_REFCURSOR
AS
   .... --Some variable definitions
   ....

BEGIN

  ....
  ...

END;

I want to call this function like this

select GET_RANK_INFO('12345',to_date('10/03/2019','dd/mm/yyyy'),null) from dual; but it gives error like that the number of argument types invoked is incorrect

I converted my calling to pl/sql block structure but my code gives still same error:

DECLARE
 p_no            VARCHAR2(200);
 p_date           DATE;
 p_count          NUMBER;
 p_cycle          NUMBER;
 p_return_code    NUMBER;
 p_return_desc    VARCHAR2(200);
 v_Return SYS_REFCURSOR;
BEGIN
 p_no   := '23';
 p_date  := to_date('10/03/2019','dd/mm/yyyy');
 p_count:= NULL;

 v_Return := GET_RISK_RANK_FN(
   p_no=> p_no,
   p_date  => p_date  ,
   p_count => p_count,
   p_cycle => p_cycle,
   p_return_code => p_return_code,
   p_return_desc => p_return_desc
 );
DBMS_OUTPUT.PUT_LINE('p_cycle= ' || p_cycle);

  :p_cycle := p_cycle ;
DBMS_OUTPUT.PUT_LINE('p_return_code = ' || p_return_code );

  :p_return_code := p_return_code ;
DBMS_OUTPUT.PUT_LINE('p_return_desc = ' || p_return_desc );

  :p_return_desc := p_return_desc ;


  :v_Return := v_Return; --<-- Cursor
 END;

Answer

Such a function is doubtful; it would be OK if it returned one value, but – returning it along with several OUT parameters smells very much like a procedure you should be using instead. So, here’s an example of how to do it:

SQL> create or replace procedure p_test
  2    (par_deptno in  number,
  3     par_count  out number,
  4     par_name   out varchar2,
  5     par_rc     out sys_refcursor
  6    )
  7  is
  8  begin
  9    select count(*), max(dname)
 10      into par_count, par_name
 11      from dept
 12      where deptno = par_deptno;
 13
 14    open par_rc for select * from dept;
 15  end;
 16  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> declare
  2    l_deptno dept.deptno%type := 10;
  3    l_count  number;
  4    l_name   dept.dname%type;
  5    --
  6    l_rc     sys_refcursor;
  7    r_deptno dept.deptno%type;
  8    r_dname  dept.dname%type;
  9    r_loc    dept.loc%type;
 10  begin
 11    p_test (l_deptno, l_count, l_name, l_rc);
 12
 13    dbms_output.put_line('count = ' || l_count);
 14    dbms_output.put_line('name  = ' || l_name);
 15
 16    loop
 17      fetch l_rc into r_deptno, r_dname, r_loc;
 18      exit when l_rc%notfound;
 19      dbms_output.put_line(r_deptno ||' - '|| r_dname ||' - '|| r_loc);
 20    end loop;
 21  end;
 22  /
count = 1
name  = ACCOUNTING
10 - ACCOUNTING - NEW YORK
20 - RESEARCH - DALLAS
30 - SALES - CHICAGO
40 - OPERATIONS - BOSTON

PL/SQL procedure successfully completed.

SQL>

---

In case you insist on a function, oh well, here you are; as you can see, it is pretty much similar to previous example:

SQL> create or replace function f_test
  2    (par_deptno in  number,
  3     par_count  out number,
  4     par_name   out varchar2
  5    )
  6  return sys_refcursor
  7  is
  8    l_rc sys_refcursor;
  9  begin
 10    select count(*), max(dname)
 11      into par_count, par_name
 12      from dept
 13      where deptno = par_deptno;
 14
 15    open l_rc for select * from dept;
 16    return l_rc;
 17  end;
 18  /

Function created.

Testing:

SQL> set serveroutput on
SQL> declare
  2    l_deptno dept.deptno%type := 10;
  3    l_count  number;
  4    l_name   dept.dname%type;
  5    --
  6    l_rc     sys_refcursor;
  7    r_deptno dept.deptno%type;
  8    r_dname  dept.dname%type;
  9    r_loc    dept.loc%type;
 10  begin
 11    l_rc := f_test (l_deptno, l_count, l_name);
 12
 13    dbms_output.put_line('count = ' || l_count);
 14    dbms_output.put_line('name  = ' || l_name);
 15
 16    loop
 17      fetch l_rc into r_deptno, r_dname, r_loc;
 18      exit when l_rc%notfound;
 19      dbms_output.put_line(r_deptno ||' - '|| r_dname ||' - '|| r_loc);
 20    end loop;
 21  end;
 22  /
count = 1
name  = ACCOUNTING
10 - ACCOUNTING - NEW YORK
20 - RESEARCH - DALLAS
30 - SALES - CHICAGO
40 - OPERATIONS - BOSTON

PL/SQL procedure successfully completed.

SQL>