CODEWITHSUNDEEP
oracle-databasefunctionplsqlcompiler-errors
ninja_mbk
ninja_mbk

Reputation: 513

Oracle function cannot be compiled

This is driving me crazy :D So i am triyng to define oracle 12 functions to simplify a date but i can't get it to compile (tells me that something is missing ..). So here comes the functions : PS : This is function a wrote to transform the abreviated the month but i can't get it to compile :

Function 1:

CREATE OR REPLACE FUNCTION replace_abreviated_Month(str IN VARCHAR2) 
RETURN VARCHAR2
IS
z VARCHAR2;
BEGIN
   IF str like '%Jan%' THEN
      z:= REGEXP_REPLACE (str, 'Jan', '01');
   ELSE if str like '%Feb%' THEN
      z:= REGEXP_REPLACE (str, 'Feb', '02');
   ELSE if str like '%Mar%' THEN
      z:= REGEXP_REPLACE (str, 'Mar', '03');  
   ELSE if str like '%Apr%' THEN
      z:= REGEXP_REPLACE (str, 'Apr', '04');
   ELSE if str like '%May%' THEN
      z:= REGEXP_REPLACE (str, 'May', '05');
   ELSE if str like '%Jun%' THEN
      z:= REGEXP_REPLACE (str, 'Jun', '06');
   ELSE if str like '%Jul%' THEN
      z:= REGEXP_REPLACE (str, 'Jul', '07');
   ELSE if str like '%Aug%' THEN
      z:= REGEXP_REPLACE (str, 'Aug', '08');
   ELSE if str like '%Sep%' THEN
      z:= REGEXP_REPLACE (str, 'Sep', '09');
   ELSE if str like '%Oct%' THEN
      z:= REGEXP_REPLACE (str, 'Oct', '10');
   ELSE if str like '%Nov%' THEN
      z:= REGEXP_REPLACE (str, 'Nov', '11');
   ELSE
      z:= REGEXP_REPLACE (str, 'Dec', '12');      
   END IF;

   RETURN z;
    END replace_abreviated_Month;

Function 2 :

CREATE OR REPLACE FUNCTION delete_time_zone(str IN VARCHAR2) 
RETURN VARCHAR2
IS
    toReturn VARCHAR2;
BEGIN
   IF str like '%CET%' THEN
      toReturn:= REGEXP_REPLACE (REF_AUTRE, 'CET ', '');
   ELSE if str like '%CEST%' THEN
      toReturn:= REGEXP_REPLACE (REF_AUTRE, 'CEST ', '');   
   END IF;

   RETURN toReturn;
END delete_time_zone;

Upvotes: 0

Views: 157

Answers (2)

Mikhailov Valentin
Mikhailov Valentin

Reputation: 1102

This should work:

CREATE OR REPLACE FUNCTION replace_abreviated_Month(str IN VARCHAR2) 
RETURN VARCHAR2 IS
z VARCHAR2(3000);
BEGIN

   IF (str like '%Jan%') THEN
      z:= REGEXP_REPLACE (str, 'Jan', '01');
   ELSIF (str like '%Feb%') THEN
      z:= REGEXP_REPLACE (str, 'Feb', '02');
   ELSIF  (str like '%Mar%') THEN
      z:= REGEXP_REPLACE (str, 'Mar', '03');  
   ELSIF  (str like '%Apr%') THEN
      z:= REGEXP_REPLACE (str, 'Apr', '04');
   ELSIF  (str like '%May%') THEN
      z:= REGEXP_REPLACE (str, 'May', '05');
   ELSIF  (str like '%Jun%') THEN
      z:= REGEXP_REPLACE (str, 'Jun', '06');
   ELSIF  (str like '%Jul%') THEN
      z:= REGEXP_REPLACE (str, 'Jul', '07');
   ELSIF  (str like '%Aug%') THEN
      z:= REGEXP_REPLACE (str, 'Aug', '08');
   ELSIF  (str like '%Sep%') THEN
      z:= REGEXP_REPLACE (str, 'Sep', '09');
   ELSIF  (str like '%Oct%') THEN
      z:= REGEXP_REPLACE (str, 'Oct', '10');
   ELSIF  (str like '%Nov%') THEN
      z:= REGEXP_REPLACE (str, 'Nov', '11');
   ELSE
      z:= REGEXP_REPLACE (str, 'Dec', '12');      
   END IF;

   RETURN z;
END replace_abreviated_Month;

/
create or replace FUNCTION delete_time_zone(str IN VARCHAR2) 
RETURN VARCHAR2
IS
    toReturn VARCHAR2(3000);
BEGIN
   IF str like '%CET%' THEN
      toReturn:= REGEXP_REPLACE(str, 'CET ', '');
   ELSIF str like '%CEST%' THEN
      toReturn:= REGEXP_REPLACE(str, 'CEST ', '');   
   END IF;

   RETURN toReturn;
END delete_time_zone;
/

Upvotes: 0

OraNob
OraNob

Reputation: 694

"z" needs to be declared something like this. z VARCHAR(100);

And the syntax needs to read ELSE if --> Should read ELSIF

Upvotes: 2

Related Questions