Re: Following code works on one database but not another

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.

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)