Issue with function name quoting when mapping dbfunction to function in package

Andrew Moskevitz

I am submitting this bug here as suggested in this blog post
https://medium.com/@alex.keh/oracle-ef-core-3-1-beta-2-now-available-254d306ff875
We are trying to map a dbfunction using the code below

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
            modelBuilder.HasDbFunction(typeof(DatabaseFunctions).GetMethod(nameof(DatabaseFunctions.GetNameId))).HasSchema("DBM").HasName("DBM_PKG.GETNAMEID");
}

this generates the error

ORA-00904: "DBM"."DBM_PKG.GETNAMEID": invalid identifier

as you can see it puts the quotes around the package name plus the function name, not each individually.
I have tried it several different ways to attempt to get it to work to no success

ORA-00904: "DBM.DBM_PKG"."GETNAMEID": invalid identifier

I have tried including the middle quotes in the name ,"DBM_PKG\".\"GETNAMEID but that results in

ORA-03001: unimplemented feature 

an examination of the sql shows this attempt is generating

"DBM"."DBM_PKG"".""GETNAMEID"() 

The EF6 provider saw the . in the function name and correctly generated the quoting.

I would hope this can be fixed, either by detecting the package.functionname in the name, or by providing an additional .HasPackageName method on the DbFunctionBuilder, or some other method.

As a temporary workaround I created a wrapper function that is not in a package DBM.GETNAMEID, but doing that defeats the point of packages.

if there is a way to do this already that does not involve intercepting the generated SQL, then it is not obvious how to do so.

Alex Keh-Oracle

This is a known limitation. My team reviewed the issue last year. Our provider cannot make changes to the EF Core function mapping implementation. What we've documented:

Database Scalar Function Mapping

* Database Scalar Function Mapping does not provide a native way to identify

functions residing within PL/SQL packages. To workaround this limitation, map

the package and function to an Oracle synonym, then map the synonym to the EF

Core function.

Alex Keh-Oracle

This is a known limitation. My team reviewed the issue last year. Our provider cannot make changes to the EF Core function mapping implementation. What we've documented:

Database Scalar Function Mapping

* Database Scalar Function Mapping does not provide a native way to identify

functions residing within PL/SQL packages. To workaround this limitation, map

the package and function to an Oracle synonym, then map the synonym to the EF

Core function.

Calin M

Hi Alex,

It's not possible to "map the package and function to an Oracle synonym", Oracle synonyms can be created only for top level objects such as packages and tables, not for functions inside packages. The solution remains what Andrew said: create a wrapper function that is not in a package, but doing that defeats the point of packages.

Not using quotes would work great as well.