Revisting Ora-06576: Not a Valid Function or Procedure Name for Simple Function Call via Various Database Tools
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...
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?
1 Answer
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;
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
