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

I ended up with a similar problem before. I had named a table ABS and everything went fine until I had to use it inside a PL/SQL Function. Then all hell broke loose. There again the PL/SQL parser was confused.

I created a synonym for the table and used that synonym inside the fucntion.
Maybe you could solve your PL/SQL parser problem with a view, hiding the

offending column name ?

Sylvain Leclerc, DBA
sleclerc_at_magrit.com

-----Original Message-----
From: nsouto_at_nsw.bigpond.net.au.nospam (Nuno Souto) [mailto:nsouto_at_nsw.bigpond.net.au.nospam] Posted At: Wednesday, December 13, 2000 4:40 AM Posted To: server
Conversation: TO_DATE does not work inside PLSQL cursor??? Subject: Re: TO_DATE does not work inside PLSQL cursor???

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 - 08:07:24 CST