Oracle function cannot be compiled

Question

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;

ELSE If is not an Oracle syntax... PLS use ELSIF instead and it should work... — przemo_pl, Jan 08 '16 at 09:25
That worked but the second one is not compiling :s Should they executed in different sql files ? — maher.belkh, Jan 08 '16 at 09:39
how are you executing them? if via SQL*Plus, you can have them in one file but the statement (function in this case) should end with "/" in next line right after the statement like that procedure a is begin null;--does nothing as this is a demo end; / procedure b is begin null; end; / — przemo_pl, Jan 08 '16 at 09:46
I am executing them in Oracle SQL Developer but one function at a time with the "Execute Statement" (Ctrl + Enter) with (so if one does not compile that does not put the other one in error also ) — maher.belkh, Jan 08 '16 at 09:49
I think you should have the "/" at the end of each statement then.. see this: http://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql — przemo_pl, Jan 08 '16 at 09:56

score 2 · Accepted Answer · answered Jan 08 '16 at 09:28

2

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

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

answered Jan 08 '16 at 09:28

OraNob

684
3
9

Nice ! That fixed the first one ! but the second still doesn't compile :( – maher.belkh Jan 08 '16 at 09:34
@maher.belkh `toReturn VARCHAR2;` must be with the size. `toReturn VARCHAR2(100);` – Tatiana Jan 08 '16 at 09:39
FUNCTION parameters don't need size. (str IN VARCHAR2) but the declaration need size. toReturn varchar2(100); – Muhammad Nadeem Jan 08 '16 at 09:43
My bad guys was i was using a parameter's name (a column referring to a table => REF_AUTRE) – maher.belkh Jan 08 '16 at 09:54
REF_AUTRE what is this? – Muhammad Nadeem Jan 08 '16 at 10:01
if this is column name, so you need to select its value in a variable first. – Muhammad Nadeem Jan 08 '16 at 10:02
2nd function has an undeclared reference. Error(9,34): PLS-00201: identifier 'REF_AUTRE' must be declared – OraNob Jan 08 '16 at 10:06

score 0 · Answer 2 · answered Jan 08 '16 at 09:58

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

Oracle function cannot be compiled

2 Answers2