CODEWITHSUNDEEP
sqloracle-databasepostgresqldatabase-migrationenterprisedb
Jeyasithar
Jeyasithar

Reputation: 535

Database migration from PostgreSQL to Oracle

I am migrating my database server from PostgreSQL to Oracle 11g. I tried various tools like Squirrel SQL. But most of the migration tools support table and views to migrate. I cannot find a solution to migrate my procedures and functions. What is the way I can migrate the functions and procedures. Please find one of the functions below.

CREATE OR REPLACE FUNCTION hrms.fngetstatus(iactdate character varying, ideactdate character varying)
  RETURNS character varying AS
$BODY$
   returnval   VARCHAR (1);
BEGIN
   IF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                      TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
      AND TO_DATE (ideactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
        returnval := 'D';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') IS NULL
   THEN

       returnval := 'D';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
        returnval := 'A';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') IS NULL
   THEN
      returnval := 'A';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
      returnval := 'D';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') =
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') =
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
      returnval := 'D';
        ELSIF     TO_DATE (ideactdate, 'mm/dd/yyyy') >=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (iactdate, 'mm/dd/yyyy') is null                      
   THEN
      returnval := 'A';  
        ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')   
   THEN
      returnval := 'A';  
        ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')   
   THEN
      returnval := 'D';   
          ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')   
   THEN
      returnval := 'D';    
   END IF;
   RETURN returnval;
END$BODY$
  LANGUAGE edbspl VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION hrms.fngetstatus(character varying, character varying)
  OWNER TO enterprisedb;

Upvotes: 4

Views: 7383

Answers (1)

user330315
user330315

Reputation:

The only thing you need to change is the code "around" the function. Oracle uses a different "header" than Postgres.

So the function in PL/SQL would look like this. I only changed the beginning CREATE OR REPLACE and the part after the final END. 

CREATE OR REPLACE FUNCTION fngetstatus(iactdate varchar, ideactdate varchar)
  RETURN varchar 
AS
   returnval   VARCHAR (1);
BEGIN
   IF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                      TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
      AND TO_DATE (ideactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
        returnval := 'D';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') IS NULL
   THEN

       returnval := 'D';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
        returnval := 'A';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') IS NULL
   THEN
      returnval := 'A';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
      returnval := 'D';
   ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') =
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') =
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
   THEN
      returnval := 'D';
        ELSIF     TO_DATE (ideactdate, 'mm/dd/yyyy') >=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (iactdate, 'mm/dd/yyyy') is null                      
   THEN
      returnval := 'A';  
        ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')   
   THEN
      returnval := 'A';  
        ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')   
   THEN
      returnval := 'D';   
          ELSIF     TO_DATE (iactdate, 'mm/dd/yyyy') >
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')
         AND TO_DATE (ideactdate, 'mm/dd/yyyy') <=
                       TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy')   
   THEN
      returnval := 'D';    
   END IF;
   RETURN returnval;
END;
/

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/94990/2

Upvotes: 3

Related Questions