Re: TO_DATE does not work inside PLSQL cursor???

In our last gripping episode nsouto_at_nsw.bigpond.net.au.nospam (Nuno Souto) wrote:
> Hi fellas.
>
> Had this problem in older versions of ORACLE. Looks like it's still
> there in 8.0.6...
> ***************************************************************
> Table T1:
> F1 NOT NULL NUMBER,
> F2 NOT NULL NUMBER,
> F3 NOT NULL DATE;
> Don't bother creating data, the error happens before it gets to it!
> ***************************************************************
> Straight SQL:
>
> SELECT f1,f2,count(f3)
> FROM T1
> WHERE F3 BETWEEN TO_DATE('01021996140000','ddmmyyyyhh24miss')
> AND TO_DATE('01022001140000','ddmmyyyyhh24miss')
> AND F1 > 100
> GROUP BY F1,F2;
>
> this works without a problem.
> ***************************************************************
> Now, in PL/SQL:
>
> DECLARE
> cursor c1 is
> SELECT f1,f2,count(f3)
> FROM T1
> WHERE F3 BETWEEN TO_DATE('01021996140000','ddmmyyyyhh24miss')
> AND TO_DATE('01022001140000','ddmmyyyyhh24miss')
> AND F1 > 100
> GROUP BY F1,F2;
> c1_rec c1%ROWTYPE;
> BEGIN
> open c1;
> fetch c1 into c1_rec;
> close c1;
> END;
> .
> /
>
> this doesn't execute at all, comes back with syntax error,
> PLS-0222, "TO_DATE is not valid function in this context"
> or words to the same effect...
> ***************************************************************
> But if I do:
>
> DECLARE
> l_date_from DATE := TO_DATE('01021996140000','ddmmyyyyhh24miss');
> l_date_to DATE := TO_DATE('01022001140000','ddmmyyyyhh24miss');
> cursor c1 is
> SELECT f1,f2,count(f3)
> FROM T1
> WHERE F3 BETWEEN l_date_from
> AND l_date_to
> AND F1 > 100
> GROUP BY F1,F2;
> c1_rec c1%ROWTYPE;
> BEGIN
> open c1;
> fetch c1 into c1_rec;
> close c1;
> END;
> .
> /
>
> then all is allright.
>
> Weird, eh?
>
> Had this in V6, V7. Now it's also there in 8.0.6? Any ideas if there
> is a fix? Or am I doing something obscure that I'm not aware of? To
> the best of my knowledge, TO_DATE is a valid SQL function in ANY
> context! If I replace the TO_DATE with other functions in other
> examples, then it works fine. It's not the BETWEEN either, happens
> with <=||>= too.
>
> TIA for any help out there.
>
> Cheers
> Nuno Souto
> nsouto_at_bigpond.net.au.nospam
> http://www.users.bigpond.net.au/the_Den/index.html
>

It is, apparently, a "development" between 8.0.5 and 8.0.6; I tried this on 8.0.5.2.1 presuming that if it happens in 8.0.6 it would most likely happen in 8.0.5. I had no problems whatsoever. I also had no problems with 8.1.6, although that doesn't do you much good. As I haven't a copy of 8.0.6 I cannot duplicate your experience. I haven't yet perused Metalink for a bug report or patch. Maybe someone else can shed more light on this.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.