Oracle SP has different result in SQL developer and CR XI

[thiagarr]

Hi all,

I am using CR XI, Oracle 9.2. I am trying the SP in CR using CR Oracle driver 5.1 (with Oracle native driver, I was not able to get this far).

When I run the SP in SQLdeveloper, I get the desired / correct values in the Table. But when I run the SP in CR, I get zero for my values. DECODE or CASE in the function did not make any difference in either CR or SQLdeveloper. I could not get the IF THEN ELSE to work in the function.

Any ideas what have I done wrong here? Any help would be appreciated.

Package:

create or replace PACKAGE TEST_REUSE_PACKAGE
AS TYPE Test_Type IS REF CURSOR RETURN ALL_USERS_REUSE%ROWTYPE;
END TEST_REUSE_PACKAGE;


Stored Procedure SP_REFCURSOR_REUSE:

CREATE OR REPLACE
PROCEDURE SP_REFCURSOR_REUSE (
Test_Cursor IN OUT Test_REUSE_Package.Test_Type,
Test_Parameter1 IN ALL_USERS_REUSE.sequence%TYPE,
Test_Parameter2 IN ALL_USERS_REUSE.sequence%TYPE)
AS

STATISTICS1 Number;
currentEndDate DATE;
BEGIN
delete from ALL_USERS_REUSE;
currentEndDate := SYSDATE;
-- change the number next to sysdate to run report for earlier months.  -1 is for previous month
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-2)), 'DD-MON-YYYY') INTO currentEndDate FROM dual;

INSERT INTO ALL_USERS_REUSE
SELECT 1 AS SEQUENCE, 'Number of users revisited in the last year' AS DESCRIPTION, 
    trade.Report.returned_users(currentEndDate, 12) AS STATISTICS1, NULL
    FROM dual;
    
INSERT INTO ALL_USERS_REUSE
SELECT 3 AS SEQUENCE, 'Number of users revisited in the last month' AS DESCRIPTION, 
    trade.Report.returned_users(currentEndDate, 1) AS STATISTICS1, NULL
    FROM dual;

    FROM dual; */

    commit;
    DBMS_OUTPUT.PUT_LINE(STATISTICS1); 
    DBMS_OUTPUT.PUT_LINE(currentEndDate);
    
OPEN Test_Cursor FOR    
  select * from ALL_USERS_REUSE
    where ALL_USERS_REUSE.sequence between Test_Parameter1 and Test_Parameter2;

END SP_REFCURSOR_REUSE;


Function returned_users1:


FUNCTION returned_users1(end_date DATE, time_frame NUMBER)
RETURN NUMBER
IS
    return_value NUMBER(20) := 0;
BEGIN
    SELECT COUNT(1)
	INTO return_value
    FROM (SELECT user_id, customertype 
    FROM oradba.temp_hist
    WHERE TRUNC(txtimestamp) BETWEEN ADD_MONTHS(TRUNC(end_date), -time_frame) AND TRUNC(end_date)
    GROUP BY user_id, customertype
    HAVING COUNT(1) > DECODE(new_user(user_id, customertype, end_date, time_frame), 'Y', 1, 0));
--    HAVING COUNT(1) > (case when (new_user(user_id, customertype, end_date, time_frame)) = 'Y' then 1 else 0 end));   */
--    HAVING COUNT(1) > (if (new_user(user_id, customertype, end_date, time_frame)) = 'Y' then 1 else 0));
    RETURN return_value;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 0;
END;

Thanks and regards,

Chandra

 

[thiagarr]

Hi,

In continuation to the post I started yesterday, I was wondering if the functions called by the SP is not liked by CR (CR whitepaper mentions SP cannot call another SP). Hence I prepared a simple one without a Function and tried. The result is same - SQL developer brings correct values and CR reports zero.

When I deleted the "delete from table" line, CR brought those values in the report and also added the zero (calculated by it) in the report. It is true of the SP with functions in them.

The example provided by CR in the Whitepaper 'Oracle Stored Procedures and Crystal Reports', things work fine as designed. The difference in the CR example and my sample is that there is deletion of data from the table and writing of data to the table. IN my sample, the Sequence and description column are populated but the statistics1 is zero.

