Yet another question ;-) (still usi

[crystalvictim]

Yet another question ;-) (still using CR8.5 professional)
Is there any known possibility to deal with user-defined database-functions in a report? we have an oracle database wherein some user-defined functions are stored. These functions can be used like common sql functions when accessing the database via sqlplus. so why shouldn't this work in crystal, too? but if it works, how do i have to use these functions? i tried a sql-expression-field but obviously the function wasn't recognized.
so i'd really like to know if anybody has already succeeded in such an issue?

 

[dgillz]

Yes, you can add a user defined function, but I am not sure you can use it in 8.5 professional. 8.5 developer definately gives you this power.

Basically you create a VB project to define your user function and the project name must begin with CRUFL. You then save this as a .dll and you must register the .dll on the machine(s) you want to have the new function.

George's Peck's book "Crystal Reports v8.5 - the Complete Reference" has a chapter that covers this in detail and I was able to create my own functions even though I am a VB novice.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[crystalvictim]

thanx dgillz,
(un-)fortunately, we don't use crystal with vb ;-). we have functions written in pl/sql by users (or to be more precise: by other developers) which are stored in our oracle database. But it looks like this ain't gonna work (at least with CR8.5 pro).
Nevertheless thank you very much for the information - you never know when you may need id .-)

 

[AnnetteH]

I have no problems calling user defined PL/SQL functions in Crystal 9 using SQL Expressions. What kind of errors are you getting? Have you verified that the Crystal user has privileges to execute the functions? Are you prefixing the function with a package or schema name if required? Also, be aware that the functions will not show up in the SQL Expression Editor lists, but you can type them in and use them anyway.

 

[crystalvictim]

I think I finally figured it out .
First, I was getting a "not implemented" error. After checking privilleges and testing via sqlplus, I tried to use an sql expression with simple function (like substr), which led to the same error. So I checked the database connection and changed it from ODBC to a native oracle driver. From then on, at least substr was working ;-)

But I got several other error messages:
"ORA-01007: variable not in select list"
"Error in compiling SQL Expression"
"Error detected by database DLL"
After some time and some tries I even got error messages when using substr :-(

So I started playing again and finally realized, that the db-driver's interface seems to be very touchy, i.e. it works if I enter the complete expression correctly BEFORE running the report for the first time. After running it and changing the sql-expression (e.g. using another field) usually leads to an error which won't disappear until restarting CR.

But anyway, it seems to work