Using Oracle stored prcedures from CR

[keef]

Hello all,

I've successfully added an Oracle stored procedure to my report. When I run the report it asks for values for the parameters etc. Lovely.

Here's an example to make things clearer (I hope)

CREATE PROCEDURE thing(id IN NUMBER
) as
name VARCHAR2

BEGIN
SELECT bg_name
INTO name
FROM bg_table
WHERE bg_id = id;

END;

I've added procedure thing to my report. And I need someway to get the data in 'name' back out to show it on my report. I did see something about creating a PL/SQL table but I still don't see how CR will see it.

If I use IN OUT parameters then Crystal Reports doesn't let me add the procedure to my report.

The only thing I can get out of my procedure is a field named 'TEXT' that never seems to be populated with anything and isn't declared in my procedure!

I'm using CR 7.0 at the mo but will hopefully get my grubby little hands on CR 8.0 before long.

Thanks for any advice that anyone can offer.

Keith Matthews
Billing and Payments Developer
Logica UK

 

[balves]

You need to create a REF CURSOR and return the REF CURSOR as an IN OUT parameter.

There are examples in these Crystal forums for Oracle when others have asked the same question.

 

[keef]

Thanks. I'd been trawling but I couldn't find anything. Then just as you responded I was implementing what's on slides 13-15 of this:

http://www.naaug.org/meetings/2001/presentations/CrystalReports_WebServer.ppt

Exactly what you suggested. It's a good example if anyone else needs to do this...

Thanks again, Keith Matthews
Nexus Billing and Payments Developer
Logica UK

 

[kkashwani]

Write Store procedure something like this

PROCEDURE YourStoreProcedure(
CompanyID IN Varchar2;
RecordSet_Cur OUT REF CURSOR
)
AS
Begin
OPEN
RecordSet_Cur
FOR
SELECT *
FROM TableName
Where Company_ID = CompanyID;

End;

Try to connect this type of stored procedure with your CR where IN parameter in your stored procedure will act as input parameter from report and REF CURSOR Out parameter act as table in CR which contain all the fields from select statement

 

[Guest_imported]

Read this pdf file. It is the best example of how to connect Oracle and Crystal Reports... two of my favorite software... NOT!!!

I was able to get it to work on my first try, as a first time Oracle SQL programmer (longing to MS SQL Server).

By the way, I'm trying to learn more about thr RDC (I have Crystal 8). Any good sites that have code examples on how to set it up and work it?

Thank you.

 

[Guest_imported]

How about I include the link to where the pdf file is.

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