Crystal Rpts; Problems trying to call Oracle Stored Proc with In and Out Parameters

[Statey603]

I am still wrestling with implementation of a reprot that calls an Oracle Stored Procedure to update a table with the last run date and time for the report. The Stored Procedure takes a string argument (report name) and returns a string (pass/fail) because somewhere I read that Crystal Stored Proc calls were expecting something be returned.

When I attempt to add the Stored Procedure to a new report using the Design wizard, I get prompted by Crystal for the input parameter. When I click OK, I get the following error message: wrong number or types of arguments. I never get a prompt for the return parameter. I am not sure if I have to assign the Stored Procedure to a report field? I am getting very confused.

Crystal Reports 2011 version 14

thanks

 

[hilfy]

Crystal is not looking for just any out param, it's looking for a cursor in the out param from the stored procedure.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Statey603]

Hi Dell,

I changed my stored procedure to return a ref cursor and that took care of that issue. Thanks.

I am still having a problem trying to have my Stored Procedure update the database with a Last Run Date. I receive error: may not perform insert/delete/update operation inside a READ ONLY transaction.

I tried added SET TRANSACTION READ WRITE; prior to the UPDATE statement but I receive an error indicating SET TRANSACTION must be the first statement of the transaction. It is !!!

CREATE OR REPLACE PROCEDURE EASDEV."TMP_UPDT_LAST_RUN_PROC"
(InReportName IN varchar2,
p_recordset OUT SYS_REFCURSOR)
is
begin

set transaction read write;

update options_last_run_tbl set
lst_run_dt = sysdate
where process_nme = InReportName;
commit;

OPEN p_recordset FOR
SELECT LST_RUN_DT, PROCESS_NME
FROM OPTIONS_LAST_RUN_TBL
WHERE process_nme = InReportName;

exception when others then

raise;

end;

 

[hilfy]

That I'm not sure about. Does the account you're using to log in to the database have access to add/update data? Or is it a read-only account?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Statey603]

The account has SELECT and UPDATE privilege for table OPTIONS_LAST_RUN_TBL and EXECUTE privilege for Stored Procedure TMP_UPDT_LAST_RUN_PROC.
I have also confirmed that the ODBC Data Source setting for Read Only Connection is NOT checked for this connection.

It seems that someone using Crystal Reports must have done something similar to this - keeping track of report last run date/time in a db table.

thanks.

 

[hilfy]

I did some searching on this issue on SCN (scn.sap.com). All of the information I could find said that the update has to occur through a Command. Normally in Crystal, a Command is just a SQL Select statement, but you can put an update statment before the select. Based on your code above, it might look something like this:


update options_last_run_tbl set
lst_run_dt = sysdate
where process_nme = '{?InReportName}';
SELECT LST_RUN_DT, PROCESS_NME
FROM OPTIONS_LAST_RUN_TBL
WHERE process_nme = '{?InReportName}';

You have to create the parameter in the Command Editor - it won't work if you create it in the main report.

Try this and see if it makes a difference for you.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Statey603]

Dell,


I tried what you suggested but cannot get it to run without Crystal displaying an Invalid Character error.

UPDATE OPTIONS_LAST_RUN_TBL
SET LST_RUN_DT = SYSDATE
WHERE PROCESS_NME = {?InReportName};
COMMIT;
SELECT LST_RUN_DT, PROCESS_NME
FROM OPTIONS_LAST_RUN_TBL
WHERE process_nme = {?InReportName};

I tried a number of code variations. If I remove the UPDATE/COMMIT, then the SELECT code runs, but that puts me back where I started - unable to update a database field. I have confirmed that the code (above) executes in my TOAD editor. I can't believe that Crystal does not support UPDATES.

 

[hilfy]

You have to put quotes around {?InReportName} - Crystal doesn't automatically do that for string parameters in a command.

Also, Crystal was designed to pull data out of the database for reporting. It was never meant for updating data back to the db and this technique is not "officially" supported by SAP.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Statey603]

Hi Dell,

I added the parameter field to my SQL by double-clicking on it (from the parameter list). It works as shown as long as I remove the UPDATE statement. I think you are right about Crystal not supporting updating of data, although a salesperson from Crystal told us we would have that capability when we were deciding on reporitn applications. I really don't understand why they would out and out not allow it. We have our database set so the only table the user has UPDATE capability for is the Last Run table and the only Procedure they have access to on to perform an update on this table. I get that it is safer to not allow users write/update capabilities, but we have full control. They are not even allowed to modify reports - everything is 'canned'. So we have no risk. I just don't understand that Crystal doesn't at least provide an option to override Read-only and allow updates if the designer so choses. If the DB user does not have the privilege, it won't be allowed. LEave the DB admin to the DBAs. I guess we are going to have to get in touch with our sales person and find out what the heck they sold us.

Thanks,
Bill

 

[hilfy]

Does it work if you do the update without the commit?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Statey603]

No. I tried having only my UPDATE statement in the SQL command and I get the read only trasaction error.

If I try multiple SQL statements - each terminated by semicolon as Oracle requires, I get the invalid character and I read somewhere that it is because of the semi-colon.

 

[hilfy]

Try formatting is as an anonymous PL/SQL Block by surrounding the whole thing with Begin...End

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Statey603]

No.
If I only have the UPDATE statement, I receive the READ ONLY Transaction error.
If I try multiple SQL statements each terminated by semi-colon as required in Oracle, then I get Invalid Character.
If I remove the semi-colons, I still get Invalid Character.

thanks for caring to ask.

 

[TeyBrady]

