How to use Oracle Stored Procedure With Crystal? 1

[krist]

Hi All,

I have used Crystal with MSSQL stored procedure with No Problem.

Now we want to support Oracle 8i.
How can I use Oracle Stored procedure with Crystal report ?

(I just found that I CANNOT 'Select * from mytable' in Oracle Stored Proc, While I can do this in MSSQL )

Thanks a lot for any help,
Krist

 

[lyanch]

Crystal has a whitepaper on how to do this. I haven't had any problems (yet).

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

Lisa

 

[krist]

Hi Lisa,

Thanks for the reply, now I get the idea.

In my case, the resultset that I want to return from the stored proc is a result of a process :
tabel_a + tabel_b ==> tabel_c
It is the content of tabel_c that I want to use for the report.

The question is :
Do I have to create a new tabel_c every time I run the report, and then drop the table after the report complete ?
OR
Can I use Temporary Table for this ?

Thanks in advance,
Krist

 

[lyanch]

I use temp tables and load them into the cursor. Basically I copied their example into oracle and then changed things to what I needed. After that I saw how they do it and it is easy. FYI you only need to create one cursor package, just reuse the object for all reports.

Lisa

 

[krist]

Hi,
Thanks for the reply.

One last question :

So, I create one cursor package, and inside it I define REF CURSOR, as many as my reports.

So If I have n reports, and each needs different table structure, I should define the package like this below ?

CREATE OR REPLACE Package Test_Package
AS
Type Test_Type IS REF CURSOR Return Temp_1%ROWTYPE;
Type Test_Type IS REF CURSOR Return Temp_2%ROWTYPE;
Type Test_Type IS REF CURSOR Return Temp_3%ROWTYPE;
Type Test_Type IS REF CURSOR Return Temp_4%ROWTYPE;
....
Type Test_Type IS REF CURSOR Return Temp_n%ROWTYPE;
End Test_Package;
/

Thanks in advance,
Krist

 

[lyanch]

Nope, you only need to create one ref cursor.. its an object that is created "new" when you open it.

so.. it would be something like:

CREATE OR REPLACE Package Test_Package
AS
Type Test_Type IS REF CURSOR Return Temp_1%ROWTYPE;
End Test_Package;
/

Then each of your SP's would be like:

CREATE OR REPLACE PROCEDURE ProcName(
CursorName IN OUT Test_Package.Test_Type,
other parameters)
IS
BEGIN

what ever you need to do here

open CursorName for
select
what you wanna output

......
Same call to the package for a cursor each time.. but each time you will get a "new" cursor..

Lisa

 

[krist]

Hi,

Okay, but here you only talk about one temporary table : Temp_1.
I have previously created Temp_1 with the column as required by report_1.

report_2 need another temporary table with different column.
report_2 : ditto
...

How can one temporary can meet all reports requirement, with various report details ?

Thanks again,
Krist

 

[lyanch]

Don't define the columns in the cursor, they will be defined when you "open cursor". My package looks like:

CREATE OR REPLACE Package Report_Package
IS TYPE Report_Type IS REF CURSOR;
END Report_Package;

ORacle handles the rest when you open it.

Lisa

 

[krist]

Lisa,
Thanks a lot

Krist