SEO

We have a development and production oracle database. The following
code works on the development database but not on the production
database. If I replace the date variable with a hardcoded date, it
works fine. Something in the I_STDT is causing a problem. Is there a
problem with my code is there a possible difference in the databases?

-- Training Days


CREATE OR REPLACE PACKAGE PG_TRDAYS AS
TYPE cdcur IS REF CURSOR;
PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
VARCHAR2);
END PG_TRDAYS;
/

CREATE OR REPLACE PACKAGE BODY PG_TRDAYS AS
PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
VARCHAR2) IS

v_hiercd cdcur;

BEGIN

OPEN v_hiercd FOR

SELECT
CRSE.CRSE_TTL "Course Title",
CRSE_SESSION.ATTENDEE_NUM_CT "Num. Attend",
CRSE.DAYS_IN_CLASS "Days in Class",
CRSE_SESSION.END_DT "End Date",
UPPER(HIER.TTL) "Organization",
LOOKUP_STRINGS.TTL "Locations",
CRSE_SESSION.SITE_CD "Site Code",
CRSE_SESSION.CRSE_SESSION_STATUS_CD "Session Status",
UPPER(CRSE.LOCAL_CRSE_PREFIX_CD) || UPPER(CRSE.LOCAL_CRSE_CD) "Course
Code"
FROM
CRSE CRSE,
CRSE_SESSION CRSE_SESSION,
HIER HIER,
LOOKUP_STRINGS LOOKUP_STRINGS,
(SELECT STRING_CD, VALUE_CD FROM LOOKUP WHERE CD=25) LOOKUP
WHERE
CRSE.CRSE_CD=CRSE_SESSION.CRSE_CD
AND
CRSE.SESSION_PROVIDER_CD=HIER.CD (+)
AND
CRSE_SESSION.SITE_CD=LOOKUP.VALUE_CD (+)
AND
LOOKUP.STRING_CD=LOOKUP_STRINGS.STRING_CD (+)
AND
CRSE_SESSION.END_DT IS NOT NULL
AND
CRSE_SESSION.ATTENDEE_NUM_CT>0
AND
CRSE_SESSION.CRSE_SESSION_STATUS_CD='O'
AND
UPPER(HIER.TTL) LIKE UPPER(I_RORG) || '%'
AND
CRSE_SESSION.END_DT BETWEEN TO_DATE(I_STDT, 'MM/DD/YYYY') AND SYSDATE

ORDER BY HIER.TTL, LOOKUP_STRINGS.TTL;
TandO := v_hiercd;

END SP_TRDAYS;

END PG_TRDAYS;

snip

> works fine. Something in the I_STDT is causing a problem. Is there
a
> problem with my code is there a possible difference in the databases?

The oracle supplied routines in the package dbms_output or perhaps
utl_file can be used here, try putting in some debugging in both places
and narrow down where your problem lies.

Gotta be honest, at this point, I barely understand Oracle let along
how to do a debug. I've tried getting a straight answer from the dba
as to why the code works on one database and not the other, but they've
been less than helpful.

On 3 Jan 2005 12:43:36 -0800, mark.fergel@bankofamerica.com wrote:

>If I replace the date variable with a hardcoded date, it
>works fine. Something in the I_STDT is causing a problem. Is there a
>problem with my code is there a possible difference in the databases?

Sorry to say so, but your post is very imprecise.
What does 'doesn't work' exactly mean?
Why is the formal parameter i_stdt a varchar2 instead of a date? (What
it should have been in the first place).

One obvious source for problems is your actual parameter (when you
call the procedure) is a varchar2 too, and isn't in the same format as
the 'MM/DD/YYYY' format you seem to expect.
Did you assure the nls_date_format setting (and/or the nls_territory
setting) for both databases is the same?
please run select * from nls_session_parameters on both databases and
compare the results.

Hth


--
Sybrand Bakker, Senior Oracle DBA

mark.fergel@bankofamerica.com wrote:

