CODEWITHSUNDEEP
sqloracle-databaseuser-defined-functions
lorenzo-s
lorenzo-s

Reputation: 17010

Is it possible to pass a condition to an Oracle user-defined function?

I'm wondering if it's possibile to pass (any way is ok, also hacks) a condition (boolean) to an user-defined function in Oracle.

Let's say I want something like:

CREATE OR REPLACE FUNCTION SCHEMA.MY_FUNC (
    condition IN ???,
    my_value IN NUMBER) RETURN NUMBER IS
BEGIN
    IF condition THEN
        RETURN my_value + 1;
    END IF;
    RETURN my_value;
END;

Upvotes: 1

Views: 1044

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

Assuming you want to be able to call it from plain SQL - otherwise you could just use a BOOLEAN - you can pass a fixed value and interpret that instead. 0/1, Y/N etc are common; using 1 as true (and anything else as false) for example:

CREATE OR REPLACE FUNCTION MY_FUNC (
    condition IN NUMBER,
    my_value IN NUMBER) RETURN NUMBER IS
BEGIN
    IF condition = 1 THEN
        RETURN my_value + 1;
    END IF;
    RETURN my_value;
END;
/

FUNCTION MY_FUNC compiled

select my_func(0, 42) from dual;
select my_func(1, 42) from dual;

MY_FUNC(0,42)
-------------
           42 

MY_FUNC(1,42)
-------------
           43

If you can pass the expression as a string, say, you could hack something together with dynamic SQL I suppose:

CREATE OR REPLACE FUNCTION my_func (
    condition IN varchar2,
    my_value IN NUMBER) RETURN NUMBER IS
    boolstr VARCHAR2(5);
BEGIN
    EXECUTE IMMEDIATE 'SELECT CASE WHEN ' || condition
        || ' THEN ''true'' ELSE ''false'' END FROM dual' INTO boolstr;
    IF boolstr = 'true' THEN
        RETURN my_value + 1;
    END IF;
    RETURN my_value;
END;
/

Which you'd have to call like:

select my_func('1=1', 42) from dual;

So you'd have to build your condition up into a string, like:

select my_func(a ||'='|| b, 42) from <some table with a and b columns>;

This seems rather unwieldy, and allows almost anything to be passed as a condition, which may of course be dangerous (SQL injection possibilities, to put it mildly). If only certain 'conditions' as possible it might be better to have function wrappers that take simple arguments and figure out the boolean condition value to call the real function, so you'd call something like my_func_eq(42, a, b).

I'd also consider whether a function is really necessary - depending on what the function is doing of course, you can probably achieve the same effect in a simple query, e.g. with case statements.

Upvotes: 3

Related Questions