Convert Stored Functions from PostgreSQL to Oracle

1. Structure of Code. All major differences between structure of stored functions in PostgreSQL and Oracle are illustrated in example below. In PostgreSQL stored functions are defined as follows:

CREATE FUNCTION function_name(arg1 type1, arg2 type2, ...) RETURNS type
    LANGUAGE plpgsql
    AS $$
DECLARE
    -- declaration of local variables
    var1 type1;
    var2 type2;
    ...
BEGIN
    statement1;
    statement2;
    ...
END
$$ LANGUAGE plpgsql;

In Oracle the same function has to be declared like this:

CREATE OR REPLACE FUNCTION function_name(arg1 type1, arg2 type2, ...) RETURN type
IS
    -- declaration of local variables
    var1 type1;
    var2 type2;
    ...
BEGIN
    statement1;
    statement2;
    ...
END;
/

2. Unsupported keywords. The following keywords are PostgreSQL specific and must be removed when mirgating code to Oracle:

COST number
DECLARE
LANGUAGE (at the end of function body)
PERFORM (calling another function)
VOLATILE

3. RETURNS TABLE. PostgreSQL allows function to return table of predefined records. Oracle does not have such capabilities, however it provides "PIPELINED" technique to iterate result of SELECT query as a table. For example, the following PostgreSQL code:

CREATE FUNCTION some_function(...) RETURNS TABLE(f1 int, f2 int)
...
    RETURN QUERY 
    SELECT 
        f1, f2 
    FROM t1 WHERE ...
END
$$;

should be converted into Oracle format like this:

CREATE TYPE my_type AS OBJECT(
    f1 int, 
    f2 int
);
/
CREATE TYPE my_table AS TABLE OF my_type; 
/

CREATE OR REPLACE FUNCTION some_function(...) 
RETURN my_table
PIPELINED IS 
BEGIN
    FOR r IN (	
        SELECT 
            f1, f2 
        FROM t1 WHERE ...  
    )
    LOOP
        PIPE ROW(
            r.f1, r.f2
        );
    END LOOP;    
END;
/

4. No length and precision in function parameter definitions. Unlike PostgreSQL, Oracle does not allow length and precision specification in declaration of function parameters.

5. SELECT meta-queries. Sometimes, SELECT queries may be used to initialize a variable rather than extract data from tables. For example:

SELECT FLOOR(abs(result_lat)) INTO part_entera;

In Oracle such queries must be ended by FROM dual.

6. EXECUTE. Both PostgreSQL and Oracle use this statement to execute dynamic SQL queries. However, in Oracle it must be used with keyword IMMEDIATE:

EXECUTE IMMEDIATE sql query;

7. RAISE NOTICE. PostgreSQL provides this statement to output a message to console. Oracle uses DBMS_OUTPUT.PUT_LINE(...) function for the same purpose.

8. Sequences. Both PostgreSQL and Oracle support sequences. Once the sequence is created, PostgreSQL may use it to get incremented values as follows:

nextval('sequence_name'::regclass)

Oracle uses similar syntax with some variation:

sequence_name.NEXTVAL

9. Embedded Functions. Last step of converting PostgreSQL stored functions into Oracle format is to replace embedded functions and operators that are specific for PostgreSQL with Oracle equivalents. More details on this topic are available in this article.