Insert row into table from cr9

[jambu]

I am using Oracle 9 and CR9 and would like to be able to insert a row into a table each time the report is run.

I have a simple insert statement (that works in sql*plus and TOAD using the same login as my ODBC connection) in the sql editor that comes up in CR9 when you select 'Add command'. When I try and save the query I get the following error.

Failed to open a rowset.
Details:HY000:[Oracle][ODBC][ORA]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.

Is this caused by Crystal only allowing transactions to be Read Only?
If so is there any way to switch this off/change to a READ/WRITE transaction?

Is this by Oracle somehow? I can't see how because sql*plus and TOAD can run the insert fine and they use the same ODBC driver and user.

 

[GJParker]

the command must always end up with a slect statment to return a recordset to the report.

The following link should help you to accomplish this task.

http://support.businessobjects.com/library/kbase/articles/c2011921.asp

Gary Parker
MIS Data Analyst
Manchester, England

 

[jambu]

Thanks for that, I forgot to mention this also happens if I have a select after the insert.

Any Oracle reporting gurus know why this wouldn't work? If its a case of permissions I can get them changed but I am not convinced.

my statement looks like this and the insert works in sql*plus.

insert into MYTABLE values('Text',1,2,3,'More Text')
select * from MYTABLE

 

[synapsevampire]

It looks like you're using the Oracle ODBC driver, which generally causes problems.

Try using the Oracle Native connectivity (fastest) or the Crystal supplied ODBC.

Otherwise it might be the permissions, and you might even try going directly against the database rather than using Add Command.

I seem to recall some issues with using an SP on Oracle for updates, but I rarely use SP's these days, and remember that SPs MUST be built according to Crystal's requirements:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_oracle_stored_procedures.pdf.asp

-k

 

[jambu]

Thanks, that looks promising.

 

[jambu]

Nope, still getting this error...
Failed to open a rowset.
Details:HY000:[Oracle][ODBC][ORA]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.

And I tried using the CR 4.10 drivers as well as the ora9 ODBC drivers.

The example worked fine until I tried to add an insert into the sp, here is the code I tried.

CREATE OR REPLACE PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE)
AS
BEGIN
INSERT INTO Test_Table VALUES
(Test_Parameter, 'xx', 'xxx', '01-Nov-70');
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table
WHERE Test_Table.ID = Test_Parameter;
END Test_Procedure;

Any further suggestions?

 

[jambu]

Oh and this runs successfully in sql*plus and inserts the line.

 

[nayaseed]

Mr.Jambu,

I am having the same problem.And there is a solution for this.
Try using Oracle Native driver not the Oracle odbc.
Install Oracle client tools and it will install the native driver
You can access this in crystal as NewConnection>OracleServer..Provide Hostname ,username and password.
Now everthing will work fine, whatever works in SQL PLUS should work.
Because even SQL Plus connects to database in the sameway.

But i still have this problem because my company does not support oracle native drivers it just supports oracle odbc...so iam still stuck...can any one help me.