current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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;
share|improve this question
3  
Different database engines do things differently. For example, oracle does not have two of the common date functions (dateadd, datediff) that you find in many other database engines. I think the only way to do this well is do it all manually so that you can test everything you do. –  Dan Bracuk Sep 26 '13 at 12:58
    
Can you give me a documentation where I can read the checklist to keep in mind while converting them manually? –  Jeyasithar Sep 26 '13 at 13:00
    
Why dont you think rewrite all of them. Oracle supports different builtin functions from Postgress –  Ugur Sep 26 '13 at 13:10
    
@DanBracuk: you don't need the clumsy dateadd or dateadd in Oracle (or Postgres) because it supports ANSI SQL date arithmetics, e.g. some_date + interval '1' week or a bit simpler current_date + 1 for "tomorrow" –  a_horse_with_no_name Sep 26 '13 at 13:10
    
The function I pasted is just an example. I tried to execute this directly to oracle sql console, getting lot of errors in the console. Ex. ORA-06550: line 7, column 24: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset –  Jeyasithar Sep 26 '13 at 13:15

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
I tried executing the query updated by you. Getting the following error ORA-06550: line 7, column 24: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset –  Jeyasithar Sep 26 '13 at 13:17
    
@Jeyasithar Then you are not telling us everything. See my SQLFiddle it does work. Does Squirrel support the / terminator for PL/SQL definitions? –  a_horse_with_no_name Sep 26 '13 at 13:18
    
I am using datasource explorer plugin in Eclipse to execute the query. –  Jeyasithar Sep 26 '13 at 13:20
2  
@Jeyasithar: Sorry I don't use eclipse so I cannot comment on that. But apparently it does not support creating PL/SQL procedures. You should try SQL Developer, SQL*Plus or any other tool that can use an alternate delimiter (because that's what you need - most probably Eclipse sends everything to the first ; because it doesn't recognize the /) –  a_horse_with_no_name Sep 26 '13 at 13:22
    
Its working. My delimiter was the problem. Thanks. –  Jeyasithar Sep 26 '13 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.