Following code works on one database but not another

From: <mark.fergel_at_bankofamerica.com>
Date: 3 Jan 2005 12:43:36 -0800
Message-ID: <1104785016.513292.156510@f14g2000cwb.googlegroups.com>

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; Received on Mon Jan 03 2005 - 14:43:36 CST