Function name out of scope in SQL
I'm trying to create an abstraction layer to mimic DB2 functions in Oracle 11g. I created the following function under the ABC schema:
create or replace function ABC.timestamp(p_date in date) return date is begin return p_date; end;
When I try to use it in a SQL statement, I get an error:
select timestamp(current_date) from dual; ORS-06553: PLS-222: no function with name 'TIMESTAMP' exists in this scope
If I call out the schema explicitly it works:
select ABC.timestamp(current_date) from dual;
I also have a logon trigger that explicitly sets the session's current_schema to ABC. The only complication I can see is that TIMESTAMP is a keyword in Oracle, but it allows me to create this function with that name.
Any way to get this to work?
I don't think that you can workaround it "as is". But, if you use double quotes around function name (which is yet another stupid thing some people do - and, look at me, I'll do it now), it'll work without owner's name:
SQL> create or replace function "timestamp" (p_date in date) return date is 2 begin 3 return p_date; 4 end; 5 / Function created. SQL> select "timestamp"(current_Date) from dual; "TIMESTA -------- 11.01.18 SQL>
However, it means that you'll ALWAYS have to use double quotes when referencing that function.
If I were you, I'd change function name. As you can see, the fact that you can do something doesn't mean that you should do it.