Stored Procedure Date Literal Fails

[tpeters1]

Oracle 9i
Crystal 10

I have found that Crystal Reports chokes on Oracle stored procedures that have date literals coded into SQL Where clauses. Here is an example of the problem:

First, I created this stored procedure on Oracle:
[tt]
CREATE OR REPLACE PROCEDURE todd_test_sp(rs IN OUT sys_refcursor)
AS
BEGIN
OPEN rs FOR
SELECT *
FROM todd_test;
END;
[/tt]
And then I created a crystal report with the procedure set as the datasource. The report returns two rows:

SOME_DATE
1/1/2006 12:00:00AM
1/31/2006 12:00:00AM

Then, if I add a where clause to the stored procudure that has a date literal:
[tt]
CREATE OR REPLACE PROCEDURE todd_test_sp(rs IN OUT sys_refcursor)
AS
BEGIN
OPEN rs FOR
SELECT *
FROM todd_test
WHERE some_date = '01-JAN-2006';
END;
[/tt]
The report should return one row, but no records are returned!

In order to prove that the query is valid, I copied the query out of the stored procedure and ran it using SQL*Plus. One row was properly returned:
[tt]
SQL> SELECT *
2 FROM todd_test
3 WHERE some_date = '01-JAN-2006';

SOME_DATE
---------
01-JAN-06
[/tt]
Does anyone have any idea why the stored procedure with a date literal fails on Crystal?!?

 

[synapsevampire]

Keep in mind that Crystal is not reformulating your SP, it simply executes the SP, so this doesn't make sense.

I think that you're overlooking something basic here.

Either you're using a bad schema, or using the wrong type of connectivity from Crystal, which you didn't post.

Don't use the Oracle supplied ODBC driver, the best is the natvie Oracle as supplied by Crystal. As long aws you're pointing at the same database/schema, it simply executes the SP, it can't alter SP code and it shouldn't randomly omit rows.

-k

 

[synapsevampire]

Also, I don't have an Oracle table to test with right now, but try using a literal for passing the date, using a CAST or CONVERT so that it's assuredly recognizing the date properly.

-k

 

[Turkbear]

Hi,
If some_date is actually a DATE field I am surprised your SqlPlus works without some time component or a Trunc function,but..

Try your SP this way:

CREATE OR REPLACE PROCEDURE todd_test_sp(rs IN OUT sys_refcursor)
AS
BEGIN
    OPEN rs FOR 
    SELECT * 
    FROM todd_test
    WHERE some_date = to_date('01/01/2006','MM/DD/YYYY');    
END;

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Right, or a to_date...

-k

 

[tpeters1]

Thanks Turkbear, your solution works.

When I changed the stored procedure to explicitely cast the literal as a date then the Crystal report returned one record.

However, the question still remains: how is it that Crystal even knows that the source code for the stored procedure has changed? The sproc just returns a cursor and Crystal never parses the query inside the sproc, right?

PS. I am connected to the database using the native Oracle driver.

 

[Turkbear]

Hi,
Actually it seems to be a problem in the way crystal passes the SQL, although it does not look like it when the Sql is viewed from CR):
I am using Oracle's native driver and with this SP:

CREATE OR REPLACE PROCEDURE JVG_test_sp(rs IN OUT sys_refcursor)
AS
BEGIN
    OPEN rs FOR 
    SELECT birth_dt from hr_cur_rptng 
    where birth_dt = '03-SEP-1941'; 
END;

When a base a report on this SP, I get an error :

ORA-01858 a non-numeric character was found where a numeric was expected

Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.

Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.


but, In Oracle ( from SqlPlus) I can do

SQL> var RS refcursor
SQL> exec JVG_test_sp(:RS)

PL/SQL procedure successfully completed.

SQL> print RS

BIRTH_DT
---------
03-SEP-41

SQL>

Seems like a bug, maybe..




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tpeters1]

Turkbear,

What reporting tool did you use the first time when you received the error?

 

[Turkbear]

Hi,
CR XI R2 .....Fully patched..

If I re-write the SP like the previous one I posted, it works fine..

CREATE OR REPLACE PROCEDURE jvg_test_sp(rs IN OUT sys_refcursor)
AS
BEGIN
    OPEN rs FOR 
    SELECT birth_dt
    FROM hr_cur_rptng
    WHERE birth_dt = to_date('09/03/1941','MM/DD/YYYY');    
END;

Show SQL in CR shows exactly the same thing in both cases, so it must be doing 'something' in the background..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tpeters1]

TOAD runs the sproc without error.

I agree, Cystal must by doing *something*.

 

[synapsevampire]

Crystal isn't doing anything, what you're seeing is explicit conversion differences between methods of execution.

Again, Crystal just FIRES the SP.

You're overthinking this.

-k

 

[tpeters1]

TOAD fires the sproc,
no errors.

SqlPlus fires the sproc,
no errors.

 

[Turkbear]

Hi,
What

explicit conversion differences between methods of execution.
?

Ther execution is the same:
In Crystal the SQL shows as:

BEGIN "WAREHOUSE"."JVG_TEST_SP"(:RS); END ;

If, as one would expect, Crystal just sends this to Oracle, why does it fail but the same code in SqlPLus does not..
How can
BEGIN "WAREHOUSE"."JVG_TEST_SP"RS); END ;
be interpreted differently?

The explicit date format ( which matches the database's) should not need conversion, anyway.







To Paraphrase:"The Help you get is proportional to the Help you give.."