Calling Oracle Functions in a universe select statement

building semantic layer
1

I have the following statement:

SELECT CALCULATEKPI(170, date ‘2009-09-01’, date ‘2010-11-01’, ‘o1’)
FROM
DUAL

when I use Toad or SQL-plus I get a result.
But if I use this select statement in a Universe/ Deski report, I get an Empty value (null)???

nicothoen :netherlands: (BOB member since 2008-11-20)

2

Hi,

when I execute your SQL in Toad I am getting error ’ ORA-00904:“CALCULATEKPI” : invalid identifier ’

SELECT CALCULATEKPI(170, date ‘2009-09-01’, date ‘2010-11-01’, ‘o1’)
FROM DUAL

assist (BOB member since 2006-04-17)

3

You will, its a custom function :), it does not exist in your database :).

Is this using the BO user account as defined in your universe connection?
Maybe, its a permissions issue?
It could also, possibly, be something to do with driver or prm file settings for that date format?

Mak 1 :uk: (BOB member since 2005-01-06)

4

CALCULATEKPI is an selfmade oracle function, that give results in Toad/TSQL.

The function does work within a Universe of Deski.
But the result is Null.

There is something between oracle database and Deski/Universe/Connection, that disrupts the result of the function.
But I do not know what??

nicothoen :netherlands: (BOB member since 2008-11-20)

5

Please have a look at this post:-

https://bobj-board.org/t/84923

Mak 1 :uk: (BOB member since 2005-01-06)

6

The BO user account used in the universe connection, is the same account as the account used in Toad/tsql.
The result in de dataprovider is still Empty (Null)

I’ve looked at the PRM files, but there is nothing special about the date formats.
I’ve tested several options with date formats, changed languages in BO client etc.

nicothoen :netherlands: (BOB member since 2008-11-20)

7

Nico,

SELECT CALCULATEKPI(170, date '2009-09-01', date '2010-11-01', 'o1') 
FROM 
DUAL

shouldn’t it be

SELECT CALCULATEKPI(170, to_date('2009-09-01','YYYY-MM-DD'), to_date('2010-11-01','YYYY-MM-DD'), 'o1') 
FROM 
DUAL

Is there a COMMIT in your custom function ?
As far as I know a COMMIT clears the resultset before it is ‘moved’ to BO.

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

8

@JdVries
date ‘yyyy-mm-dd’ is a valid structure for passing a date (although, yes, it is more common to use to_date)

I can guarantee that there is not a commit in this function (without even seeing the code) If there was a commit, the op would not be able to call it within a select statement.

Did not know that. Every day is a school day.

pablolee :uk: (BOB member since 2008-07-29)