Oracle functions in CR / CR custom functions

[thiagarr]

HI,

I use CR XI with Oracle 9.2. I have a requirement to use some functions created in SQL/PLSQL in CR. An example function is as follows:

FUNCTION total_new_users(end_date DATE, time_frame NUMBER)
RETURN NUMBER
IS
    return_value NUMBER(20) := 0;
BEGIN
    SELECT COUNT(1)
    INTO return_value
    FROM oradba.up_user a
    WHERE TRUNC(a.date_created) >= ADD_MONTHS(TRUNC(end_date), -time_frame);
    RETURN return_value;
EXCEPTION
    WHEN OTHERS 
    THEN
        RETURN 0;
END;

Basically, these are used to do a SELECT operation based on the parameters passed to the function and return the resule for further use.

Is there anyway something like this can be used to build a custome function in CR. The key issue I seem to have is the use of database fields which are not allowed to be used in the function editor. Is there anyway I can do something like this by manually typing the complete field name?

I understand that SQL functions can be used directly in CR. Is it possible that Oracle functions can be used similarly? The functions are part of a package and I can see the package name as a Qualifier in CR database Expert but not the details.

Any help would be appreciated.



Thanks and regards,

Chandra