Revisting ORA-06576: not a valid function or procedure name for simple function call via various dat-CodePudding

Revisting ORA-06576: not a valid function or procedure name for simple function call via dbeaver database-tool

I have seen many posts, but they don't cover the situation I am experiencing.

I am trying to do a simple function call to Oracle SQL via dbeaver:

According to the right-click call function obtained via dbeaver the function syntax it is:

CALL OUR_DB.GET_YEAR_FROM_DATE(:INPUT_DATE);

To be more concrete:

CALL OUR_DB.GET_YEAR_FROM_DATE('01/02/2024');

However, I get the following error: ORA-06576: not a valid function or procedure name

The function does seem to compile correctly.

I also tried to make my own function (which may have syntax errors), so that may be a follow-up question.

However, the above function is pre-existing on our database and should work correctly. Assuming that is true, how does one call it from dbeaver?

I am using: dbeaver 21.3

and also the ORACLE from SELECT * FROM v$version is: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

For what it's worth, I used Oracle SQL Developer Version 4.1.4.21 There is a very similar error, possibly a more detailed error message:

CALL OUR_DB.GET_YEAR_FROM_DATE('01/02/2024');

Error report -
SQL Error: ORA-06576: not a valid function or procedure name
06576. 00000 -  "not a valid function or procedure name"
*Cause:    Could not find a function (if an INTO clause was present) or
           a procedure (if the statement did not have an INTO clause) to
           call.
*Action:   Change the statement to invoke a function or procedure

This would seem to indicate that the problem is not really due to the tool, but the underlying database.

If the syntax is thought to work, could it just be a permissions issue?

CodePudding user response：

There is a substantial difference between the function and procedure.

Both get parameters, but only function returns a value.

So basically a function can be called in a select statement or in a PL/SQL block in an assign statement. See example below.

Only a procedure can be simple called as you do it.

Some elementar information abour functions and procedures and their distinctions:

what-is-the-difference-between-function-and-procedure-in-pl-sql

functions-vs-procedures-in-oracle

Examples

create function hello(par varchar2) return varchar2  as
begin
   return ('world ' || par);
end;
/

-- wrong usage of function
CALL  hello('hello')
SQL Error [6576] [65000]: ORA-06576: not a valid function or procedure name

-- legal use of function in SELECT
select  hello('hello') from dual;
world hello

-- or in PL/SQL block

-- use Ctrl Shift O  for SET serveroutput ON;
DECLARE
  v_result varchar(100);
BEGIN
     v_result := hello('helo');
     dbms_output.put_line(v_result);
END;