3

I've been having a strange issue where the comparison of a date column to SYSDATE yields the following error:

01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
           incorrect.  The input data did not contain a number where a number was
           required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation.

I'm re-creating a MATERIALIZED VIEW; which included some minor changes, and whenever the process aborts it always points to the '>=' in the following derived table query:

SELECT id, 
  desc,
  start_date,
  end_date
 FROM T_LIPR_POLICY_ROLE TLPR
 WHERE end_date >= SYSDATE

Now end_date is a type DATE, and I can actually execute this query by itself, but whenever I try to run it in the materialized view it always aborts with the error above. Although last week I was able to create it with the same query.

Any ideas?

Thank you,

sonar
  • 385
  • 3
  • 15
  • Can you post all the relevant DDL? Is it possible that your materialized view was altered, and it no longer has the same data types as the query? – Jon Heller Feb 09 '13 at 05:41

2 Answers2

1

Hi I'm terribly sorry for the long delay. I just couldn't post the whole statement for security reasons.

Now the issue has been resolved. The problem was that our materialized view script was aggregating data from 17 different places vía a UNIONs. Now for some reason the error was pointing to wrong line of code (see below).

SELECT id, 
 desc,
 start_date,
 end_date
FROM T_LIPR_POLICY_ROLE TLPR
WHERE end_date >= SYSDATE <-- ORACLE POINTS TO THIS LINE

Now this was like the tenth statement in the script, but the error really was in the sixth statement in the script; which was obviously misleading. In this statement a particular record (out of millions) was attempting the following operation:

to_date('  / 0/    ') <-- This was the cause of the problem.

Note that this text wasn't like this in the actual script it literally said to_date(<column name of type varchar>), but 2 records out of 15 million had the text specified above.

Now what I don't quite get is why Oracle points to the wrong line of code.

¿Is it an Oracle issue? ¿Is it a problem with the SQL Developer? ¿Could it be a conflict with a hint? We use several like this: /*+ PARALLEL (init 4) */

Thank you for all your help.

sonar
  • 385
  • 3
  • 15
  • This was totally an issue with the SQL Developer. Toad reports the error location accurately. – sonar Jun 18 '13 at 15:19
0

Is desc a column name? If yes then you are using a oracle reserved keyword desc as a column name.

SELECT id, 
  desc,---- here
  start_date,
  end_date
 FROM T_LIPR_POLICY_ROLE TLPR
 WHERE end_date >= SYSDATE

We cannot use oracle reserved keywords in column names.

Please change the column name.

Abhishek kumar
  • 2,586
  • 5
  • 32
  • 38
  • Oh sorry about that. It's not the actual column name. The thing is that I can actually run the query by itself just fine. It's just that whenever I run it as part of the CREATE MATERIALIZED VIEW statement I get this error about 15 minutes into the execution. – sonar Feb 11 '13 at 14:13