TO_DATE does not work inside PLSQL cursor???

From: Nuno Souto <nsouto_at_nsw.bigpond.net.au.nospam>
Date: Fri, 08 Dec 2000 12:35:55 GMT
Message-ID: <3a30d0f2.1826515@news-server>

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 Received on Fri Dec 08 2000 - 06:35:55 CST