SqlDbx Forum

garbuya

Member

Problem with script variables

I'm running the following small script in Oracle 11g R1 using an example from http://www.sqldbx.com/Help/index.htm#pa … mmands.htm

DEFINE var_date DATE
SELECT * FROM  ORDERS WHERE ORD_DATE = '$(var_date)';

Instead of getting a prompt asking me for a date, as it described here
I'm getting "ORA-01858: a non-numeric character was found where a numeric was expected"

Are script varialbles supported for Oracle?

sqldbxhelp

Administrator

Re: Problem with script variables

This is incorrect usage. Possible usages
1.
DEFINE var_date '11/10/2013' DATE
SELECT * FROM  ORDERS WHERE ORD_DATE = &var_date;
This will be translated to
SELECT * FROM ORDERS WHERE ORD_DATE=to_date ('11/10/2013', 'MM/DD/YYYY);

2.
DEFINE var_date 11/10/2013
SELECT * FROM ORDERS WHERE ORD_DATE = '&var_date';
This will be translated to
SELECT * FROM ORDERS WHERE ORD_DATE = '11/10/2013';

Next release will introduce significant improvement to how DEFINE works and also will allow to use $(var_date) syntax for Oracle.