Best way to write an Oracle Procedure using Spring Data Jpa

I don't think you will find it easy to make a procedure that is portable between PostgreSQL and Oracle. The procedure languages are similar, but there are some basic differences.

https://www.postgresql.org/docs/current/plpgsql-porting.html documents some of these differences. The biggest blockers to writing portable procedures in my opinion would be:

• Oracle uses packages, but PostgreSQL doesn't. You can organize procedures in PostgreSQL using schemas.

• A PostgreSQL procedure body must be delimited with $$.

• Some data type names and built-in functions have different names in the two systems.

See the manual page I linked to for more compatibility issues, and several examples of procedures translated from Oracle to PostgreSQL.

In general, it's hard to write portable SQL between different brands, nor is it a good goal. If you were able to write generic SQL that works on multiple brands, you would have to limit your usage to only the features all brands had in common, which is very little. You would have to refrain from using any brand-specific syntax or functions, which means you wouldn't be able to take advantage of any of the brands fully.

I worked on a database framework for PHP years ago, in which I supported Oracle, DB2, PostgreSQL, MySQL, SQLite, and Microsoft. There was no way to write SQL to be portable across all those brands, or even any two of the brands. The code was designed to use the Adapter Pattern, and any brand-specific features were implemented in the respective adapter class.

Thanks Bill. I have gone through your link and it will definitely help me in working with PostgreSQL. But here the scenario is to model and mimic the functionality of oracle procedure in Spring Data JPA do that it will not be hardly coupled with DB. I just connect to respective DB and process data using JPA entity models. So is it a good approach to write SQL queries in JPA/Hibernate Native queries and execute or any other way to model the procedure functionality in JPA framework.

Well, you want to replace an Oracle stored procedure with Spring Boot plus JPA code that works on any database. You can simply do it by following these steps to convert Oracle procedure into Spring Boot JPA logic:

Step 1: Understand what the Procedure does

Step 2: Decide where the Logic belongs in your spring boot layers

Step 3: Translate Oracle SQL Features to JPA or JPQL

Step 4: Use Transaction Correctly

Step 5: Write Repository Queries

Step 6: Handle DB Differences (Oracle – Postgres)

Step 7: Testing Across Databases

Step 8: Keep it Maintainable

Hope this will help you.

Thanks Mamta. Can you please give reference of Step 3 & 5

Steps 3 & 5 is where you write this code :) Or try to find code that does it for you / ChatGPT / hire expensive consultants. There's no automatic translator between them

I am not asking for code. I asked for learning reference which I can follow.

You could consider the analogy of being asked to translate a popular book of poetry from one spoken language to another. How would you do this?

You'd have to learn the language the book is written in, well enough to understand the use of language and imagery. You'd have to understand why readers who speak that language find it beautiful as poetry.

Then you'd have to learn the language you need to translate the book into, well enough to make it beautiful to readers who speak that language.

Then you would be ready to translate each poem in the book. Each poem would be a new challenge, because they are all different.

Is there a learning reference you can follow to do the translation? I don't think so. You need to spend time learning both languages deeply, then with fluency you will be able to write poetry in the second language that conveys the same meaning as the first.

This will take a lot of time, for both study and practice.