[Feature] Support Stored Procedures and Functions for other providers

[pgkdev]

Tested EF Core Power Tools to reverse engineer an Oracle Database, I was able to create models for tables and views but not for Stored Procedures included in Oracle Packages.

Steps to reproduce

Create Oracle Database with a Package and a Stored procedure.
Using EF Core Power Tools, try to reverse engineer to create model for Stored Procedure inputs and outputs.
(Maybe it only supports SQL Server SPs for now, like shown in project pages, but Oracle support for SPs will be implemented soon? Need it, thanks :)

Further technical details

EF Core Power Tools version: 5.2.406

Database engine: Oracle XE 184 Win64

Visual Studio version: (e.g. Visual Studio 2019 16.8.3)

[ErikEJ]

Not something I plan to implement, as I no nothing about Oracle, and had no test server.

But a PR would be welcome, if you want to do that, I can provide more implementation details.

Code for Postgres stored proc discovery:

https://github.com/vb-consulting/NpgsqlRest/blob/master/NpgsqlRest/RoutineSourceQuery.cs

[pgkdev]

Unfortunately I don't have the time and the knowledge to implement a complicated piece of software like this.

I was liking so much this tool, it seem perfect but then notice this missing feature for Oracle, and I have lots of SPs :s
Do you have some alternative solution for this?

Thanks and great project "EF Core Power Tools".

[ErikEJ]

It is actually not that complicated. But maybe someone else can step up @ProH4Ck ?

[ProH4Ck]

Ok... I'll check in the next few days if this is possible but I remember that something is not yet implemented in Oracle EF Core provider (UDTs for example)...
I need to check if basic stored procedures work and if they'll work, I'll add support to EF Core Power Tools.

[ErikEJ]

Fantastic! Feel free to ask me for implementation suggestions/advice.

[Giorgi]

In Oracle, it is common to group stored procedures and functions in packages. In the project that I had to deal with Oracle, I had a separate static class for each Oracle package that contained an extension method on DbContext for every sproc/function found in that package.

I can help with adding support for Oracle too.

[ErikEJ]

I would prefer an implementation that resembles the SQL Server implementation as closely as possible

[Giorgi]

How do you deal with sprocs that have the same name but have different schema?

[ErikEJ]

Specify the schema name when calling them...

But I think schema may mean something completely different in Oracle

[Giorgi]

Yes, the schema is a different thing in Oracle. In Oracle, you can have sprocs with the same name but in different packages. Specifying the package name every time you call a sproc means having to type it manually, but we can have it generated by grouping the sprocs in separate classes instead of having one giant class holding all sprocs/functions.

[ErikEJ]

Ah, I think I get your question now. I name the method SchemaMethodName if there are duplicates.

Have a look at the current implementation.

[Giorgi]

I see. Well, I would prefer to have

var procedures = new NorthwindContextProcedures(db);
procedures.SchemaName.SprocName(param1, "param2");

But I guess it's too late to change? It would align with how you execute them in the database and would help clean up intellisense too.

Also, what do you think about returning a named tuple when the sproc has multiple output parameters instead of using OutputParameter?

[ErikEJ]

It is actually SprocnameSchemaname.

Tuples would be a breaking change as well, and changing the API is also not what this feature is about.

[pgkdev]

In Oracle, it is common to group stored procedures and functions in packages. In the project that I had to deal with Oracle, I had a separate static class for each Oracle package that contained an extension method on DbContext for every sproc/function found in that package.

I can help with adding support for Oracle too.

I can confirm this, we have Procedures and Functions in packages.

[ErikEJ]

@pgkdev why was this closed?

[ErikEJ]

I think you must try to implement something, or explain with examples, my Oracle knowledge is zero.

[pgkdev]

@pgkdev why was this closed?

It was a click mistake, sorry :s

[pgkdev]

I think you must try to implement something, or explain with examples, my Oracle knowledge is zero.

It's +- like me, I'm using what other IT team provided for Oracle DB schema. But noticed that, stored procedures inside packages.

[pgkdev]

@ProH4Ck any news on the implementation?
Need that :)

[kevinvenclovas]

Is someone working on this issue? I need this for my projekt now otherway i need to use a other framework/tool

[ErikEJ]

No, this issue needs help from experts in the various RDBMS systems.

[kevinvenclovas]

Oke thanks. Is there a workaround?
Is there a option to execute a raw sql string to execute my procedur?

[ErikEJ]

Sure, you can just hand code what the stored procedure mapping does (run against a SQL Server db, or see the code in the GitHub repo ( in the ScaffoldingTester project)

[ErikEJ]

Closing and moving to Backlog milestone

[andrewdci03]

@ErikEJ you mentioned not implementing because of no knowledge of Oracle, is this the same for MySQL? My team is trying to reverse engineer the stored procedures in our MySQL Database, but it seems this doesn't support that yet.

[ErikEJ]

@andrewdci03 Correct, only SQL Server / Azure SQL stored procedures are supported, but I am willing to help anyone with a PR