Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

local function inside PL/SQL script

I'm trying to execute this code in Oracle 10 SQL Developer:

FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 ) 
RETURN numeric IS
    l_date VARCHAR2(100);
BEGIN
    l_date := TO_date( p_val, p_format );
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END is_valid_date;


BEGIN
DBMS_OUTPUT.PUT_LINE(is_valid_date('20120101', 'YYYYMMDD' )); 
END;

but I get a generic error without any specific Oracle code, as if it is a syntax problem.

I need to check if a date is valid and, as there is no Oracle built in function for that, I have defined it inside my script (I don't want it to be global or stored somewhere).

Edit:

I have found a solution on an oracle forum using oracle regexp, instead of a function. My script is:

BEGIN

select * from mytable where not REGEXP_LIKE(mydatefield, '(((0[1-9]|[12]\d|3[01])\.(0[13578]|1[02])\.((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\.(0[13456789]|1[012])\.((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\.02\.((19|[2-9]\d)\d{2}))|(29\.02\.((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))')

END;

where mydatefield is in the format DD.MM.YYYY

like image 445
user1630809 Avatar asked Feb 18 '14 14:02

user1630809


People also ask

Can we write a function inside procedure in Plsql?

Is it possible to create a function inside a procedure in oracle? Hi Param, The answer is : YES.

What is local procedure in PL SQL?

A local module is a procedure or function that is defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is defined only within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside that enclosing block.

How do you declare a local variable in PL SQL?

After the declaration, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name. Syntax for declaring variable: Following is the syntax for declaring variable: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]


1 Answers

If that's your entire script, you're missing the DECLARE keyword at the start of your anonymous block:

DECLARE
    FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 ) 
    RETURN numeric IS
        l_date VARCHAR2(100);
    BEGIN
        l_date := TO_date( p_val, p_format );
        RETURN 1;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN 0;
    END is_valid_date;

BEGIN
    DBMS_OUTPUT.PUT_LINE(is_valid_date('20120101', 'YYYYMMDD' )); 
END;
/

anonymous block completed
1

Without that you'll get a series of errors starting with

Error starting at line : 1 in command -
FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 )
Error report -
Unknown Command

... which I imagine is the 'generic error' you referred to.

like image 164
Alex Poole Avatar answered Oct 22 '22 12:10

Alex Poole