local function inside PL/SQL script

Question

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

score 26 · Accepted Answer · answered Feb 18 '14 at 14:38

26

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.

answered Feb 18 '14 at 14:38

Alex Poole

183,384
11
179
318

Thanks Alex. This is what I get! Anyway, I cannot use my function if I replace (getting an error): DBMS_OUTPUT.PUT_LINE(is_valid_date('20120101', 'YYYYMMDD' )); with: SELECT is_valid_date('20120101', 'YYYYMMDD') from DUAL; – user1630809 Feb 18 '14 at 14:54
1

@user1630809 - no, not from plain SQL, because it's local to the PL/SQL block, which is what you wanted. You can't have it both ways. You could create a real function at the start of the script and drop it again at the end, but that's not a good approach as it incurs significant overhead, and also DDL does an implicit commit which might burn you one day. I'm not sure why you wouldn't want to just create this once as a global utility function, possibly in a package of useful functions. What's your wider goal here? – Alex Poole Feb 18 '14 at 14:58
It is not clear to me when you say "it's local to the PL/SQL block": PUT_LINE and SELECT would be in the same position. In which part of the block is it possible to use a sql statement such as select or update that uses my function? I'm not allowed to create real function in the DB. – user1630809 Feb 18 '14 at 15:08
1

@user1630809 - you cannot use a function declared inside your block from a `select` or `update`. You can select a value into a variable, and then call your function with that variable; but you can't do it in one step. You can't call your function from outside the block because it ceases to exist when the block ends - it's only in-scope within the block (between the `DECLARE` and `END`). `PUT_LINE` is part of the `DBMS_OUTPUT` package so doesn't have that restriction, but it's a procedure so you can't refer to that in SQL either. `SELECT` is a whole different class of command. – Alex Poole Feb 18 '14 at 15:16
Thanks Alex for your explanation. I suppose that the 'scope within the block (between the DECLARE and END).' is referred to 'DECLARE' and 'END is_valid_date'. Now it is clear, as it seems clear that I cannot find a solution to my problem as I wish. – user1630809 Feb 18 '14 at 15:23
@user1630809 - no, between `DECLARE` and the final `END`, otherwise your call within `DBMS_OUTPUT.PUT_LINE` wouldn't work. But you can't use it within a `select` even there, so you're still stuck. Without more context I can't suggest an alternative approach though. – Alex Poole Feb 18 '14 at 15:36

local function inside PL/SQL script

1 Answers1