I met a strange problem when using oracle table function.
Two environments: local and remote, the code is the same only some config varies and they share the same database. The oracle table function can work perfectly in my local environment, but an error occurs on remote even when I just tried to use a very simple table function.
create type V as table of varchar2(4000);
/
CREATE FUNCTION test2 RETURN v IS
BEGIN
RETURN v('a', 'b');
END test2;
/
I tested by using below in plsql and the invalid identifier error occurs here.
select * from table(Test2());
ORA-00904: : invalid identifier
It's also strange that the identifier name should be part of the error message, but it is not there. The query alone can work well in plsql developer. And the Test2 function can work well in local and remote if not used in table function.
The code in local and remote is the same. Only weblogic config, jdbc driver version etc. may be different. I can't access to the remote environment except the db shared by both. I tried to search the problem online but didn't find an answer. So I hope some members here can shed some light on this problem. Any help is much appreciated.
The db and weblogic version:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Application Server - Weblogic 10.3.3.0
ps: I found some one posted a simiar question on community.oracle.com, but it hasn't been solved yet. https://community.oracle.com/thread/2503982
Well, I was wrong. It turned out to be a question relating to privilege.
After running sql like below, problem solved.
GRANT EXECUTE ON "DB_USERNAME"."TEST" TO "DB_USERNAME_USER";