View source | Discuss this page | Page history | Printable version   

PL-SQL code rules to write Oracle and Postgresql code

Contents

Procedure Language rules

Openbravo supports Oracle and PostgreSQL database engines.

The minimum required version are:

as described in the System Requirements.

This is a set of recommendations for writing Procedure Language that works on both database backends (when possible) or that can be automatically translated by DBSourceManager.

These recommendations assume that you have written code in Oracle and that you want to port it to PostgreSQL. So they point out many features/constructs which oracle adds on top of the SQL standard and which are thus oracle specific and not available in PostgreSQL.

To help developers to test if their code can be translated by dbsourcemanager the ant export.database command contains an Translation consistency check which does the complete standardization/translation cycle once and reports and differences found (ignoring whitespace).


Bulbgraph.png   Note: It is strongly recommended to avoid (when possible) using PL/SQL when writing new code and write it using DAL instead.

General rules

This a list of general rules that assure that PL runs properly on different database backgrounds.

CASE WHEN variable IS NULL THEN X ELSE Y END

If the variable is the result of concatenating several strings () are required.

SELECT * FROM (SELECT 'X' FROM DUAL) A
SELECT field AS field_name FROM DUAL
COALESCE(variable_integer, )

do

COALESCE(variable_integer, 0)

to guarantee that it will also work in PostgreSQL.

RIGHT: COALESCE(movementdate, TO_DATE('01-01-1900', 'DD-MM-YYYY'))


Cursors

There are two different ways of using cursors: in FETCH clauses and in FOR loops. For FETCH cursor type no changes are required (except for %ISOPEN and %NOTFOUND methods that are explained below).

Oracle FETCH cursor declarations:

CURSOR	Cur_SR IS

should be translated in PostgreSQL into:

DECLARE Cur_SR CURSOR FOR

For cursors in FOR loops the format suggested is:

TYPE RECORD IS REF CURSOR;
	Cur_Name RECORD;

that is both accepted by Oracle and PostgreSQL.


Arrays
Bulbgraph.png   Currently Arrays are not supported by dbsourcemanager so cannot be used in PL-SQL code.

In Oracle, arrays are defined in the following way:

TYPE ArrayPesos IS VARRAY(10) OF INTEGER;
  v_pesos ArrayPesos;
v_dc2 := v_dc2 + v_pesos(v_contador)*v_digito;

but in PostgresSQL they are defined as:

v_pesos integer[];
v_dc2 := v_dc2 + v_pesos[v_contador]*v_digito;
ROWNUM

To limit the number of registers that a SELECT command returns, a cursor needs to be created and read the registers from there. The code could be similar to:

--Initialize counter
v_counter := initial_value;
--Create the cursor
FOR CUR_ROWNUM IN (SELECT CLAUSE)
LOOP
  -- Some sentences
  --Increment the counter
  v_counter := v_counter + 1;
  --Validate condition
  IF (v_counter = condition_value) THEN
    EXIT;
  END IF;
END LOOP;


 %ROWCOUNT

SQL%ROWCOUNT cannot be used directly in PostgreSQL. To convert the SQL%ROWCOUNT into PostgreSQL its value should be defined in a variable. For example:

GET DIAGNOSTICS rowcount := ROW_COUNT;

In place of SQL%ROWCOUNT the previously declared variable should be used.


 %ISOPEN, %NOTFOUND

PostgreSQL cursors do not support %ISOPEN or %NOTFOUND. To address this problem %ISOPEN can be replaced by a boolean variable declared internally in the procedure and is updated manually when the cursor is opened or closed.


Formating code
RAISE NOTICE '%', '@Message@' ;
RAISE EXCEPTION '%', '@Message@' ; --OBTG:-20000--
 IF (CONDITION)
    COMMAND;
 END IF;
 SELECT * into VAR from FUNCTION();
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;
  :: interval -> to_interval(,)
  :: double precision -> to_number()
Elements not supported by dbsource manager

Functions

PERFORM and SELECT are the two commands that allow calling a function. Since PostgreSQL does not accept default function parameters we define an overloaded function with default parameters.

To allow the automatic translator to do its job the following recommendations should be followed:


Procedures

There are two ways of invoking procedures from PosgreSQL:


Views

PostgreSQL does not support update for the views. If there is the need of updating a view a set of rules should be created for the views that need to be updated.

In PostgreSQL there are no table/views USER_TABLES or USER_TAB_COLUMNS. They should be created from PostgreSQL specific tables like pg_class or pg_attribute.


Triggers

Rules that the triggers should follow:

IF INSERTING OR (UPDATING AND :OLD.FIELD = ) THEN

The correct way of expressing this is:

IF INSERTING THEN 
... IF UPDATING THEN
IF :OLD.NAME = THEN

If you are using the automatic translator consider that:

Retrieved from "http://wiki.openbravo.com/wiki/PL-SQL_code_rules_to_write_Oracle_and_Postgresql_code"

This page has been accessed 30,848 times. This page was last modified on 22 October 2019, at 20:45. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.