Any suggestions are welcome and will be greatly appreciated.

CREATE OR REPLACE
PROCEDURE SP_REFCURSOR_V2_REUSE (
Test_Cursor IN OUT Test_REUSE_Package.Test_Type,
Test_Parameter1 IN ALL_USERS_REUSE.sequence%TYPE,
Test_Parameter2 IN ALL_USERS_REUSE.sequence%TYPE)
AS

currentEndDate DATE;
return_value NUMBER(20) := 0;
return_value1 NUMBER(20) := 0;

BEGIN
delete from ALL_USERS_REUSE;
currentEndDate := SYSDATE;
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-2)), 'DD-MON-YYYY') INTO currentEndDate FROM dual;

SELECT COUNT(*)
    INTO return_value
    FROM test.test_user a WHERE CUSTOMERTYPE <> 'FTN'  AND
		    ((DATE_CREATED BETWEEN '29-JUL-2000' AND '30-JUN-2002'
			  AND UPPER(FLAG) = 'Y') OR
		     (DATE_CREATED BETWEEN '1-JUL-2002' AND currentEndDate));

   
OPEN Test_Cursor FOR    
  select * from ALL_USERS_REUSE
    where ALL_USERS_REUSE.sequence between Test_Parameter1 and Test_Parameter2;

END SP_REFCURSOR_V2_REUSE;




Package:

create or replace PACKAGE TEST_REUSE_PACKAGE
AS TYPE Test_Type IS REF CURSOR RETURN ALL_USERS_REUSE%ROWTYPE;
END TEST_REUSE_PACKAGE;

Thanks and regards,

Chandra

 

[thiagarr]

Hi All,

I have been working further on this and want to share the following with you and, at the same time, seek help from you all in resolving the issue.

This is the same version of code I included yesterday.

I am getting the same error in CR, etc except. But, when I change the following code

insert into ALL_USERS_REUSE select 1 as SEQUENCE, 'Test1' as DESCRIPTION, return_value as STATISTICS1, null from dual;

into the following (basically, replace 'return_value' with 1000), I get the correct result in CR. In effect, there is some issue in the variable allocation, etc while using CR (but not in SQLdeveloper - SQLdeveloper always showed the correct result, as mentioned before)

insert into ALL_USERS_REUSE select 1 as SEQUENCE, 'Test1' as DESCRIPTION, 1000 as STATISTICS1, null from dual;

Complete code is shown below:

CREATE OR REPLACE
PROCEDURE SP_REFCURSOR_V2_REUSE (
Test_Cursor IN OUT Test_REUSE_Package.Test_Type,
Test_Parameter1 IN ALL_USERS_REUSE.sequence%TYPE,
Test_Parameter2 IN ALL_USERS_REUSE.sequence%TYPE)
AS

sql_stmt VARCHAR2(200);
STATISTICS1 Number;
currentEndDate DATE;
return_value number := 0;

BEGIN

delete from ALL_USERS_REUSE;
currentEndDate := SYSDATE;
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-2)), 'DD-MON-YYYY') INTO currentEndDate FROM dual;

SELECT COUNT(*) INTO return_value FROM dba.user a 
              WHERE CUSTOMERTYPE <> 'FTN'  AND 
		    ((DATE_CREATED BETWEEN '29-JUL-2000' AND '30-JUN-2002'
			  AND UPPER(TNCFLAG) = 'Y') OR
		     (DATE_CREATED BETWEEN '1-JUL-2002' AND currentEndDate));
        
        dbms_output.PUT_LINE('Return value = '||return_value);

insert into ALL_USERS_REUSE select 1 as SEQUENCE, 'Test1' as DESCRIPTION, return_value as STATISTICS1, null from dual;
  
commit;
    
OPEN Test_Cursor FOR    
  select * from ALL_USERS_REUSE
    where ALL_USERS_REUSE.sequence between Test_Parameter1 and Test_Parameter2;

END SP_REFCURSOR_V2_REUSE;

Thanks and regards,

Chandra