How to get a simple Oracle PL/SQL block to be run in a Crystal Report's (I'm using version XI) Command Object. That is some thing like below should get executed and return a resultset to Crystal...

declare
<some variables>
begin
<some code>
<a SELECT statement>
end;

I know how to get this working using a Stored Procedure using a REF CURSOR. But, my purpose does not solve just with a Stored Procedure. Perhaps, I need to call a Stored Procedure in such PL/SQL block as mentioned below: (If the below block is formatted to be in separate lines, it throws some error. And it only works if it is all in one line... strange!)

-------------------------------------------------------------
declare tmpId number; TYPE tmpRefCursor IS REF CURSOR RETURN tempTable%ROWTYPE; tmpCursor tmpRefCursor; begin spTest1(tmpId); spTest2(tmpCursor); spTest3(tmpId); end;
-------------------------------------------------------------

In the above block, the Stored Procedures spTest1 and spTest3 do some pre and post sanity work and spTest2 is the one which should return some records to crystal. Actually, spTest1 returns tmpId (OUT Parameter) and the same tmpId is passed to spTest3 (as IN parameter). spTest2 has a REF CURSOR parameter (as IN OUT).

For me, the above block runs successfully and I can see that spTest1 and spTest3 gets executed as expected. But Crystal Report is blank as for some reason the records are not returned to the Crystal. I have tried with Native drivers vs Data Direct 5.0. Data Direct always gives some error. Native Drivers run fine but Records are not returned.

Same such block was perfectly working if it was Sybase. But I could not get this working in Oracle.


I'd greatly appreciate any help to get this working.