Re: Following code works on one database but not another

Comments embedded.

DA Morgan wrote:
> mark.fergel_at_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_at_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 Received on Tue Jan 04 2005 - 08:06:33 CST