Postgres Plus Advanced Server (known as EDB Postgres Advanced Server after the v. 9.5 release) provides Oracle compatibility and allows you to write packages and procedures, and lets you call them as all Oracle users are accustomed to. However, there are a few minor pedantic changes you’ll need to get accustomed to in order to write packages and procedures correctly in Advanced Server. When it comes to using Custom Types, you’ll need to be mindful of a few things. We’ll go through a simple example so you can get on your way.
Suppose you want to bulk-insert several thousand rows into a database, after combing it through with some business logic. If you write a procedure that takes in as arguments all the data that fills in the columns of one row, it might look something like this:
However, if you call this procedure thousands of times via your Java app, you might find that the roundtrip latency will slow you down. Why not send all the data over to the database at once, and have a procedure iterate through the data set and insert it into the database? In order to do this, you’ll need to use Custom Types, and the first two steps towards that are:
After that, you’re ready to create your procedure that will take in (as an array) all the data to be inserted, and use your original procedure (empInsert) to insert the data after applying the business logic:
Once these components are in place, you can write up a simple Java app that looks something like this: