-1

I have a stored procedure that contains the following:

V_DATACONVERTIDA varchar2(100) := TO_DATE('27/10/1994 23:59:59', 'DD/MM/YYYY HH24:MI:SS.SSSSS');

It works fine, but the problem is I gotta make that date a variable. So I created

V_DATAEXPIRACAO VARCHAR2(100) := p__formdata;

being p__formdata (varchar2) a parameter from the SP.

When I made that change, though, I got the following error:

ORA-01858: a non-numeric character was found where a numeric was expected

Rodrigo
  • 11
  • 2
  • 8
  • It seems you are assigning date output to VARCHAR2. – IndoKnight Apr 08 '13 at 18:49
  • I know, but (I dont know how or why) but in the first line of code I posted I'm doing the same thing and it works. :( – Rodrigo Apr 08 '13 at 18:51
  • I switched from varchar2(100) to date and it threw me the same error – Rodrigo Apr 08 '13 at 18:53
  • 3
    Can you post more of the stored procedure? I don't think there's enough information to go on here - for example, we don't know the data type of `p__formdata`. – Ed Gibbs Apr 08 '13 at 18:57
  • it's varchar2 as well, I don't understand why it says it expects a numeric value when everything is supposed to be string (or varchar) – Rodrigo Apr 08 '13 at 19:02

1 Answers1

1

" I don't understand why it says it expects a numeric value when everything is supposed to be string (or varchar)"

Well clearly everything isn't a string. Oracle hurls the ORA-01858 error we attempt to cast a string to a date but with wrong format mask; for instance this example passes a character month but specifies a numeric month in the mask: to_date('08-APR-2013', 'DD-MM-YYYY') .

So, if you're getting that error your program isn't doing what you think it is. But only you can see the whole source, so only you can figure out where the bug lies.

APC
  • 144,005
  • 19
  • 170
  • 281