Problem with Oracle stored function

Hello all,
Hopefully someone can help me with this problem: I created a function in Oracle (on SCO) to convert a Unix timestamp number to a date (the number is the number of seconds since 1-Jan-1970). When logged onto sqlplus on the Unix box, the function works fine.

I added the function to oracle\ora7EN.prm to make the function available to BO. However, when I use the function in BO, I get a message from Oracle saying that a non-numeric was found where a number was expected. I’m querying the same data in both environments, so I’m positive that the data that is being passed is strictly numeric. 1. Is there some way to see the actual data that is getting passed to Oracle? The generated SQL looks fine. I was wondering if some spurious characters are getting passed to Oracle for some reason. 2. Has anyone encountered this problem? Any ideas at to how to fix it?

Thanks,
Jerry Clark
Compaq Computer Corp, Tandem Division
Fremont CA


Listserv Archives (BOB member since 2002-06-25)

Have you made sure that the function definition in the prm file states that you are returning a date AND made sure that the object datatype is set to a date as well. Obvious checks but …

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

From: Lori S. Furda [SMTP:LoriSue@WORLDNET.ATT.NET] Sent: 15 October 1998 14:54

BO. However, when I use the function in BO, I get a message from
Oracle
saying that a non-numeric was found where a number was expected. I’m querying the same data in both environments, so I’m positive that
the
data
that is being passed is strictly numeric.

I had a similar problem with functions some time ago. Our function was
called from an object. What I found was BusinessObjects could not understand the output parameter unless it was a number. Our solution was
to return a Julian number from the functin and convert the Julian number to
a date in you SQL fragment in the Universe …

Good luck,
Best Regards, Lori S. Furda
Sage Solutions, Inc.
Lori_SAGE@solution4u.com

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (9am-5pm ET only): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’


Listserv Archives (BOB member since 2002-06-25)

BO. However, when I use the function in BO, I get a message from Oracle saying that a non-numeric was found where a number was expected. I’m querying the same data in both environments, so I’m positive that the
data
that is being passed is strictly numeric.

I had a similar problem with functions some time ago. Our function was called from an object. What I found was BusinessObjects could not understand the output parameter unless it was a number. Our solution was to return a Julian number from the functin and convert the Julian number to a date in you SQL fragment in the Universe …

Good luck,
Best Regards, Lori S. Furda
Sage Solutions, Inc.
Lori_SAGE@solution4u.com


Listserv Archives (BOB member since 2002-06-25)