Oracle stored procedure does not work

[hempsh]

I am trying to use an oracle stored procedure in a crystal report, using Oracle 8.1.7.00 and Crystal 8.5. The code for the procedure and related objects are shown below and were created in the its1 schema. I am trying to run the report logged on as user reports. I get different results depending on the driver I select, but not any are successful.

a) using a Oracle 8.01.73.00 ODBC driver logged in as reports,
I get the following error:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.
(the properties for the ODBC driver has 'enable result sets' checked and 'read only conection' unchecked)

b) using the CR Oracle8 ODBC driver logged in as its1 (if logged in as reports with the driver property set to show procedures from other schemas, its locks up), I get thousands of records returned and I stop run, because I should only be getting back 8 records.

c) using the oracle native driver logged in as reports, I get back thousand of records like case (b) above.

I have tested this procedure in sqlplus and it returned the 8 rows expected. I tested the procedure using sqlcon32 (odbc tool from crystal site) using the driver in case (a) above and it worked fine, but using the driver in case (b)
above I got the same result as when I tried it in crystal.

Any ideas why I am having a problem?


1.
CREATE GLOBAL TEMPORARY TABLE MC_TMP_TEST
(
SRVNO VARCHAR2(9),
SRVKEY NUMBER(9),
PART_COST NUMBER(11,2),
LABR_COST NUMBER(11,2)
)
ON COMMIT PRESERVE ROWS;
GRANT DELETE, INSERT, SELECT, UPDATE ON MC_TMP_TEST TO REPORTS;

2.
CREATE OR REPLACE package test_package
as type test_type is ref cursor return mc_tmp_test%rowtype;
end test_package;
GRANT EXECUTE ON TEST_PACKAGE TO REPORTS;

3.
CREATE OR REPLACE PROCEDURE Test_Procedure (
Test_Cursor IN OUT its1.Test_Package.Test_Type)
AS
BEGIN
INSERT INTO IMSV7.MC_TMP_TEST
(SELECT srvno, srvkey, 0, 0 FROM ITS1.HISTORY WHERE COMPDTTM > '01-JUN-2004');
open Test_Cursor for
select * from its1.mc_tmp_test;
END ;
GRANT EXECUTE ON TEST_PROCEDURE TO REPORTS;

 

[dtempleton]

Try connecting to Oracle using the CR Oracle8 v3.6 driver.

 

[hempsh]

Actually, I did use the CR Oracle8 v3.6 driver in case (b), not the CR Oracle8 driver as stated.

 

[lyanch]

I am not sure what you use the temp table for?.. I would just insert into the cursor:

3.
CREATE OR REPLACE PROCEDURE Test_Procedure (
Test_Cursor IN OUT its1.Test_Package.Test_Type)
AS
BEGIN
open Test_Cursor for
(SELECT srvno,
srvkey,
0 as Part_Cost,
0 as LABR_COST
FROM ITS1.HISTORY WHERE COMPDTTM > '01-JUN-2004');
END ;
GRANT EXECUTE ON TEST_PROCEDURE TO REPORTS;
Hope this helps

Lisa

 

[hempsh]

I created a simple procedure, just to test the concept of creating a temporary table and then being able to return the records from the temp table to crystal. In reality, I would only use this in a procedure that was more complex, involving more tables and updating the data in the temp table before returning the recordset to crystal.

 

[lyanch]

Ah... I usually set the temp table to "On commit delete rows" but just to be safe I delete everything from the temp table before I use it.

ie.. first command in output proc would be:

delete from IMSV7.MC_TMP_TEST

I also dropped the row stuff from the package creation.. it just seemed to work better, here is mine:

PACKAGE Report_Package
IS TYPE Report_Type IS REF CURSOR;
END Report_Package;


There is a white paper, which looks like you have read.. hope this helps.

Lisa

 

[hempsh]

Lisa, thanks for the suggestions. I have not been able to get it to work. Have you done stored procedures using a temporary table and returned results to crystal? What driver did you use? Would you be willing to share a sample?

 

[lyanch]

I can.. stripped down anyway, mine are for oracle 9i, using cr 9 (although started on 8,5)

Package as above:

A Temp table: (procedure uses three total)

/*==============================================================*/

/* Table: RSP_SSRCapacity_Dates */

/*==============================================================*/
DROP TABLE RSP_SSRCAPACITY_DATES CASCADE CONSTRAINTS ;

create global temporary table RSP_SSRCapacity_Dates (
ChangeDate Date,
TotalCap Varchar2(48),
PriStart int,
PriEnd int
)

on commit delete rows;

MAin stored proc:


CREATE OR REPLACE PROCEDURE RSP_SSRCapacity (
SSRCapacity IN OUT Report_Package.Report_Type,
StartDate in date,
EndDate in date,
PriStart1 in int := 999,
PriStart2 in int := 999,
PriStart3 in int := 999,
PriStart4 in int := 999,
PriStart5 in int := 999,
PriStart6 in int := 999,
PriStart7 in int := 999)

IS

IncTime date := trunc(StartDate);

TotCap varchar2(48);

UseEndDate date;

BEGIN

delete from RSP_SSR_Used;
delete from RSP_SSRCapacity_Dates;
delete from RSP_BW_Used;
-- bunch of code***
-- filling temp table I showed you
While IncTime <= (trunc(UseEndDate) + interval '1' Day - interval '1' second) loop



insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, 0, nvl(PriStart1,256) - 1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart1, nvl(PriStart2,256) -1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart2, nvl(PriStart3,256) -1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart3, nvl(PriStart4,256) -1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart4, nvl(PriStart5,256) -1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart5, nvl(PriStart6,256) -1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart6, nvl(PriStart7,256) -1);

insert into RSP_SSRCapacity_Dates values(trunc(IncTime,'MI'), TotCap, PriStart7, 999);

IncTime := IncTime + interval '60' Second;

end loop;

-- bunch more code

-- load cursor
OPEN SSRCapacity FOR
select ChangeDate,
(select sum(Used) from RSP_SSR_Used rs2 where rs2.ChangeDate <= rs1.ChangeDate and rs2.PRIORITY between rs1.PriStart and rs1.PriEnd) as Used,
(select sum(Used) from RSP_BW_Use rs2 where rs2.ChangeDate <= rs1.ChangeDate) as BWAvail,
TotalCap,
PriStart,
PriEnd
from RSP_SSRCapacity_Dates rs1
where rs1.PriStart <> 999;
END;

/


Hope this helps. One thing.. I have never used "select *", try actually selecting the columns..

That is all I can think of..

 

[hempsh]

Lisa, thank you for the sample code and your suggestions. I found if I changed the select used to insert rows in the temporary table so that it was not based on a date field, it worked. If I converted the date in the select to a text compare by using a TO_CHAR function, it also worked. It doesn't make any sense that the date compare does not work when called from crystal, but apparently it doesn't.