I am writing a process to delete records based on dates.
Here is the table:
| Column Name | Data Type | Nullable | Default | Primary Key |
|---|
| ID | NUMBER | No | - | 1 |
| CT_NAME | VARCHAR2(100) | Yes | - | - |
| CT_CATEGORY | VARCHAR2(100) | Yes | - | - |
| BASE_PTS | NUMBER | Yes | - | - |
| PROD_PTS | NUMBER | Yes | - | - |
| REF_PTS | NUMBER | Yes | - | - |
| START_DATE | DATE | Yes | - | - |
| END_DATE | DATE | Yes | - | - |
| CREATED_ON | DATE | Yes | - | - |
| CREATED_BY | VARCHAR2(50) | Yes | - | - |
| UPDATED_ON | DATE | Yes | - | - |
| UPDATED_BY | VARCHAR2(50) | Yes | - | - |
with start_date and end_date being the key date fields.
The query will delete dates with specific start and end dates. The issue I am having is that I keep getting a ORA-01858: a non-numeric character was found where a numeric was expected error when the query executes.
delete from ct_point_values
where start_date=:P4_START_DATE_V
and end_date=:P4_END_DATE_V
If I run the query in the sql window, no problem:
delete from ct_point_values
where start_date='1/1/2014'
and end_date='12/31/2014'
The query with the hard coded values works find. Binds fail. I have tried variations of casting, to_char and to_date with no luck.
One thing I do notice is that when I return to enter my bind variables in the sql workshop, the fields are pre-populated with this:
1/1/2014
12/31/2014
for 1/1/2014 and 12/31/2014. The only item I have found relates to this suggests it is a possible bug:
SQL Workshop Bind Variables - Date Issues
Any thoughts on how to address this, or is this really a bug?
I am running on Apex 4.2, 11g.
Thanks!