Error PLS-00103 compiling user-defined function in Oracle

I'm trying to create a user defined function in Oracle that will return a DATE when given a text argument containing a date substring. I've tried a couple ways of writing this, and all seem to throw the same error:

CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2(50))
  RETURN DATE DETERMINISTIC IS开发者_JAVA技巧
BEGIN
  RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','\3-\1-\2'), 'YYYY-MM-DD'));
END;

the error:

FUNCTION lm_date_convert Compiled. 1/46

PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

Any thoughts on this, and general UDF writing tips (and good references) are welcome! Thanks.

We cannot restrict the datatype when specifying parameters in stored procedures. That is, just use VARCHAR2 rather than VARCHAR2(50).

Just to prove I'm reproducing your problem ...

SQL> CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2(50))
  2    RETURN DATE DETERMINISTIC IS
  3  BEGIN
  4    RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','\3-\1-\2'), 'YYYY-MM-DD'));
  5  END;
  6  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION LM_DATE_CONVERT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/49     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

SQL>

Now to fix it:

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2)
  2    RETURN DATE DETERMINISTIC IS
  3  BEGIN
  4    RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','\3-\1-\2'), 'YYYY-MM-DD'));
  5* END;
SQL> r
  1  CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2)
  2    RETURN DATE DETERMINISTIC IS
  3  BEGIN
  4    RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','\3-\1-\2'), 'YYYY-MM-DD'));
  5* END;

Function created.

SQL> 

"If you really do want a VARCHAR2(50) then declare a type of VARCHAR2(50) and use the type."

Declaring a SQL TYPE to enforce sizing is a bit of overkill. We can declare SUBTYPEs in PL/SQL but their sizes are not actually enforced in stored procedure signatures. However there are workarounds as I discuss in this other thread.

As an aside, why are you using Regex to solve this problem? Or rather, what problem are you trying to solve which cannot be solved with TO_CHAR and TO_DATE? Oracle's pretty forgiving with format masks.