wrong number or types of arguments calling a procedure from Oracle SQL Developer

Question

logged as the user CC_LOPES I have this procedure:

create or replace PACKAGE BODY            "P_MSG_HOTEL" AS

  function parse_msg(p_id in number, p_msg in varchar2) return number is
  ...
end;

That I try to execute from Oracle SQL Developer with

 EXECUTE P_MSG_HOTEL.parse_msg(596210657, '@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60');

I got this error:

Error que empieza en la línea: 1 del comando :
BEGIN P_MSG_HOTEL.parse_msg(596210657, '@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60'); END;
Informe de error -
ORA-06550: línea 1, columna 126:
PLS-00306: wrong number or types of arguments in call to 'PARSE_MSG'
ORA-06550: línea 1, columna 126:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

Am really excited to know incase you really got the error you mentioned in your question.

Ideally you must had got something like:

     *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'PARSE_MSG' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

But you posted :

Error que empieza en la línea: 1 del comando :
BEGIN P_MSG_HOTEL.parse_msg(596210657, '@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60'); END;
Informe de error -
ORA-06550: línea 1, columna 126:
PLS-00306: wrong number or types of arguments in call to 'PARSE_MSG'
ORA-06550: línea 1, columna 126:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

This looks quiet misleading.

I did the below demo to show what i meant.

CREATE OR REPLACE PACKAGE P_MSG_HOTEL
AS
   FUNCTION parse_msg (p_id IN NUMBER, p_msg IN VARCHAR2)
      RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY P_MSG_HOTEL
AS
   FUNCTION parse_msg (p_id IN NUMBER, p_msg IN VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      RETURN 1;
   END;
END;

On execution the way you showed it gives the error which say :

EXECUTE P_MSG_HOTEL.parse_msg(596210657,
'@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60');

Error

    *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'PARSE_MSG' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This clearly means Oracle is not able to identify your function while execution. However when i do like:

SQL> 
select  P_MSG_HOTEL.parse_msg(596210657, '@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60') as col
 from dualSQL>   2  
  3  /

       COL
----------
         1

I get the output.

Or if i use an Anonymous block i get the result.

SQL> DECLARE
   x   NUMBER;
BEGIN
   x :=
      P_MSG_HOTEL.parse_msg (
         596210657,
         '@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60');
  2     DBMS_OUTPUT.put_line (x);
END;
/  
1

PL/SQL procedure successfully completed.

SQL> 

So, in short, you cannot use the function the way you are executing.

Answer 2

You cannot use execute command to execute a function, the function return some value that needs to be captured somewhere, Use anonymous block:

declare 
  f_return number;
begin
  f_return := P_MSG_HOTEL.parse_msg(596210657, '@S,358639058787154;E,10;D,05102017145210,05102017145210;G,4046393,51206983,258,8;M,4709;S,0;IO,1,0,0;DI,79DEAD60');
end;
/

Answer 3

Don't execute the function directly . either do it inside DBMS_OUTPUT

SET SERVEROUTPUT ON

EXEC DBMS_OUTPUT.PUT_LINE(P_MSG_HOTEL.parse_msg(arg1,arg2));

Or run a query with function in select to get the output.

select P_MSG_HOTEL.parse_msg(arg1,arg2) FROM DUAL;