Problem Accessing Stored Oracle Procedure 1

[keith818]

I have created a stored procedure in Oracle that contains an ‘INSERT’ statement in it which inserts values to a table which are then returned by a cursor variable to Crystal Reports using a ‘OPEN [cursor] FOR’ statement. The problem is that when I try to add this procedure to the report I get the following error:

ODBC error:[Oracle][ODBC][Ora]ORA-01456:may not perform insert/delete.update inside a READ ONLY transaction

If I take the ‘INSERT’ statement out the procedure works fine.

Instead of storing the values computed by the procedure in a table I have also tried to store them in a PL/SQL table but do not know how to return this table as a cursor variable.

Can any one help me?

Thanks

Keith

 

[troyboy]

Try doing the insert in a seperate cursor to the one being referenced by Crystal. The stored procedure can contain more than one cursor.

 

[keith818]

Thanks for your help, unfortunatly I dont think that'll work as the insert dosent use a cursor, but just inserts directly into the Database.

The INSERT is in the main body of the procedure in a for loop and is as follows:

INSERT INTO AGED_DEBTS VALUES
(EVENT_SEQ, DEBT_REF, SYSDATE - EVENT_DATE, REC_STATUS);

While the cursor which is returned is:
OPEN TEST_CUR FOR
SELECT DEH.EVENT_SEQ, DEH.DEBT_REF,
SYSDATE - DEH.EVENT_DATE, DEH.REC_STATUS,
DI.ORIGINATING_DEPT
FROM DEBT_EVENT_HISTORY DEH, AGED_DEBTS AD
WHERE DEH.EVENT_SEQ = AD.EVENT_SEQ

 

[troyboy]

Ok, how about putting the insert within a procedure.
procedure insert_rec_urg IS

begin
for i in 1..4 loop
insert into ....
end loop
end

insert_rec_urg;

Another alternative is to use the following format:

v_cursor1 := dbms_sql.open_cursor;
begin
v_sqlstatement :='Delete from table';
v_sqlstatement := v_sqlstatement || ' where create_date < sysdate ';
dbms_sql.parse(v_cursor1,v_sqlstatement,dbms_sql.v7);
v_returncode:=dbms_sql.execute(v_cursor1);
end;
dbms_sql.close_cursor(v_cursor1);

open test_cur...

 

[synapsevampire]

I created an Oracle SP which did an insert and then returns data, so I'm not sure why you're getting this error.

Unfortunately I don't have that setup here, perhaps it's driver related.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[keith818]

Again, thanks or your help. Unfortunatly that still hasnt fixed the problem.

The first way dosent work because your not allowed to call another stored procedure from the orginating procedure.

When I tried the second option I still got the same error. I think it must also prevent you from using dynamic SQL statements.

 

[troyboy]

Keith,

The attached do work, honest. It took a few days of slog but I needed to do a similar thing. The procedure is not a seperate stored procedure but a procedure within the stored procedure. I don't understand either, I much prefer SQL Server.
The only other thing to check is that you are using the Crystal supplied Oracle Driver rather than an ODBC connection.
The only other variant I can think of is versions of Oracle, I was using version 8.06.

 

[keith818]

I can call both those procedures form another SQL statement they work fine, I only have the problem when I try to import the stored procedure into Crystal Reports. I am using the Windows 2000 ODBC driver v8.01.76.00 and am calling a 7.32 Oracle Database.

 

[troyboy]

Keith, there is a knowledge base article on the Crystal Decisions site entitled Oracle stored procedures and crystal reports. There is a table that suggests that you should use the native oracle driver (p2sora.dll) for the interface to work with Oracle 7.x. I used this method rather than the odbc driver supplied by crystal as I couldn't find a way of deploying the odbc driver.

 

[synapsevampire]

Ahhh, I hadn't tried dynamic SQL, but troyboy is absolutely right, use the connectivity suggested by CD, it's VERY flakey otherwise.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[keith818]

Instead of using the ODBC driver I used the native oracle driver and it worked! Cheers for all the help.

Keith