Defining Oracle Database Functions in Designer

building semantic layer
1

I have searched this forum and not seen this topic anywhere. For a particular object, I need to call a database function, which accepts two arguments and returns a value. I defined the function in the Ora7en.prm file, told it where to point and provided the arguments. While the function appears in Designer, when I parse, it returns a message of “Invalid Column” or “From keyword not found where expected.” This function runs properly in every other query tool and all columns are valid. The creator of the function did define variables and assign a few labels within it, so is it possible that Designer is mistaking variables and labels for columns?? We are all really stumped and wonder how Designer makes use of a function and if it can truly work as intended. Below is the code in question. Any insight would be greatly appreciated!

FUNCTION (NAME)

	 (
	     i_STMT_ID NUMBER,
		 i_FLAG INTEGER
	 )
	 RETURN NUMBER
	 IS
	     c_Sum NUMBER(15,2);
	BEGIN
	    SELECT
		    SUM(AMT_LOOP.AMT)
	    INTO
		    c_Sum
		FROM
		    (SELECT DISTINCT DETAIL_ID L_ID
			FROM
				DETAIL,
				XREF_SUM
			WHERE
				XREF_SUM.L_ID = DETAIL.L_ID AND
XREF_SUM.STMT_ID = i_STMT_ID) LEG_LOOP,

(SELECT  DETAIL_AMT.L_ID LEG_ID,
					 DETAIL_AMT.SET_AMT AMT
FROM DETAIL_AMT
WHERE (DETAIL_AMT.CO_FG = i_FLAG)) AMT_LOOP
	WHERE
                LEG_LOOP.LEG_ID = AMT_LOOP.LEG_ID;
		RETURN c_Sum;

END;

Celeste (BOB member since 2003-02-03)

2

Sometimes parsing in Designer is not foolproof. The acid test is to include the object/function in a BO query and see if it runs as expected

Paul Shovlar :uk: (BOB member since 2002-09-05)

3

Celeste,

Don’t bother to include all your own function in the PRM files. BO works just fine without them.

The only restriction is that you have to know what they are and how they have to be used.

Changing the PRM has to be repeated on all developer machines and after every BO upgrade.

A universe with Error messages may be completely acceptable if you know why you have the error messages and why it fails on the designer

a typical example would be the object TODAY SQL: sysdate
if you parse it it will fail because you have no tables in the from.

Run any query with at leas 1 object and the object Today and it will work

ClaireB :de: (BOB member since 2002-08-09)

4

Thanks very much to all for the great suggestions! I’m learning that although objects may not parse in Designer, they can work in Reporter. Thanks again.

Celeste (BOB member since 2003-02-03)