CREATE FUNCTION Statement - PostgreSQL to Oracle Migration

In PostgreSQL, the CREATE FUNCTION statement creates a user-defined function or stored procedure. In Oracle, you can use CREATE FUNCTION and CREATE PROCEDURE statements.

If a PostgreSQL function does not return any value i.e. defined as RETURNS void, you can convert it to a stored procedure in Oracle:

PostgreSQL:

  -- Inserts a row to a table
  CREATE OR REPLACE FUNCTION insert_city (name VARCHAR(70)) 
  RETURNS void AS $$
  BEGIN
    INSERT INTO cities VALUES (name);
  END;
  $$ LANGUAGE plpgsql;

Oracle:

  -- Inserts a row to a table
  CREATE OR REPLACE PROCEDURE insert_city (name VARCHAR2) 
  AS
  BEGIN
    INSERT INTO cities VALUES (name);
  END;
  /

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

• PostgreSQL to Oracle Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - February 2013