Oracle Error

[getjbb]

In Oracle 9i I am trying to assign variables declared as a string to a date field. I am getting the following error:
ORA-01858: a non-numeric character was found where a numeric was expected

The code is as follows:

v_mnth VARCHAR2 DEFAULT NULL,
v_day VARCHAR2 DEFAULT NULL,
v_yr VARCHAR2 DEFAULT NULL

v_date DATE := to_date(v_mnth|| '/' ||v_day || '/' || v_yr,'MM/DD/YYYY');

But, when I hardcode a value for v_mnth, v_day and v_yr I do not get the error.

I do not understand what is wrong.

I appreciate your help in advance.

getjbb

 

[carp]

Try doing your concatenation in the executable part of your code - or initializing the values of your variables. By doing the concatenation in your declaration, with your variable set to NULL, you are asking Oracle to convert the string '//' into a date - and that will not fly!

 

[tarn]

Only a guess but maybe:

to_date('v_mnth/v_day/v_yr','MM/DD/YYYY');

based on reading this:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

IHTH

Laurie.

 

[getjbb]

Thanks, tarn and carp. I will give your suggestions a try.

 

[carp]

getjbb -
The following will work:

DECLARE
   v_mnth VARCHAR2(5) := '02';
   v_day VARCHAR2(5) := '08';
   v_yr VARCHAR2(5) := '2012';
   v_date DATE;
BEGIN
v_date := to_date(v_mnth|| '/' ||v_day || '/' || v_yr,'MM/DD/YYYY');
END;

but only because I have initialized the first three variables to non-null values. Without these values, I get the same error that you are reporting.