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.

CREATE FUNCTION for a Stored Procedure

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;
  /

Database and SQL Migration Tools

About SQLines

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

You could leave a comment if you were logged in.