> We have a development and production oracle database. The following
> code works on the development database but not on the production
> database. If I replace the date variable with a hardcoded date, it
> works fine. Something in the I_STDT is causing a problem. Is there a
> problem with my code is there a possible difference in the databases?
>
> -- Training Days
>
>
> CREATE OR REPLACE PACKAGE PG_TRDAYS AS
> TYPE cdcur IS REF CURSOR;
> PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
> VARCHAR2);
> END PG_TRDAYS;
> /
>
> CREATE OR REPLACE PACKAGE BODY PG_TRDAYS AS
> PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
> VARCHAR2) IS
>
> v_hiercd cdcur;
>
> BEGIN
>
> OPEN v_hiercd FOR
>
> SELECT
> CRSE.CRSE_TTL "Course Title",
> CRSE_SESSION.ATTENDEE_NUM_CT "Num. Attend",
> CRSE.DAYS_IN_CLASS "Days in Class",
> CRSE_SESSION.END_DT "End Date",
> UPPER(HIER.TTL) "Organization",
> LOOKUP_STRINGS.TTL "Locations",
> CRSE_SESSION.SITE_CD "Site Code",
> CRSE_SESSION.CRSE_SESSION_STATUS_CD "Session Status",
> UPPER(CRSE.LOCAL_CRSE_PREFIX_CD) || UPPER(CRSE.LOCAL_CRSE_CD) "Course
> Code"
> FROM
> CRSE CRSE,
> CRSE_SESSION CRSE_SESSION,
> HIER HIER,
> LOOKUP_STRINGS LOOKUP_STRINGS,
> (SELECT STRING_CD, VALUE_CD FROM LOOKUP WHERE CD=25) LOOKUP
> WHERE
> CRSE.CRSE_CD=CRSE_SESSION.CRSE_CD
> AND
> CRSE.SESSION_PROVIDER_CD=HIER.CD (+)
> AND
> CRSE_SESSION.SITE_CD=LOOKUP.VALUE_CD (+)
> AND
> LOOKUP.STRING_CD=LOOKUP_STRINGS.STRING_CD (+)
> AND
> CRSE_SESSION.END_DT IS NOT NULL
> AND
> CRSE_SESSION.ATTENDEE_NUM_CT>0
> AND
> CRSE_SESSION.CRSE_SESSION_STATUS_CD='O'
> AND
> UPPER(HIER.TTL) LIKE UPPER(I_RORG) || '%'
> AND
> CRSE_SESSION.END_DT BETWEEN TO_DATE(I_STDT, 'MM/DD/YYYY') AND SYSDATE
>
> ORDER BY HIER.TTL, LOOKUP_STRINGS.TTL;
> TandO := v_hiercd;
>
> END SP_TRDAYS;
>
> END PG_TRDAYS;

Just a couple of quick comments on your code. They won't solve
the immediate problem but ....

1. Why alias tables to themselves? It accomplishes nothing but
make your code harder to read.

2. You might want to consider bind variables in your WHERE clause

3. You must SELECT INTO in PL/SQL so I can't believe it works anywhere
in any database ... at least not an Oracle database.

No version, no error message, no specific help is possible. But I like
#3 for being the real problem.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Comments embedded.

