Solved

ORA-01456, Can't perform update/insert etc in Stored Procedure from Crystal Reports

Posted on 2010-08-25
30
2,071 Views
Last Modified: 2012-05-10
Okay, I have a Crystal Report which I want to link to a Stored Procedure.   This stored procedure uses temporary tables within it.  It inserts/updates/deletes etc etc.  

When I try to connect to the stored procedure in Crystal Reports I get the following error message:

Query Engine Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

Seems pretty crazy that you can't make any changes to tables if calling a stored procedure from Crystal, so there must be something wrong here.  Is it purely because it's in a read only transaction?  How am I in a read only transaction?  Where/how can I change that? etc etc
0
Comment
Question by:obrienj
  • 19
  • 11
30 Comments
 

Author Comment

by:obrienj
ID: 33519896
Okay, strange.  I realised I wasn't selecting any output data at the end.  So I added that functionality (using a ref curser)
I didn't think it would make a difference to the problem above, but now I'm getting a different error message, so perhaps it has.

Now I'm getting an even stranger message:
Query Engine Error: 'Cannot obtain error message from server.'
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520010

Can you execute the stored Procedure from your database, eg Mysql
CALL `yourprocedure`() with exactly the same parameters, that should give you the actual error in mysql,

What SQL database are you using?>
0
 

Author Comment

by:obrienj
ID: 33520018
I can execute it fine within Oracle... using Toad.
0
 

Author Comment

by:obrienj
ID: 33520053
btw, you mentioned using exactly the same parameters.... this makes me think you might misunderstand where the error is coming up.

It is occurring within Database Expert when trying to ADD the Stored Procedure to the Crystal Report.
When it asks for Parameters I am leaving Set to Null value checked as I dont have any parameters at that time.  I believe this is the correct way to do it.   Then within the report I should be able to pass the parameters later (haven't got there yet, and have never used a SP within Crystal before)
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520122

I run SP with crystals all the time, With Mysql and i have no dramas, I think your problem could be that, I don't believe Crystal command allows (DML) activities such as INSERT, UPDATE and DELETE



0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520224

can you try creating a simple stored procedure, that doesn't Insert, update or delete, anything, but just selects and see if you get the same error, Or if the procedure works in crystals.
0
 

Author Comment

by:obrienj
ID: 33520417
Okay, I don't know whats going on now.  I removed all insert/update etc statements and still got the same message.

Query Engine Error: 'Cannot obtain error message from server.

I then went back and set everything the way it was when I got the first message and still the same.  I'll have to try sort this out, I'll be back in a while.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520449

I have gotten this error before, I would just start with making a simpe stored procedure just, select like 1 field in a SP and see if you can get it to work,

Okay, no probs, good luck
0
 

Author Comment

by:obrienj
ID: 33520673
Okay, I have a simple one working.  Returns an existing table.... ill start playing around with it now and see how I get on
0
 

Author Comment

by:obrienj
ID: 33520776
Okay, I built it up slowly.  Added the rest of the parameters, still worked fine.  The minute I added a select into statement it failed.

"SELECT SLABEL INTO SSTARTOBJECT FROM FOBJ WHERE LOBJID = LSTARTID AND LOBJCLAID = LSTARTCLAID;"

Once I added the above statement I got the error:
Query Engine Error: 'Cannot obtain error message from server.'
0
 

Author Comment

by:obrienj
ID: 33520794
I removed the select into (I don't really need it) and put in the first insert statement.  It failed again, with the original error message in the first post.
0
 

Author Comment

by:obrienj
ID: 33520795
NSERT INTO SCRPATH (SDMTREPORTID,LPATHNO,LPATHOBJID)
            Select sDmtRepID, ROWNUM, lObj2ID from fhie where lObj1ID = lStartID and lObj1ClaID = lStartClaID and lObj2ClaID = lStartClaID;


Also note, SCRPATH is a Global Temporary Table.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520840

Well if you ahve tested and stored Prcodeures work fine for you, and as expected with just selects, but fails when trying to INSERT this kinda proves my thinking that you can't do any data manipulation with in crystals using stored Procedures. I don't think Crystals allows Update, Insert or delete.

Can you create the Temp table in your DB manually, then call it through your stored procedure from Crystals?
 
0
 

Author Comment

by:obrienj
ID: 33520938
em, that would be pretty nuts..... surely it cant possibly have a restriction like that???  Would make the functionality of calling stored procedures pretty redundant.


Found this on the internet:

8. If using an ODBC driver, it must be the CR Oracle ODBC driver (installed
by CR). Other Oracle ODBC drivers (installed by Microsoft or Oracle)
may not function correctly.

I don't have CR Oracle ODBC driver installed, ill see if I can find it.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520965

Ahh, wierd, but you can use stored Procedures with your current ODBC connection, You proved that already !

Anyway, Let me know how you go ;)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:obrienj
ID: 33521074
Ahhhh, this is stupid.

I found, on another help site, someone looking for an answer to the same problem.  It turned out it was the ODBC driver.  He switch to a Native Oracle Driver and it worked fine.  Just checked it there and it works.

The problem for me is that I can't make use of that.  CR is a 3rd party application to my program.  My program uses ODBC.

I'll have to find/try that CR ODBC Oracle driver, hopefully that will help.


At least I know it is possible..... thank god CR aren't that mad as to limit the ability totally
0
 

Author Comment

by:obrienj
ID: 33521079
ps:  If anyone knows where to find the CR Oracle ODBC driver I would be grateful.
0
 

Author Comment

by:obrienj
ID: 33521135
okay, I found it..... fingers crossed :)
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33521166
Okay sweet,  good luck mate ;)
0
 

