Problem:
A db user came to me that, something strange is happening, he could not manage to execute a PL/SQL block.
He is getting PLS-00302 error while executing an anonymous PLSQL block.
In PLSQL block, there is a function QUERYFUNC which is already created in the same schema.
Thus giving right with grant option is nonsense.
The weird thing does not happened when I executed in SYSTEM schema.
This is what happens:
DUMMY@MYDB:node1> select DUMMY.QUERYFUNC('345354') from dual;
DUMMY.QUERYFUNC('345354')
----------------------------------------------------------------------------
X
1 row selected.
DUMMY@MYDB:node1> declare
2
3 RetVal VARCHAR2(200);
4 MYVAR VARCHAR2(200);
5
6 BEGIN
7 MYVAR := '12345';
8
9 RetVal :=
DUMMY.QUERYFUNC ( MYVAR );10
11 --DBMS_OUTPUT.Put_Line('RetVal = ' || DUMMY.QUERYFUNC ( MYVAR ));
12
13 END;
14
15
16
17 /
RetVal := DUMMY.QUERYFUNC ( MYVAR );
*
ERROR at line 9:
ORA-06550: line 9, column 15:
PLS-00302: component 'QUERYFUNC' must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored
Cause:Below code worked, in which DUMMY schema is not specified.
DUMMY@MYDB:node1> declare
2
3 RetVal VARCHAR2(200);
4 MYVAR VARCHAR2(200);
5
6 BEGIN
7 MYVAR := '12345';
8
9 RetVal :=
QUERYFUNC ( MYVAR );10
11 --DBMS_OUTPUT.Put_Line('RetVal = ' || DUMMY.QUERYFUNC ( MYVAR ));
12
13 END;
14
15 /
PL/SQL procedure successfully completed.
First of all I guessed it was because we have same function somewhere else, or there is synonym object which overrides the QUERYFUNC function.
But after querying dba_objects I could not find any.
At last, the goal comes from other side...
SolutionThere was a table named DUMMY in the schema named DUMMY.
Which assumes DUMMY.
QUERYFUNC is a table column or something I guess.
Thank you for the solution, Mustafa Saburlu. "Afiyet olsun" for Turkish tea :)
