Variables in SQL*Plus (Beginner's question)

Hi,

[Quoted] We're evaluating Oracle vs SQL server and Sybase, and the other databases provide this facility for us:

declare ncurdate char(4)
ncurdate = TO_CHAR (TO_NUMBER (TO_CHAR (SYSDATE, 'YYMM'))-300)  (this will yield '9704')

followed by

SELECT * FROM TEST WHERE MORTGAGE_DATE >= ncurdate

This is a simple way to select mortgages in the last three years, given that the mortgages are stored in YYMM format.

We haven't been able to come up with a good alternative in Oracle. The DEFINE command only lets us assign literals rather than expressions, and trying to do anything in PL/SQL requires a SELECT INTO command, and the use of cursors to get anything done.

Is there any way to declare variables in SQL*Plus, and then reference them in a SELECT statement?

Thanks in advance.

Dave Received on Tue Jul 08 1997 - 00:00:00 CEST