Author Comment

by:obrienj
ID: 33521181
oh ffs.... completely different error message now.

pls-00306: wrong number of types of arguments in call to 'CR_EMPATHSTEPS'


:(
0
 

Author Comment

by:obrienj
ID: 33521232
HOLD THE PHONE!!!

I just had a play around, and maybe.... just maybe.... my application will actually allow me to connect with the direct native driver.....

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33521240

Crystal reports strikes again, i completely get your frustration, Crystals is extreemly trying at the best of times.
Maybe google that error, still, the last connector ran with simple stored procedure, you tested that. So it's wierd.

Are you adding the stored procedure from the tree, in database expert? Does the SP have any parameters?

0
 

Author Comment

by:obrienj
ID: 33521317
Okay, it appears to be allowing it, but cant figure out where to connect my parameters.

Yes, I am adding the stored procedure in the tree in database export.  The SP has three parameters.
I read somewhere else that I should leave those set to null and define them in the report (i.e. select them from report fields), but I can't find how to do that.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33521544
Theres a few ways to do itm depends what your parameters are?

In the Database expert, you could Go 'Add Command'

put your Call in Add command

And make 3 paramters in the left had Panel
{?PARAMETER1}
{?PARAMETER2}
{?PARAMETER3}

Then when you refresh, just fill in your parameters,

OR else you can do it through Crystals, by Adding a 'New Parameter' and putting in your parameter field as the default, the select Expert, and append your parameter field to The 'New Parameter'

Then refresh the report and enter what you need to pass to the parameter



0
 
LVL 8

Expert Comment

by:kingjely
ID: 33521574

put your Call in Add command, something like this, and add the paramters into the Parameters Panel
CALL `My PROC`("{?PARAMETER1}","{?PARAMETER}","{?PARAMETER3}");

0
 

Author Comment

by:obrienj
ID: 33521771
em, the parameters that I want to pass in.  They will be coming from the report itself.  i.e. parameters passed to the report and/or other fields etc.
Please tell me that's possible?  doesn't seem to be from the Add Command area.
0
 

Author Comment

by:obrienj
ID: 33521958
If I add it like a table as before, it adds the Parameters as parameters of the report itself.  I should be able to get it from here using a sub-report.
0
 
LVL 8

Accepted Solution

by:
kingjely earned 500 total points
ID: 33521998

Yea no worries, It will just be a matter of playing around with which way to set up the paramters will work best . Glad you got it sorted ;)

Kj
0
 

Author Comment

by:obrienj
ID: 33523080
Okay, I'm up and running.

Thanks for the help.  I know you didnt get the answer for me, but you tried at least.... ill reward the points.
0
 

Author Closing Comment

by:obrienj
ID: 33523108
Found solution myself.  Used Native Oracle Driver and it worked.  
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now