DA Morgan wrote:
> mark.fergel@bankofamerica.com wrote:
>
> > We have a development and production oracle database. The
following
> > code works on the development database but not on the production
> > database. If I replace the date variable with a hardcoded date, it
> > works fine. Something in the I_STDT is causing a problem. Is
there a
> > problem with my code is there a possible difference in the
databases?
> >
> > -- Training Days
> >
> >
> > CREATE OR REPLACE PACKAGE PG_TRDAYS AS
> > TYPE cdcur IS REF CURSOR;
> > PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
> > VARCHAR2);
> > END PG_TRDAYS;
> > /
> >
> > CREATE OR REPLACE PACKAGE BODY PG_TRDAYS AS
> > PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
> > VARCHAR2) IS
> >
> > v_hiercd cdcur;
> >
> > BEGIN
> >
> > OPEN v_hiercd FOR
> >
> > SELECT
> > CRSE.CRSE_TTL "Course Title",
> > CRSE_SESSION.ATTENDEE_NUM_CT "Num. Attend",
> > CRSE.DAYS_IN_CLASS "Days in Class",
> > CRSE_SESSION.END_DT "End Date",
> > UPPER(HIER.TTL) "Organization",
> > LOOKUP_STRINGS.TTL "Locations",
> > CRSE_SESSION.SITE_CD "Site Code",
> > CRSE_SESSION.CRSE_SESSION_STATUS_CD "Session Status",
> > UPPER(CRSE.LOCAL_CRSE_PREFIX_CD) || UPPER(CRSE.LOCAL_CRSE_CD)
"Course
> > Code"
> > FROM
> > CRSE CRSE,
> > CRSE_SESSION CRSE_SESSION,
> > HIER HIER,
> > LOOKUP_STRINGS LOOKUP_STRINGS,
> > (SELECT STRING_CD, VALUE_CD FROM LOOKUP WHERE CD=25) LOOKUP
> > WHERE
> > CRSE.CRSE_CD=CRSE_SESSION.CRSE_CD
> > AND
> > CRSE.SESSION_PROVIDER_CD=HIER.CD (+)
> > AND
> > CRSE_SESSION.SITE_CD=LOOKUP.VALUE_CD (+)
> > AND
> > LOOKUP.STRING_CD=LOOKUP_STRINGS.STRING_CD (+)
> > AND
> > CRSE_SESSION.END_DT IS NOT NULL
> > AND
> > CRSE_SESSION.ATTENDEE_NUM_CT>0
> > AND
> > CRSE_SESSION.CRSE_SESSION_STATUS_CD='O'
> > AND
> > UPPER(HIER.TTL) LIKE UPPER(I_RORG) || '%'
> > AND
> > CRSE_SESSION.END_DT BETWEEN TO_DATE(I_STDT, 'MM/DD/YYYY') AND
SYSDATE
> >
> > ORDER BY HIER.TTL, LOOKUP_STRINGS.TTL;
> > TandO := v_hiercd;
> >
> > END SP_TRDAYS;
> >
> > END PG_TRDAYS;
>
> Just a couple of quick comments on your code. They won't solve
> the immediate problem but ....
>
> 1. Why alias tables to themselves? It accomplishes nothing but
> make your code harder to read.
>
> 2. You might want to consider bind variables in your WHERE clause
>
> 3. You must SELECT INTO in PL/SQL so I can't believe it works
anywhere
> in any database ... at least not an Oracle database.
>

Not with the OPEN <cursorname> FOR syntax, which is what he's written.
This works quite well. Had he not opened a cursor for the select I
would have agreed.

> No version, no error message, no specific help is possible. But I
like
> #3 for being the real problem.
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace 'x' with 'u' to respond)

My thoughts tend to side with Sybrand Bakker; I_STDT should be a DATE,
not a VARCHAR2, or TO_DATE() should be used to convert the value with a
constant date mask. It would appear, as Sybrand has already stated,
that the OP is expecting the same default date format between
databases, something one shouldn't be expecting. Using TO_DATE() or
declaring I_STDT as a DATE would probably clear up the entire issue, or
at least throw an error for an improperly formatted date string.
David Fitzjarrell

Comments embedded.

