Calling procedure from oracle to crystal report

Question

I've created an oracle procedure and it's working on oracle SQL editor.
I want to use the procedure in Crystal report.
From database expert, I select the procedure and there's an error message:

==============================
Query Engine Error: 'ADO Error Code: 0x
Source: OraOLEDB
Description: ORA-01850: hour must be between 0 and 23
ORA-06512: at "NPLS.PROC_YEARLYACTIVELIST2", line 19
ORA-06512: at line 1
Native Error: '
==============================

I declare the variables like this:
==============================
str INT;
inv_date DATE;
==============================

Here's the procedure,
==============================
CREATE OR REPLACE PROCEDURE PROC_YEARLYACTIVELIST2(in_year IN VARCHAR)
AS

ctr INT;
str INT;
curr_rowid VARCHAR2(50);
inv_date DATE;

BEGIN

DELETE FROM TBLACTIVELISTYEARLY2;
ctr := 1;
FOR ctr IN 1..12
LOOP

IF ctr = 1 THEN
str := '01';
inv_date := to_date('31-01-' || in_year || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
END IF;
END;
/
==============================

I've been trying to solve this problem for so long and still can't find solution.
I hope you can help me.

Thanks! :)

-Michelle (On the job trainee)

Answer 1

one possibility is that in_year is not being passed in by Crystal or passed in as NULL.

please verify this. you can add this code as a quick check:

  if (trim(in_year) is null) or length(in_year != 4))
  then
    raise_application_error(-20000, 'year is invalid: '  || in_year);
  end if;
 inv_date := to_date('31-01-' || in_year || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');

what error does that throw?

as if the year was null, we'd get that error:

SQL> declare
  2    inv_date DATE;
  3     in_year varchar2(42) ;
  4  begin
  5   inv_date := to_date('31-01-' || in_year || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01850: hour must be between 0 and 23
ORA-06512: at line 5

Answer 2

Check your database nls time setting. i think it is different from you crystal reports setting.

if you need to change the nls setting use this query-- alter session set nls_date_format ='your date format here';

Answer 3

The function is valid and works. There is an extraneous dash between the year and the hour in the format mask. Oracle should be able to ignore that but perhaps Crystal can't: it would certainly be worthwhile cleaning it up, if only to eliminate it from enquiries.

If it's not that then the only other thing it could be is teh value of in_year. Can you get that written to a trace file to see what it is?

Alternatively here's a more convoluted but perhaps less flaky way to get the same datetime:

last_day(trunc(to_date( in_year, 'YYYY') , 'YYYY') ) + (86399/86400)

                          ^  first day of current month
          ^ first day of current year
 ^ last day of month
                                                        ^ last second of the day