PL/SQL code conversion #
Empty strings and NULL values #
For Oracle, an empty string is
NULL too. PostgreSQL makes a difference:
IS NULL and empty are different.
Some queries working with Oracle may not work as expected if directly copied.
The most frequent problems are comparing a column with an empty string, and
concatenating with a
NULL. Please refer to what has already been exposed in
the previous chapter about porting queries.
When PL/SQL code is ported to PL/pgSQL, one has to mind the calling code too:
if the application’s code hasn’t converted
NULL, then one has to be
careful about when the function receives an empty string in place of a
Here are some examples illustrating this in Oracle:
IF vidfee IS NULL THEN ...
IF vidfee IS NOT NULL THEN ...
vidfee will be
NULL if it is an empty string or if it is
NULL, and it won’t make any difference. If this calling code accesses a
function with such tests, but passing empty strings, it will work with Oracle.
It will fail with Postgresql, as it is much stricter in this regard. You’ll have
to modify the
IF tests in the converted PL/pgSQL function, to avoid a rewrite
in the calling function.
Here is an example of converted code (first test):
IF COALESCE(vidfee, '') = '' THEN ...
In this example, if
coalesce will return
'', which is
what we want here.
The second test (
IS NOT NULL) is a bit less intuitive:
IF (vidfee IS NOT NULL AND vidfee <> '') THEN ...
One can also start the function with a simple hack, which will greatly simplify the rest of the tests. This is not applicable to SQL though, only to PL/SQL.
IF vidfee = '' THEN vidfee := NULL END;
Ora2Pg does these code conversions automatically by default, the directive
NULL_EQUAL_EMPTY disables this behavior.
Queries and functions execution #
SELECT with no
INTO clause exists, it has to be replaced by
PERFORM. This is also done automatically by Ora2Pg.
Thus, the following extract from a PL/SQL procedure:
BEGIN SELECT ename, sal FROM EMP WHERE empno=7902 FOR UPDATE; ... END;
will be converted to:
BEGIN PERFORM ename, sal FROM EMP WHERE empno=7902 FOR UPDATE; ... END;
In addition, the
EXEC instruction used to retrieve into a variable the return
code from a PL/SQL function doesn’t exist in PostgreSQL. This has to be
rewritten by using
SELECT INTO This is performed by Ora2Pg.
The following PL/SQL extract:
EXEC :a := get_version();
will be converted to:
SELECT get_version() INTO a;
Dynamic queries execution #
To execute a dynamically built query, Oracle provides an
clause. In PostgreSQL, the
IMMEDIATE must be removed, as it in not supported.
EXECUTE is always executed immediately. This is performed
automatically by Ora2Pg.
Furthermore, one should use
to build a dynamic SQL query in PostgreSQL. This avoids SQL injection. This
isn’t performed by Ora2Pg.
For instance, the following SQL code:
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' || v_column || ' = :2'; EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
should be converted this way (note the
: replaced by
sql_stmt := 'UPDATE employees SET salary = salary + $1 WHERE ' || quote_literal(v_column) || ' = $2'; EXECUTE sql_stmt USING amount, column_value;
COMMIT in a routine #
There is no way with PostgreSQL to
ROLLBACK instructions inside a
function or even in a procedure called by a function.
In these cases, it is advisable to perform transaction management in higher level code.
Note that, due to MVCC implementations, PostgreSQL can support long transactions
more easily than Oracle. Also, some intermediate
COMMIT statements can often
be purely and simply deleted.
Exceptions management #
Exceptions’ handling is quite different between Oracle and PostgreSQL. First,
SQLCODE is almost equivalent to PostgreSQL’s
SQLSTATE. One has thus to
be replaced by the other, which is performed by Ora2Pg.
But the most notable difference is the way the error is handled. If an error is
triggered in a PL/SQL block, only the triggering statement is rollbacked. As a
consequence, one often sees savepoints declared at the beginning of the block
ROLLBACK TO SAVEPOINT statements issued in the exception block.
PostgreSQL’s way of handling exceptions is different. When an error is trigger
in a PL/pgSQL block, the whole block is rollbacked by the exception. PL/SQL
SAVEPOINTS with thus be simply ported, by removing the
ROLLBACK TO SAVEPOINT instructions.
The following PL/SQL code:
BEGIN SAVEPOINT s1; ... EXCEPTION WHEN ... THEN ROLLBACK TO s1; ... WHEN ... THEN ROLLBACK TO s1; ... END;
will be simplified this way:
BEGIN ... EXCEPTION WHEN ... THEN ... WHEN ... THEN ... END;
Some exceptions’ names will also have to be replaced. Here are the errors that will have to be replaced:
raise_application_error will be converted to
RAISE EXCEPTION. The following code from Oracle:
raise_application_error( -20000, 'Unable to create a new job: a job is currently running.' );
must be converted this way:
RAISE EXCEPTION 'Unable to create a new job: a job is currently running';
SELECT INTO #
SELECT ... INTO ... statement will need adaptation to behave with
PostgreSQL the same way it does with Oracle. PostgreSQL’s
STRICT option is
Oracle’s default behavior. One will have to add the
STRICT keyword after
INTO when an exception on
TOO_MANY_ROWS exists in the
same code block.
Thus, this procedure in PL/SQL:
BEGIN SELECT feegroupid INTO vsalegroup FROM feegroup WHERE feeclass = 'V' AND isdefault = 1 AND ispublic = vispublic; EXCEPTION WHEN NO_DATA_FOUND THEN vidartpxvte := '-040'; RETURN vidartpxvte; WHEN TOO_MANY_ROWS THEN vidartpxvte := '-045'; RETURN vidartpxvte; END;
has to be converted this way for PostgreSQL:
BEGIN SELECT feegroupid INTO STRICT vsalegroup FROM feegroup WHERE feeclass = 'V' AND isdefault = 1 AND ispublic = vispublic; EXCEPTION WHEN NO_DATA_FOUND THEN vidartpxvte := '-040'; RETURN vidartpxvte; WHEN TOO_MANY_ROWS THEN vidartpxvte := '-045'; RETURN vidartpxvte; END;
BULK COLLECT #
There is no
BULK COLLECT in PostgreSQL. This is used in Oracle to load the
content of a query in an array, and then iterate over this array.
For example, this Oracle code:
CREATE PROCEDURE AllAuthors IS TYPE my_array IS varray(100) OF VARCHAR(25); temp_arr my_array; BEGIN SELECT author_name BULK COLLECT INTO temp_arr FROM authors ORDER BY author_name; FOR i IN temp_arr.first .. temp_arr.last LOOP DBMS_OUTPUT.put_line(i || ') author_name: ' || temp_arr..(i)); END LOOP; END AllAuthors;
can be translated to:
CREATE FUNCTION AllAuthors() RETURNS VOID AS $$ DECLARE temp_arr VARCHAR(25); BEGIN temp_arr := (SELECT author_name FROM authors ORDER BY author_name); FOR i IN array_lower(temp_arr,1) .. array_upper(temp_arr,1) LOOP RAISE NOTICE '% ) author_name: %', i, temp_arr..(i); END LOOP; END; $$ LANGUAGE plpgsql;
instr function #
PostgreSQL’s documentation proposes a PL/PgSQL implementation of Oracle’s
in its chapter about
porting from Oracle PL/SQL.