Oracle 9i, Crystal 9, Stored Proc issues...

[nodwag]

It's been about 4 years since I've worked with Oracle on a daily basis, having joined a company that works almost solely with SQL Server. It took quite a while to get weened off of Oracle, but now I'm experiencing the opposite on a side project where I'm working with it again.

What I want to do is:
Create a Stored Proc that will gather data from a number of tables, manipulate it in a Global Temp Table, and then return the result to Crystal

Temp Table Def:
CREATE GLOBAL TEMPORARY TABLE temp_test
(
vco VARCHAR2(32),
reportstotx VARCHAR2(32),
funcrole VARCHAR2(64)
) ON COMMIT PRESERVE ROWS;

Package Def:
CREATE OR REPLACE PACKAGE gap_test2_pack
AS TYPE gap_return IS REF CURSOR RETURN temp_test%ROWTYPE;
END gap_test2_pack;

Stored Proc Def:
CREATE OR REPLACE PROCEDURE gap_test2_proc (rc_query IN OUT gap_test2_pack.gap_return)
IS
BEGIN

INSERT INTO temp_test(vco,reportsto,funcrole)
SELECT trinametx, trireportstotx, trifunctionalrolecl
FROM t_tripeople
ORDER BY trinametx;

COMMIT;

OPEN rc_query FOR
SELECT *
FROM temp_test;

END gap_test2_proc;

When I attempt to associate the Stored Proc with a report in Crystal, I get the following error:

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

Using the Oracle 9.02.00.0 ODBC driver.

Everything appears to compile fine in Oracle (using TOAD to verify), so is this an issue with my Stored Proc syntax, an ODBC issue, or a Crystal issue?

I'm just used to working with SQL Server now where I can do all my transactional SQL code, piling stuff into a temp table and then just query on that at the end and return that result to Crystal.

Any help would be greatly appreciated...

Thanks,
Don



DGaw
Independent Consultant
Seattle, WA

 

[nodwag]

Well... after banging my head against many walls for far too long, I may have answered my own question. Sadly, it was only after posting this question to the group.

Turns out, if I create a Native Oracle connection instead of an ODBC connection, I do not get the error described above, and I can manipulate the data in the global temp table as desired and return the result set, all sorts of fine, to Crystal.

However, should anyone have any suggestions of what to do for an ODBC driver, just for kicks, let me know.

In any case, I believe I have the work around I need.

Thanks,
Don

DGaw
Independent Consultant
Seattle, WA

 

[synapsevampire]

You're using the Oracle ODBC driver which isn't supported when you should be using the Crystal supplied Oracle ODBC driver.

But stick to the native connectivity, it's faster anyway.

If you do decide to go with the Crystal supplied Oracle ODBC driver, make sure you set the option of Procedure Returns Results in the ODBC setup.

-k

 

[nodwag]

Thanks!

I think the "Procedure Returns Results" option was my initial issue. On my box, everything ran fine with the Oracle ODBC driver. On the production box, it wouldn't work at all, and that had the Crystal driver on it. Hence, my conclusion was that the Crystal driver was tweaky.

Looks like I'll be fine to stick native, so I'm happy.

Thanks, again.

D

DGaw
Independent Consultant
Seattle, WA