On
Database Patch Set Update : 11.2.0.4.181016 (27734982)
I encountered this puzzling issue:
In dynamic SQL, I use two functions, but one triggers a ORA-00904: "function_name_1": invalid identifier
unless I prefix it with the package name.
Code is (shortened):
CREATE PACKAGE pac is
procedure test1(
par1 IN INTEGER,
par_out OUT sys_refcursor);
function fun1(p1 IN tab1.FooID%type, p2 tab1.FooID%type) return INTEGER ;
function funSomethingAccess(
p_1 IN tab1.FooID%type, -- these are all actually INTEGER
p_2 IN tab1.FooID%type,
p_3 IN tab2.BarID%type,
p_4 IN tab2.BarID%type,
p_5 integer,
p_6 IN tab3.BazID%type
) return INTEGER ;
end pac;
CREATE PACKAGE BODY pac is
procedure test1(
par1 IN INTEGER,
par_out OUT sys_refcursor)
is
sqlQuery VARCHAR2(200);
begin
sqlQuery := 'select * from tab1 where col1=:par1 and funSomethingAccess(col1, col2, 3, 4, 5, 6) = 1 and fun1(col1, col2) =1';
OPEN par_out FOR sqlQuery USING par1;
end;
end pac;
Using those functions in regular (static) SQL in procedures in same package works fine (without package name prefix).
But the above dynamic SQL returns the error: ORA-00904: "funSomethingAccess": invalid identifier
If I write it like this:
sqlQuery := 'select * from tab1 where ... and pac.funSomethingAccess(1,2,3,4,5,6) = 1 and fun1(1,2) =1';
then it works.
Why?
Why is the package name needed for the first function call, while the second works without it?
Is this some rule I overlooked?
Seems similar to bug 18780530, but I use no pipelined functions.
Message was edited by: David Balažic
Fixed typos in example SQL