Crystal Reports XI - Using an oracle procedure via cursor fails

[DelSenato]

I am writing some reports on Crystal XI on an Oracle 10g database.

I have implemented the ref cursor and written a procedure to populate a table from an identical table in another schema.

The problem I have is that when using the native oracle connection (with tnsnames) the proc deletes the data from the table but does not repopulate it (and it should always delete). If I change the connection via Set Datasource Location and make the d/b type OLE DB (ADO) the proc works fine.

My questions(s) are :

Native mode - Is this via TNSNAMES or CRDB_ORACLE.DLL?

Apologies if that seems a stupid question but native to what? I thought it was native to the d/b so would therefore be TNSNAMES but

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

suggests otherwise when it says NATIVE is via CRDB.

OLE DB (ADO) - Is this not now superceded by RDO or is it the other way about.

Again, apologies if this seems stupid but I find the documentation very vague.



The proc is as follows

CREATE OR REPLACE PROCEDURE a_POP (EXCEPT_CUR IN OUT a.PKG_CRYSTAL_REPORTS.CUR) IS
V_COUNTER NUMBER(6);

BEGIN
DELETE FROM a.AL_F_000MAF_TMP;
COMMIT;
BEGIN
SELECT COUNT(*) INTO V_COUNTER FROM a.AL_F_000MAF_TMP;
END;
IF V_COUNTER = 0 THEN
INSERT INTO a.AL_F_000MAF_TMP
SELECT * FROM Ac.C_DIRECT_DEBIT
WHERE VALUE_DATE = TO_DATE('22-AUG-2006');
END IF;
COMMIT;
begin

OPEN EXCEPT_CUR FOR
SELECT * FROM a.AL_F_000MAF_TMP;
end;

END a_POP;
/


Any help would be muchly appreciated.

 

[synapsevampire]

Native means that it is a proprietary Crystal connectivity.

I find various issues when using each type of connectivity, if you find one that works for your SP, you're better off than some...

-k

 

[DelSenato]

Thanks for that Kai, certainly explains a few things.....


Mick.