- 3,715,756 Users
- 2,242,858 Discussions
- 7,845,559 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 467 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 417 SQLcl
- 42 SQL Developer Data Modeler
- 184.9K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 9 DevOps
- 3K QA/Testing
- 250 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
Issue with function name quoting when mapping dbfunction to function in package

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.
Best Answer
-
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.
Answers
-
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.