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?

1 answer

  • answered 2018-01-11 21:18 Littlefoot

    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.