How to use Oracle Function in Crystal Report ?

[aryadk]

Hi,
I want to use Oracle Function in Crystal Report 8.5. The result from this function will be used to filter data from other table. This is what the function is doing:

SELECT QUOTATION_ID
FROM WORK_ORDER_QUOTATION_REP
WHERE RESULT_KEY = p_result_key_
AND param_type_ = 0
UNION
SELECT p_result_key_
FROM dual
WHERE param_type_ <> 0;


Note that p_result_key_ and param_type_ are parameters defined in Crystal report.

 

[synapsevampire]

Consider creating a Stored Procedure and use that.

-k

 

[aryadk]

Thanks for the response,
I wrote the procedure and when I try to include in into the report, it tries to use it like a table. If I don't include it while creating the report, I am not sure how to pass the Report parameters as parameters to the procedures. Here is the code that I wrote to create the procedure:

CREATE OR REPLACE PROCEDURE TEST1 (
ifs_result_key_ IN OUT NUMBER,
param_type_ IN NUMBER )
IS

return_value_ WORK_ORDER_QUOTATION_REP.QUOTATION_ID%TYPE := NULL;

CURSOR get_quotation_id IS
SELECT QUOTATION_ID
FROM WORK_ORDER_QUOTATION_REP
WHERE RESULT_KEY = ifs_result_key_
AND param_type_ = 0
UNION
SELECT ifs_result_key_
FROM dual
WHERE param_type_ <> 0;

BEGIN

OPEN get_quotation_id;
FETCH get_quotation_id into return_value_;
CLOSE get_quotation_id;
ifs_result_key_ := return_value_;

EXCEPTION
WHEN OTHERS THEN
ifs_result_key_ := return_value_;

END TEST1;

 

[synapsevampire]

An SP must return rows to the report.

Crystal is a reporting package, not a programming language, so it logically expects that you want to return data, so make sure that the SP returns data in the end, that doesn't mean that you have to use that data, but why use Crystal if that's the case.

You really didn't share anything technical about what happened, did you get an error?

Note that you need to turn on the Procedure returns results in the CR ODBC driver options if you're using that connectivity (and you probably should be).

-k

 

[rhinok]

SV's got your back. You can't use Functions (Oracle or SQL Server User Defined) in a report. Instead, you have a few options:[ol][li]Create a View and refer to the function within the View - If your base query isn't particulary complex, this is probably a good option[/li][li]Create a Stored Procedure and refer to the Function within the Stored Procedure (instead of simply writing a proc that returns the Function as you did above) - I generally only use Stored Procedures for complex data crunching. With Oracle, there are special considerations when creating Stored Procedures to be used by Crystal Reports. Check out the following White Paper from Crystal Decisions:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf

[/li][li]Use the base query as the basis for your report and write a SQL Expression within the report that acts as a correlated subquery that you can use to filter the data[/li][/ol]

~Kurt