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.

30+ years as a computer programmer. Started with Visual Basic, now doing Java, SpringBoot, React and .NET. Running my own software company at mysoftware2u.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store