Re: Following code works on one database but not another

mark.fergel_at_bankofamerica.com wrote:
> I ran nls_session_parameters on both databases and they came back with
> the exact same info:
>
> PARAMETER VALUE
> ------------------------------ ----------------------------------------
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT DD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> NLS_TIME_FORMAT HH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
> NLS_DUAL_CURRENCY $
> NLS_COMP BINARY
> NLS_LENGTH_SEMANTICS BYTE
> NLS_NCHAR_CONV_EXCP FALSE
>
> My biggest reason for having the to_char on the date is that the
> package is based on the packages created by the adminstrative group.
> While I'm not positive, I believe it is being done because the package
> is used in the creation of a crystal report and then accessed (with
> parameters passed) via an ASP page.
>
> My issue with it not working is that when I try and create a crystal
> report against the package, it works fine in development but production
> is throwing me an empty rowset error and not bring in the tables. I've
> verified that there data exists for the values I am trying to pass by
> hard coding in the dates. What doesn't work is that even though I've
> hard coded the dates, etc., if I try to write the report against that
> (which I know has records), it still comes back with the empty rowset
> error.
>

Although you seem to have solved your issue already, I'd like to know *how* you checked that those records exist.

 From what you wrote earlier (about not knowing much of debugging in oracle), I'd like to improve your toolset a little:

in sqlplus try the following on both databases:

SQL> variable c refcursor;
SQL> BEGIN

2> pg_trdays.SP_TRDAYS(:c, <I_RORG>, <I_STDT>);
3> END;
4> /

(Replace <I_RORG> and <I_STDT> with some suitable values). Do you still get records both in development and production?

Cheers,
Holger Received on Fri Jan 07 2005 - 02:18:55 CST