Oracle PLSQL to PostgreSQL PLPGSQL Array Length Migration
Migrating Oracle PL/SQL code to PostgreSQL PL/pgSQL requires a deeper understanding of the behavior in both Oracle and PostgreSQL databases. It is not only the syntax but also the behavior that is very important. Else, upon porting the PL/SQL code to PL/pgSQL, we may encounter some strange application behaviors that are difficult to trace. We would like to discuss one of such common mistakes while porting arrays indexed by integers from Oracle PL/SQL to PostgreSQL PL/pgSQL.
Let us consider a very simple use case in Oracle, as follows. In this example, we are declaring a collection or an associative array, that is indexed by an integer.
In the above function, we defined the test_func function with an array, which has 2 parameter variables as `a`, `b`. By using these 2 variable values as an index to the array, we are saving some random values and returning the array length to the function called in the environment.
As expected, we got the array length as 2. Now, let us use some random parameter values and compare the behavior in Oracle and PostgreSQL. These random parameter values will be used as index of the array variable.
