Oracle: wrong number or types of arguments in call

Question

create procedure employee_get
(
   c_Emp  out SYS_REFCURSOR
)
is
begin
   open c_Emp for
   select a.last_name, b.department_id, b.department_name from employee a inner join department b on a.department_id = b.department_id
   order by a.last_name asc ;
end employee_get;
-- run
var c_out refcursor;
exec employee_get(:c_out);
print :c_out;

Everything was fine until I made the Run command. I'm new member, please help me. Thanks and respect!

Please clarify your problem, format your code, and add some explanation about what you trying to achieve. — Crocsx, Aug 30 '19 at 02:27
Your procedure takes one parameter, but your `exec` statement doesn't supply that parameter. — Bob Jarvis - Reinstate Monica, Aug 30 '19 at 03:38
@BobJarvis, I tried following your instructions, but it still didn't work — Đoàn Hiển, Aug 30 '19 at 04:26
`drop procedure employee_get` would drop the procedure if the syntax was correct, but it is missing its `;` (unless you changed `sqlterminator`), and the `create procedure` statement above is missing its trailing `/`, so running the code above in SQL\*Plus will just prompt for the next line. — William Robertson, Aug 31 '19 at 07:41
@WilliamRobertson, i don't run the query drop procedure employee_get. It has no effect. Even if I delete it, there is still an error — Đoàn Hiển, Sep 03 '19 at 03:38

Popeye · Answer 1 · 2019-08-30T06:23:08.777

1

You need following code to execute the procedure:

create procedure employee_get
(
   c_Emp  out SYS_REFCURSOR
)
is
begin
   open c_Emp for
   select a.last_name, b.department_id, b.department_name from employee a inner join department b on a.department_id = b.department_id
   order by a.last_name asc ;
end employee_get;
/

-- EXECUTE THE PROCEDURE
DECLARE
C_OUT SYS_REFCURSOR;
BEGIN
-- run
employee_get(C_OUT);
dbms_sql.return_result(C_OUT); --UPDATED
END;
/

Cheers!!

edited Aug 30 '19 at 06:23

answered Aug 30 '19 at 04:52

Popeye

34,354
4
8
30

That will miserably fail because of the EXEC call within the PL/SQL block. – Littlefoot Aug 30 '19 at 05:51
Correct.. Updated it – Popeye Aug 30 '19 at 05:56
@Tejash, My desired result is to display data. But it does not display – Đoàn Hiển Aug 30 '19 at 06:19
Then you need to use `dbms_sql.return_result(C_OUT);` as updated in the answer – Popeye Aug 30 '19 at 06:23
@Tejash, sql navigator show mess: "component 'RETURN_RESULT' must be declared". I don't know why the error is so – Đoàn Hiển Aug 30 '19 at 06:43
Because you don't have privileges on the package, I presume. I've posted an alternative approach, see the answer (it can't fit into the comment and can't be formatted). And, to be *politically correct*, upvoded Tejash's answer. – Littlefoot Aug 30 '19 at 07:11
1

`dbms_sql.return_result` requires Oracle 12.1. It's possible @ĐoànHiển is stuck with an old version, in which case there are some more options here: https://stackoverflow.com/a/40360471/230471 – William Robertson Aug 30 '19 at 07:17
Missing grants would give `PLS-00201: identifier 'DBMS_SQL' must be declared`. However, `dbms_sql` is granted to `public` by default. `PLS-00302: component 'RETURN_RESULT' must be declared` indicates that the package exists but the procedure doesn't, which would be the case prior to Oracle 12.1. – William Robertson Aug 31 '19 at 08:06

score 0 · Answer 2 · answered Aug 30 '19 at 07:10

0

The procedure which returns refcursor:

SQL> create or replace procedure employee_get (c_emp out sys_refcursor) is
  2  begin
  3    open c_emp for select empno, ename from emp;
  4  end;
  5  /

Procedure created.

Execution:

SQL> declare
  2    c_out sys_refcursor;
  3  begin
  4    employee_get(c_out);
  5    dmbs_sql.return_result(c_out);
  6  end;
  7  /
  dmbs_sql.return_result(c_out);
  *
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00201: identifier 'DMBS_SQL.RETURN_RESULT' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

See with your DBA whether you can get execute privileges on DBMS_SQL package.

If not:

SQL> var c_out refcursor;
SQL> exec employee_get(:c_out);

PL/SQL procedure successfully completed.

SQL> print :c_out

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL>

answered Aug 30 '19 at 07:10

Littlefoot

78,293
10
26
46

I did like you but failed – Đoàn Hiển Aug 30 '19 at 07:42
Well, it *works* (as you can see). Please, edit your initial message and post what you did and how Oracle responded. I don't know why it failed for you. – Littlefoot Aug 30 '19 at 07:44
* line 2: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EMPLOYEE_GET' ORA-06550: line 1, column 7: PL/SQL: Statement ignored – Đoàn Hiển Aug 30 '19 at 07:53
The procedure accepts only one parameter, and its type is refcursor. Is that correct? If possible, post your SQL*Plus session (just like I did) which **exactly** shows every step you made, with all messages returned by Oracle. You posted some commands, but I can't tell whether it is really what you ran or not. – Littlefoot Aug 30 '19 at 07:57
okie, thanks you. I'll try again. hope done. I am used to using SQL Server, switching to Oracle is more difficult than I thought. :)) – Đoàn Hiển Aug 30 '19 at 08:12
True; although similar, they seem to be quite different after all. Good luck! – Littlefoot Aug 30 '19 at 08:15

Oracle: wrong number or types of arguments in call

2 Answers2