Stored Procedure Issue

[ddnh]

I've been asked to write a report that calls a table created by a stored proc. The stored proc deletes the old data in the table and replaces it with new data. Is there a way for me to do this? In the past, I've only worked with SPs that have parameters. I've connected to them directly like any other datasource. If I try to connect directly to this one it gives me an error:

---------------------------
Crystal Reports
---------------------------
Query Engine Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
ORA-06512: at "MAPOWNER.UPDATED_EWALLET_REPORT", line 19
ORA-06512: at line 1
'
---------------------------
OK
---------------------------


If I use the table as the datasource, the data won't be current. The SP has to be run in order to get the current data. Any help would be appreciated.

I'm using CR for .net and an Oracle database.

 

[BobHuk]

In your stored procedure build a temporary table instead of a static one. Somewhere near the top of the SP put the line
"drop table (yourtablenamehere)"

Then build your temporary table within the SP and populate it.

From Crystal call the SP by setting it as the datasource. Each time the report is opened the SP will be run. This will drop the existing table and rebuild it anew. So the data in it will always be the most up to date available.

 

[Isle]

I would be inclined to create a new stored proc that does two things.
First, it calls the stored proc to update the existing table.
Second, it reads that newly updated existing table, and returns that as the result set of the stored proc.
For this to work, you need to have rights to perform the data update. Getting fresh data is a requirement you have identified, but I'm not clear whether you have sufficient rights to do this.

 

[lyanch]

Crystal doesn't allow you to call other stored procedures from within the stored procedure that returns the data in Oracle

Lisa

 

[bdreed35]

Lisa:

Is that just an issue with Oracle because I have called other SP's from within SQL Server SP's. Pardon my ignorance as I have never had to write an Oracle SP.

Thanks.

~Brian

 

[lyanch]

It affects several databases. I don't think I can do it in Sybase either. Oracle has its own set of rules to abide by. If you look on the support site there are several whitepapers on the issues of Oracle and SP's

Lisa