Using Regular Expressions To Convert Oracle PL/SQL Stored Procedures to Postgres PL/PGSQL
We all know that there will eventually come a time when a major decision has been made to change databases. When a lot of custom SQL code has been written for the old database, which needs to be converted to the new one — and this is true especially when stored procedures are used a lot — , automated programs and regular expressions can be a speedy and efficient way to get the job done.
In this article, I will go through some main SQL syntax differences between Oracle PL/SQL and Postgres PL/PGSQL and how to use VB.Net and regular expressions to convert from one SQL version to another. You’ll get an opportunity to learn about regular expressions and know some main syntax differences in the two database versions of SQL above.
Replace PROCEDURE keyword with FUNCTION
In stored procedures, it is sometimes necessary to replace the keyword PROCEDURE in Oracle PL/SQL to FUNCTION in PostgresQL. So for example, the syntax:
CREATE OR REPLACE PROCEDURE
in Oracle/PLSQL would become:
CREATE OR REPLACE FUNCTION
in Postgres PL/PGSQL.
Or in more complex stored procedure bodies :
DROP PROCEDURE BATCHNO1_PROC;
CREATE PROCEDURE BATCHNO1_PROC(
IN_c_BatchNo IN VARCHAR2,
IN_c_BatchCategory IN VARCHAR2,
IN_c_BeginDate IN VARCHAR2,
IN_c_EndDate IN VARCHAR2,
OUT_n_ErrCode OUT NUMBER,
OUT_v2_ErrMsg OUT VARCHAR2,
OUT_n_Cnt OUT NUMBER
) IS
…
in Oracle PL/SQL, would become in Postgres PL/PGSQL :
DROP PROCEDURE BATCHNO1_PROC;
CREATE FUNCTION BATCHNO1_PROC(
IN_c_BatchNo IN VARCHAR2,
IN_c_BatchCategory IN VARCHAR2,
IN_c_BeginDate IN VARCHAR2,
IN_c_EndDate IN VARCHAR2,
OUT_n_ErrCode OUT NUMBER,
OUT_v2_ErrMsg OUT VARCHAR2,
OUT_n_Cnt OUT NUMBER
)AS
$BODY$
…
In VB.NET coding, after reading and loading the input file containing the original PL/SQL stored procedure, the relevant code would be converted by reading line by line (where in the code below, fileRowIndex or fileRowIndex1 is the current line being read within the program), as below:
‘Detect if the string has the word PROCEDURE
Dim procedureStringPosition = strFileData(fileRowIndex).IndexOf(UCase(“PROCEDURE”)) + “PROCEDURE”.Length
Dim procedureOpenBracketPosition = strFileData(fileRowIndex).IndexOf(“(“)
‘Find the starting position of the first character of the procedure name and its length
procedureName = strFileData(fileRowIndex).Substring(procedureStringPosition + 1, strFileData(fileRowIndex).IndexOf(“(“, procedureStringPosition + 1) — procedureStringPosition — 1)
‘Prefix the comment expression ( — — )in front of the original line and insert the converted SQL below
Dim commentProcedureLine As String = “ — “ & strFileData(fileRowIndex)
strFileData(fileRowIndex) = commentProcedureLine & Chr(13) & strFileData(fileRowIndex).Replace(“PROCEDURE”, “FUNCTION”)
End If
Example output:
— CREATE OR REPLACE PROCEDURE SEARCH_AREA
CREATE OR REPLACE FUNCTION SEARCH_AREA
Procedure endings (END)
In Oracle PL/SQL, procedures are normally ended with the keyword END; and optionally the name of the procedure appended at the end e.g. END BATCHNO1_PROC;
In Postgres, only the END keyword is used. If it is the last line of the stored procedure, the SP is ended with the forward slash (/) in Oracle PL/SQL. However, the forward slash is not used in Postgres PGSQL and the following body of PL/PGSQL is appended at the end of Postgres stored procedures:
$BODY$
LANGUAGE plpgsql
With VB.NET, this is done through coding as follows:
‘Check if the current line contains END word
Dim regexMatchEndInProcedureBody As Match = Regex.Match(strFileData(fileRowIndex).ToString, “\s*\bEND\b\s*”)
If (regexMatchEndInProcedureBody.Success) Then
‘Check if it is END IF and END LOOP and not related to procedures so do not process
If Not (strFileData(fileRowIndex1).Contains(“ IF”) Or strFileData(fileRowIndex1).Contains(“ LOOP”)) Then
‘Check if line contains END; -or- END proc_name;
If (strFileData(fileRowIndex1).Contains(procedureName) And strFileData(fileRowIndex1).Contains(“;”) Or (Regex.Match(strFileData(fileRowIndex1).ToString, “END\s*;”).Success)) Then
‘Comment the current existing line containing END which is to be converted
‘(don’t remove the converted lines, just comment them out for reference later)
procedureBodyEndCommentLine = “ — “ & regexMatchEndInProcedureBody.Value & getProcedureNameInProcedureBody
If (strFileData(fileRowIndex1).Contains(procedureName) And strFileData(fileRowIndex1).Contains(“;”)) Then
‘Attach the Postgres specific body to the end of the stored procedure
strFileData(fileRowIndex1) = procedureBodyEndCommentLine & Chr(13) & “END;” & Chr(13) & “$BODY$” & Chr(13) & “LANGUAGE plpgsql”
End If
‘Check for forward slash which will be on the next line below
Dim regexMatchForwardSlash As Match = Regex.Match(strFileData(fileRowIndex1 + 1).ToString, “\s*/\s*”, RegexOptions.IgnoreCase)
If (regexMatchForwardSlash.Success) Then
If Not Regex.Match(strFileData(fileRowIndex1 + 1).ToString, “\s*\/\*\s*”, RegexOptions.IgnoreCase).Success Or Not Regex.Match(strFileData(fileRowIndex1 + 1).ToString, “\s*\*\/\s*”, RegexOptions.IgnoreCase).Success Then
‘Comment out the line to be converted, do not remove it
strFileData(fileRowIndex1) = “END;” & Chr(13) & “ — /” & Chr(13) & “$BODY$” & Chr(13) & “LANGUAGE plpgsql”
End If
End If
The regular expression \s*\/\*\s* detects the forward slash (/) with as many spaces before or after including no spaces. The forward slash needs to be preceeded and postceeded with the backslash in a regular expression.
Example output:
— END SEARCH_AREA;
END;
— /
$BODY$
LANGUAGE plpgsql
Replacing := with DEFAULT during stored procedure variable declarations in PostgresQL
During stored procedure variable declarations, the := expression in PL/SQL is replaced with the DEFAULT keyword in PL/PGSQL such as in the example below:
num2 IN NUMBER := 5
replaced with:
num2 IN NUMBER DEFAULT 5
With VB.NET, the coding is as follows:
‘change := syntax to DEFAULT using regular expression
Dim regexMatchDefaultBeforeIs As Match = Regex.Match(strFileData(fileRowIndex1).ToString, “\s*:=\s*”, RegexOptions.IgnoreCase)
If (regexMatchDefaultBeforeIs.Success) Then
‘Comment out the existing line to be converted, don’t remove it
Dim commentLineDefaultBeforeIs As String = “ — “ & strFileData(fileRowIndex1)
strFileData(fileRowIndex1) = commentLineDefaultBeforeIs & Chr(13) & strFileData(fileRowIndex1).Replace(“:=”, “DEFAULT”)
End If
The regular expression \s*:=\s* detects the := keyword with as many spaces before or after including no spaces.
Replacing NUMBER keyword with NUMERIC
The NUMERIC keyword in PL/PGSQL is a straightforward replacement for the NUMBER keyword in PL/SQL .
With VB.NET, this is a straightfoward
strFileData(fileRowIndex1).Replace(“NUMBER”, “NUMERIC”)
Removing the DEFAULT ‘ ’ expression
In Oracle, a procedure variable may be declared with an empty string as a default value such as:
STAFFID IN VARCHAR2 DEFAULT ‘ ’,
However, this is not recognised in PostgresQL and needs to be removed, so the phrase becomes:
STAFFID IN VARCHAR2,
In VB.NET, the following coding does this:
Dim regexMatchDefaultEmptyString As Match = Regex.Match(strFileData(fileRowIndex1).ToString, “\s*DEFAULT\s*’’\s*”, RegexOptions.IgnoreCase)
If (regexMatchDefaultEmptyString.Success) Then
Dim commentLineDefaultEmptyString As String = “ — “ & strFileData(fileRowIndex1)
strFileData(fileRowIndex1) = commentLineDefaultEmptyString & Chr(13) & strFileData(fileRowIndex1).Replace(regexMatchDefaultEmptyString.Value, “”)
End If
The regular expression \s*DEFAULT\s*’’\s* detects the DEFAULT ‘’ phrase with as many spaces before or after including no spaces.
Replacing the || expression in PL/SQL with CONCAT in PL/PGSQL
Another difference is the use of the || (OR function) in Oracle is replaced with the CONCAT keyword in Postgress. The change is as follows:
sqlStatement := ‘ SELECT ’;
sqlStatement := sqlStatement || ‘ Field1’;
sqlStatement := sqlStatement || ‘ Field2’;
sqlStatement := sqlStatement || ‘ FROM BatchNo1’;
will be changed to the Postgres PL/PGSQL version as shown below:
sqlStatement := ‘ SELECT ’;
sqlStatement := CONCAT(sqlStatement , ‘ Field1’);
sqlStatement := CONCAT(sqlStatement , ‘ Field2’);
sqlStatement := CONCAT(sqlStatement , ‘ FROM BatchNo1’);
Note —
(1) since we are reading the input file line by line, we can use a For … Next statement and the Readline method with a variable to store the current line.
(2) You can use the popular regex online tool at Rububular to test out your regular expressions easily.
(3) You can read more about VB.NET regular expressions usage here.
The above are some of the key syntax changes in SQL between Oracle PL/SQL and Postgres PL/PGSQL and of course, there are many more more. Using VB.NET (or other tools), I automated the task of detecting and changing the syntaxes by reading the stored procedure files and converting them to Postgres PL/PGSQL format and saving it as a new file. This is especially useful when you have hundreds of already existing Oracle PL/SQL files which you need to convert to Postgres PL/PGSQL. Of course, we will build on the program to detect more and more syntax migrations as we proceed.
The resulting converted PL/PGSQL file can then be tested by running it with a Postgres database query tool such as pgAdmin or HeidiSQL.
(Note on my earlier articles — For a rundown on fullstack Java and REST backend development and React frontend development, please check out : Creating a fullstack React/Material-UI DataGrid frontend data grid connected to a Java SpringBoot REST GET API backend with axios.
This article is further extended with my other article, Creating a fullstack Java app with AJAX HTML frontend using jsGrid datagrid and Apex Charts chart component calculating monthly averages from uploaded Excel data files on a Spring Boot REST backend running Apache Tomcat.
For a Python-Django REST API stack implementing HTTP GET, check out my other article, Creating a Python Django REST Service backend with Postgres database.
For a node.js-ExpressJS-MongoDB (through mongoose) stack REST API backend with CRUD functionality accessed via a React-Data-Grid and Material-UI/DataGrid, please check out my article, Creating a CRUD node.js REST service backend with node.js, Express and MongoDB (mongoose) and a React-Data-Grid / Material-UI DataGrid frontend to access the service.
The above article is further extended with Deploying a Node.js server-side backend CRUD REST service to Heroku connected to a cloud-based MongoDB Atlas database which shows how to deploy the node.js app created to Heroku cloud hosting and also to use the cloud-based MongoDB Atlas for public deployment.
An Android/Google Location API article, Creating a Realtime Handphones Locations Tracker For Android Devices with Google Location API and Google Maps).
