Hi,
I have a stored function in my database, which I published as follows:
create or replace function c (p varchar2, d varchar2) return varchar2 as language java name 'xxx.funcName (java.lang.String, java.lang.String) return java.lang.String';
I am able to call it in SQL commands as follows:
select c@psrdb ('=''EJYZ''', '90') from dual@psrdb;
Now I am creating an APEX page where the user inputs the value d. From SQL commands, I did:
select c@psrdb('=''EJYZ''', :P22_D) from dual@psrdb;
and when I input '90' for P22_D, the call is successful. However, when I try it in the APEX page (I created a text field item and an interactive report with the previous SQL query), I get this error:
report error:
ORA-00904: "A2"."C@PSRDB('=''EJYZ''',:P22_D)": invalid identifier
I tried to debug, and when I changed the SQL query to:
select c@psrdb ('=''EJYZ''', '90') from dual@psrdb where :P22_D = '10';
And I test it on my page, the function is called when I input 10 for P22_D, and when I input something else the function is not called.
It seems that SQL grammar check does not recognize :P22_D when it is an input to a stored function? Please let me know if you have any ideas how to solve this.
Thanks in advance!