DA Morgan wrote:
> mark.fergel@bankofamerica.com wrote:
>
> > We have a development and production oracle database. The
following
> > code works on the development database but not on the production
> > database. If I replace the date variable with a hardcoded date, it
> > works fine. Something in the I_STDT is causing a problem. Is
there a
> > problem with my code is there a possible difference in the
databases?
> >
> > -- Training Days
> >
> >
> > CREATE OR REPLACE PACKAGE PG_TRDAYS AS
> > TYPE cdcur IS REF CURSOR;
> > PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
> > VARCHAR2);
> > END PG_TRDAYS;
> > /
> >
> > CREATE OR REPLACE PACKAGE BODY PG_TRDAYS AS
> > PROCEDURE SP_TRDAYS(TandO OUT cdcur, I_RORG IN VARCHAR2, I_STDT IN
> > VARCHAR2) IS
> >
> > v_hiercd cdcur;
> >
> > BEGIN
> >
> > OPEN v_hiercd FOR
> >
> > SELECT
> > CRSE.CRSE_TTL "Course Title",
> > CRSE_SESSION.ATTENDEE_NUM_CT "Num. Attend",
> > CRSE.DAYS_IN_CLASS "Days in Class",
> > CRSE_SESSION.END_DT "End Date",
> > UPPER(HIER.TTL) "Organization",
> > LOOKUP_STRINGS.TTL "Locations",
> > CRSE_SESSION.SITE_CD "Site Code",
> > CRSE_SESSION.CRSE_SESSION_STATUS_CD "Session Status",
> > UPPER(CRSE.LOCAL_CRSE_PREFIX_CD) || UPPER(CRSE.LOCAL_CRSE_CD)
"Course
> > Code"
> > FROM
> > CRSE CRSE,
> > CRSE_SESSION CRSE_SESSION,
> > HIER HIER,
> > LOOKUP_STRINGS LOOKUP_STRINGS,
> > (SELECT STRING_CD, VALUE_CD FROM LOOKUP WHERE CD=25) LOOKUP
> > WHERE
> > CRSE.CRSE_CD=CRSE_SESSION.CRSE_CD
> > AND
> > CRSE.SESSION_PROVIDER_CD=HIER.CD (+)
> > AND
> > CRSE_SESSION.SITE_CD=LOOKUP.VALUE_CD (+)
> > AND
> > LOOKUP.STRING_CD=LOOKUP_STRINGS.STRING_CD (+)
> > AND
> > CRSE_SESSION.END_DT IS NOT NULL
> > AND
> > CRSE_SESSION.ATTENDEE_NUM_CT>0
> > AND
> > CRSE_SESSION.CRSE_SESSION_STATUS_CD='O'
> > AND
> > UPPER(HIER.TTL) LIKE UPPER(I_RORG) || '%'
> > AND
> > CRSE_SESSION.END_DT BETWEEN TO_DATE(I_STDT, 'MM/DD/YYYY') AND
SYSDATE
> >
> > ORDER BY HIER.TTL, LOOKUP_STRINGS.TTL;
> > TandO := v_hiercd;
> >
> > END SP_TRDAYS;
> >
> > END PG_TRDAYS;
>
> Just a couple of quick comments on your code. They won't solve
> the immediate problem but ....
>
> 1. Why alias tables to themselves? It accomplishes nothing but
> make your code harder to read.
>
> 2. You might want to consider bind variables in your WHERE clause
>
> 3. You must SELECT INTO in PL/SQL so I can't believe it works
anywhere
> in any database ... at least not an Oracle database.
>

Not with the OPEN <cursorname> FOR syntax, which is what he's written.
This works quite well. Had he not opened a cursor for the select I
would have agreed.

> No version, no error message, no specific help is possible. But I
like
> #3 for being the real problem.
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace 'x' with 'u' to respond)

My thoughts tend to side with Sybrand Bakker; I_STDT should be a DATE,
not a VARCHAR2, or TO_DATE() should be used to convert the value with a
constant date mask. It would appear, as Sybrand has already stated,
that the OP is expecting the same default date format between
databases, something one shouldn't be expecting. Using TO_DATE() or
declaring I_STDT as a DATE would probably clear up the entire issue, or
at least throw an error for an improperly formatted date string.
David Fitzjarrell

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.

I did switch the I_STDT IN VARCHAR2 to DATE instead. Doing so does
work. I tried various combinations that I can get to work. I really
would prefer in Crystal to be able to type my date string out as
01/01/2004, which was the format I had specified. By changing it to
DATE, Crystal automatically engages it's mini calendar instead of the
string parameter box.

fitzjarrell@cox.net wrote:

> Not with the OPEN <cursorname> FOR syntax, which is what he's written.
> This works quite well. Had he not opened a cursor for the select I
> would have agreed.

Darn! Missed that. Thanks for pointing it out.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)