Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: TO_DATE does not work inside PLSQL cursor???

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

From: Nuno Souto <nsouto_at_nsw.bigpond.net.au.nospam>
Date: Wed, 13 Dec 2000 09:40:21 GMT
Message-ID: <3a37403f.1431892@news-server>

On Wed, 13 Dec 2000 07:32:59 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>I don't know -
> from_date, to_date seem a reasonably pair of names.
>
>If you look at v$sort_usage, you will find that some
>employees of Oracle Corp have managed to name
>(and q.a.) a column called USER !
>

Ouch!

Well, I wonder as time goes by and more Sybase apps are converted to ORACLE, how many more of these little Easter eggs will crop up.

The obvious thing from my Oracle-centered universe would be to name such a column "date_to" or even "dateto". But I can understand where Sybasers and SSers would object, given they have a "dateto" function. I can understand it being originally "to_date", given this was designed for Sybase.

What worries me deeply is that PL/SQL gets confused, but if I fire the same SQL SELECT statement in SQL*Plus, then I get no errors: it accepts "to_date" as a function name, unless I double-quote it which would bring it to a column name. With PL/SQL, it gets confused when it sees TO_DATE inside a cursor, even though what follows it is a conventional function (delimited by ()). Obviously, the parsing taking place for a PL/SQL cursor is different from the parsing taking place in normal SQL.

Oh well, fun and games. The workaround was easy anyway: Just defined the boundaries for BETWEEN as two variables and used the TO_DATE function to populate them.

Catchyalata.
Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Wed Dec 13 2000 - 03:40:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US