Home » Developer & Programmer » Reports & Discoverer » Want info abot calling oracle stored procedure from crystal reports!!!
Want info abot calling oracle stored procedure from crystal reports!!! [message #89293] Thu, 22 January 2004 22:16 Go to next message
Buland
Messages: 1
Registered: January 2004
Junior Member
Dears,

I have an oracle stored procedure which calculates according to the given criteria ( it will come from asp side) and put the records in the oracle temp table. so, I want to call this procedure from crystal reports, so that report can easily get the data before deletion as all the working will be in the same session.

So how can i accomplish my task?

I am using crystal reports 9.

Thanks.

Regard's

Buland
Re: Want info abot calling oracle stored procedure from crystal reports! [message #89319 is a reply to message #89293] Thu, 05 February 2004 13:19 Go to previous message
Alex
Messages: 190
Registered: August 1999
Senior Member
General considerations: you haven't specify your Oracle version; assume 8i or better. You cannot do this with any other version of RDBMS.

1. Create a table [[table1]] with a structure corresponding your resultset (the one you wish to return)

2. Create a package
PACKAGE my_cursor AS
TYPE my_data IS REF CURSOR RETURN table1;
END my_cursor;
/* note that my_data is declared as rowtype of the table1 which will contain records you wish to return */

3. Create a stored procedure
PROCEDURE GET_DATA
(
param1
,ret_cursor IN OUT my_cursor.my_data
) AS
BEGIN
-- do something --
-- return the recordset from the table --
OPEN ret_cur FOR SELECT * FROM table1;
RETURN;
END;
/* the procedure MUST NOT be inside a package */

4. Configure ODBC DSN; make sure that checkbox "Procedure Returns Results" is CHECKED.

5. Add the stored procedure as data source (like table, view etc.)

6. Enjoy!
Previous Topic: How do I get a .txt report from Oracle 1 1 i?
Next Topic: Monthly report?
Goto Forum:
  


Current Time: Fri Mar 29 06:05:51 CDT 2024