Here is how I report off Oracle Stored procedures. Hopefully it will work for you too. In my example below you should be able to slap your SQL in the stored procedure like I explain to do it below. I recommend that you run a report just like the one that I talk about below to prove that it works and then you can make it do what you want it to do.

I have a crystal report that i run on a schedule that has a stored procedure in it and the purpose of running the report is so it will add seven days to a date in an oracle table. In the stored procedure I run the update and then the open statement that returns data to the report. I never look at the report because all I care is that it runs and adds seven days to the date.

-------------------


Create this magic package one time for all crystal reports that use stored procedures.
[tt]CREATE OR REPLACE
PACKAGE Rpt_Cursor
AS
TYPE RptCur IS REF CURSOR;
END Rpt_Cursor;[/tt]

The Stored procedure uses the first parameter for crystal to return the data. The second is an example of how you would set up a parameters if you have any. This SP is adding the number of days to the current date and returning it which should work as a test for anyone. You will want to add in your select statement to the Open line.

[tt]CREATE OR REPLACE
PROCEDURE sp_test(reportcursor IN OUT Rpt_Cursor.RptCur,
p_days in number)
AS

BEGIN

Open reportcursor for select sysdate + p_days as days from dual;

END;[/tt]

From my experience you can do pretty much whatever you want before the open statement. If you need to use a stored procedure it is likely that you will have code above the open statement. If you don't you might want to consider using a command object instead of a stored procedure.

 

[Statey603]

Hi TeyBrady,

I appreciate your taking the time to post.

Is the stored Procedure inside the package or are they separate entities?

Thanks,
Bill

 

[TeyBrady]

They are separate. You will need to create the package once and then never touch it again. The stored procedure refers to the package. You could write a second stored procedure for another report but you would not need to write a second package. You do need to make sure your stored procedure looks a lot like mine. For example my p_days will be your InReportName. Make sure you change the type from number in your stored procedure. Above the select you can add in your update. Make sure you can do my simple example that adds some days to the current date to see that it works and then make it work for you.

Good luck!

 

[Statey603]

OK. I think I get it. Yes. I will keep it simple at first (as you describe) and then try my implementation. I am working on this right now although I have to leave the office shortly. I will reply tomorrow with results.

Thank you very much.
- Bill

 

[Statey603]

TeyBrady,

I set up what you suggested but still get the same behavior when I implement the code in sp_test() to call my update stored proc. When I attempt to create a Crystal report, if sp_test() calls a proc that performs a database update, Crystal displays the following error and will not allow me to save the Command. [ORA-1456:may not perform insert/delete/update operation inside a READ only transaction]
I have tried it 2 ways.

Below is my sp_test() proc. I can only successfully call it in Crystal if I comment out the call to UPDATE_LAST_RUN_PROC() which pretty much defeats the purpose.

CREATE OR REPLACE PROCEDURE EASDEV.sp_test(reportcursor IN OUT Rpt_Cursor.RptCur, p_days in number)
AS
BEGIN

UPDATE_LAST_RUN_PROC('SERV_AUTH_V'); -- <-- my stored proc to update last run tbl
Open reportcursor for select sysdate + p_days as days from dual;

END;
/

I attempted setting up the Crystal Rpt the following 2 ways - each ending with the same error 9listed above).

1. Direct call to sp_test()
Select sp_test() as data source browsing database objects via Crystal's Database Expert explorer.

2. Call sp_test() from Command.
Pasted the following SQL code in Command object.
DECLARE
MyRptCur SYS_REFCURSOR;
BEGIN
sp_test(MyRptCur, 6);
END;

Perhaps I did not ask the original question correctly....bottom line is I am trying to execute an UPDATE statement from Crystal.

 

[TeyBrady]

I think the problem is that you are calling a stored procedure to do the update. I meant that you could have an update statement in the stored proc for the report. I jsut made this change to my stored procedure and tested it. I have a table called fatigue_date with one column called end_date and only one record in it. So my statement will add 7 days to the date in the table every time the crystal report is retrieved.

[tt]CREATE OR REPLACE
PROCEDURE sp_test(reportcursor IN OUT Rpt_Cursor.RptCur,
p_days in number)
AS

BEGIN

update fatigue_date
set end_date = end_date + 7;
commit;

Open reportcursor for select sysdate + p_days as days from dual;

END;[/tt]


I checked the original stored procedure I was referring to and I have table deletes in it as well as substatial cursors that read through data and insert rows in a table. Then at the end I do the update I mentioned earlier. Finally I return the data to the report to make crystal happy.

Try replacing your stored procedure call with an upate statement to see if it works. If the update is complex bring all of that logic from the stored procedure you were trying to call into this stored procedure and try it.

 

[Statey603]

I have tried a direct in-line UPDATE statement instead of calling the stored procedure (similar to your example but using my db tables), but get the same error message. I have reconfirmed that the user has the proper privilege and that I can execute the sp_test() procedure when logged in as this user using TOAD for Oracle.

I really am thinking that this is a Crystal configuration issue. Somehow my Crystal is set for READ Only Transactions.
I found a post on the SAP forum [by Richard Toy] that appears similar my issue but the registry keys are not in my registry. [

http://scn.sap.com/thread/2079166

].
It discusses changing registry key HKEY_CURRENT_USER\SOFTWARE\BUSINESS OBJECTS\SUITE11.0\CRYSTAL REPORTS\DATABASE\ODBC to 0 in CR2011. I do not have this key. I tried adding it but it still gives me the READ only transaction error.

I am on CR2011 version 14.
What Crystal Rpts version are you on
and
do you know if you have this registry key or if there is a way to configure Read/Write transaction?
NOTE: I have confirmed that the ODBC Driver Configuration for my database connection has Read Only connection NOT checked.