Usage of Oracle Stored Procedure in Crystal Reports 2

[anganesh]

Hello,

I am trying to use Oracle Stored Procedure in Crystal Reports and we are encountering oracle error 'no data found...'

We don't have the concept of RecordSet in Oracle Stored Procedures. In oracle, the RecordSet equivalent is pl/sql tables (dynamically populated temporary tables similar to arrays...Should I use pl/sql tables)

Has anyone used Oracle Stored Procedures in Crystal Reports???

 

[deborahb]

Please someone, answer this. I want to know also! Deborah

 

[DianeJ]

The way I use Oracle Stored procedures with my reports is by returning the data in a ref cursor.

So first I declare a package to use:

create or replace package spm as
type rctl is ref cursor;
end;

then you create your stored procedure with whatever parameters you want, returning whatever data you want:

CREATE OR REPLACE procedure spm_MyProcedure(
parameter1 in table1.column1%TYPE,
parameter2 in table1.column2%TYPE,
rc1 in out spm.rctl) is
begin
open rc1 for
select table1.column3, table1.column4
from table1
where table1.column1 = parameter1 and
table1.column2 = parameter2;
end spm_MyProcedure;

In order to access the stored procedure in Crystal Reports you need to do the following 2 things;

1. connect to the database using the native dB drivers (p2sora7.dll is the one that I use)
2. in Crystal Reports designer File->Options in the SQL tab you need to select 'Allow reporting on Stored Procedures'

Once you have compiled your stored procedure in Oracle you can go into Crystal Reports and create a new report (select SQL/ODBC button in Crystal Reports 7 Report Expert). Once you have logged on to your database you should see SPM_MYPROCEDURE along with all the tables in your database. You can select the stored procedure and from then on the Report Expert treats it like a table.

 

[alam]

Dear Diane,
I had used the procedure for first time and tried it with ur above example.Everything works fine except for the fact that, i am getting "rc1" as the parameter shown in the report,I tried it making only OUT but still it shows up..
So, do u have any idea how to modify it so that i jus get the IN parameters shown in my reports.
Thank You,
This is first time i am gonna work on reports with procedures, i am pretty excited.., need some help on that OUT parameter..

 

[MalcolmW]

For those interested the details, specifically how things vary from version to version, driver to driver, etc...

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

A sample oracle stored proc is included in this file. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[DianeJ]

In order for the Oracle stored procedure and the report to communicate with each other you need an IN OUT REFCURSOR parameter (rc1 in my example).

If you make it just a regular OUT parameter (not a REFCURSOR) you cannot pass a recordset to the report. If you make the REFCURSOR parameter just OUT then the stored procedure will not show up in the list of tables/stored procedures to create the report from.

Because the rc1 parameter is an IN OUT REFCURSOR, it will show up in the report as a parameter in the prompt box. But there is no need to enter a value in for it. In my VC++ app parameter dialog box I hide this parameter from the user.

Side note: if you use stored procedures with SQL Server you do not need to use a REFCURSOR parameter.

 

[CBear]

Hi Diane,

I like the clarity of your example. I am able to create the package, and stored procedure. I am also able to select the Stored Procedure when creating a new report. However, when prompted for the Parameters, and after inputting a parameter value in the tab I get an "ODBC Error: [Microsoft][ODBC Driver Manager]SQL datatype out of range" error. I made the stored procedure really simple, just one parameter, and one field. The field is a datetime field. Just to let you know, when I get to the stage where I try to create a new report, and have to enter values for my prompts, I have two prompts, parameter1 and rc1. I select a date for parameter1 and leave rc1 alone. I click the OK button then I get the error. Any ideas? I am able to create a stored procedure with Access, but I think this is sloppy. The version of Oracle I'm using is 7.3.4. Again any hints are greatly appreciated.

Cheers.

 

[mcarval2]

Hello, I am trying to use procedure oracle in the crystal but something no this working right, I already verified the document in the site of the seagate on the subject and it helped me a lot, but now I believe that the mistake is other.

Do I have the oracle 8.1.6.0.0 and the crystal 8.0.0.371, was my procedure created and done test with success but does all try to open in the crystal desing through odbc the procedure asks the parameters and how much squeeze the button ok appears the message crw32.exe - Application Error and does it abort the Design, does some exist bug on this subject????


Thank you.

 

[Interdev]

Hello,
Now the Procedure Oracle and Crystal Report with ASP it is work fine, but I need to go in advance. Can I use PL/SQL in Oracle Storage Procedure ???, because with cursor I have some dificult,works but with some limitation, are there a possibility to work with PL/SQL in Oracle ???

Thanks and excuse me my english.

 

[puchomi]

Suppose if I want to query 4 tables then how do I do that in the procedure ??