"Oracle.manageddataaccess.client.oracleexception: 'ORA-01867: the interval is invalid. Does anyone know the proper way to do this? "

Question

0.00/5 (No votes)

See more:

I would like to change a sysdate parameter in the App.config file. I am passing a parameter (:ADHERENCEDAY) to my query in the app.config file. I am getting an error message at the cmdAdherence.ExecuteReader() line. "Oracle.ManagedDataAccess.Client.OracleException: 'ORA-01867: the interval is invalid. Does anyone know the proper way to do this?"

App.Config

<add key ="ADHERENCEDAY" value = "1"/>
   
<add key ="QUERY_ADHERENCE_TABLE" value ="Select ADHERENCE_ID,CALENDAR_ID,TIME_TABLE_VERSION_ID,ROUTE_ID,ROUTE_DIRECTION_ID,PATTERN_ID,GEO_NODE_ID,OPERATOR_ID,RUN_ID,WORK_PIECE_ID,VEHICLE_ID,BLOCK_ID,TRIP_ID,SERVICE_TYPE_ID,TRANSIT_DIVISION_ID,TIME_POINT_ID,SCHED_ADHERE_WAIVER_ID,REVENUE_ID,TIME_OF_DAY_ID,VEHICLE_BASE_ID,IS_LAYOVER,BLOCK_STOP_ORDER,MESSAGE_TIME,SCHEDULED_TIME,SCHED_DIST_FROM_LAST_GEO_NODE,ADHERENCE,ACTUAL_ARRIVAL_TIME,ACTUAL_DEPARTURE_TIME,SCHEDULED_TIME_OFFSET,ODOMETER,VALIDITY,EARLY_COUNT,ONTIME_COUNT,LATE_COUNT,MISSING_COUNT,ADJUSTED_EARLY_COUNT,ADJUSTED_ONTIME_COUNT,ADJUSTED_LATE_COUNT,ADJUSTED_MISSING_COUNT,EARLY_WAIVED_TP,LATE_WAIVED_TP,LAYOVER_EARLY_ALLOWED,LAYOVER_LATE_ALLOWED,EARLY_WAIVED_PT,OVERLOAD_ID,IS_BATCHSTORAGE,IS_VEHICLE_STOPPED,IS_DOOR_OPENED,FIRST_LOC_STOP_TIME,FIRST_DOOR_OPEN_TIME,CLOSEST_LOC_TIME,LAST_DOOR_CLOSE_TIME,LAST_LOC_START_TIME,PATTERN_GEO_NODE_SEQ,MISSING_WAIVED_TP,SCHEDULED_TRAVEL_TIME,ACTUAL_TRAVEL_TIME,RECOVERY_TIME,ROUTE_STOP_SEQUENCE,TRIP_EDGE,CROSSING_TYPE_ID,TIME_POINT_INTERVAL_ID,GEO_NODE_INTERVAL_ID
     From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ':ADHERENCEDAY' day, 'YYYYMMDD'))"/>

string strQueryRows = ConfigurationManager.AppSettings["QUERY_ADHERENCE_TABLE"].ToString();

OracleCommand cmdAdherence = new OracleCommand(strQueryRows,con);

cmdAdherence.Parameters.Add(new OracleParameter("ADHERENCEDAY", Convert.ToInt32(ConfigurationManager.AppSettings["ADHERENCEDAY"])));

OracleDataReader readAdherence = cmdAdherence.ExecuteReader();

What I have tried:

I have tried the following options.

From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ':ADHERENCEDAY' day, 'YYYYMMDD'))"/>

<pre>From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL 'ADHERENCEDAY' day, 'YYYYMMDD'))"/>

<pre>From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ADHERENCEDAY day, 'YYYYMMDD'))"/>

Posted 30-Nov-23 6:14am

James Glisson II

Updated 30-Nov-23 6:18am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2023-11-30T10:09:00

Solution 1

Your error message is quite clear. It means that variable is not converted into proper value.
The way you write variable in a query depends on Oracle database. I'd suggest to read this: Using Substitution Variables[^]

Few examples at:
https://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer[^]
https://dba.stackexchange.com/questions/3652/how-do-i-declare-and-use-variables-in-oracle[^]

Posted 30-Nov-23 10:09am

Maciej Los

Comments

James Glisson II 1-Dec-23 7:15am

Yes, I understand the error message and I know where the error is. My question is, how do I properly write the statement in App.Config (passing a SysDate parameter. ":ADHERENCEDAY"). If I don't pass the :ADHERNCEDAY parameter, the query runs fine. It's only when I pass the parameter in the Sysdate function that causes the problem. I illustrated that in my initial example. I added the error message just for context. I am trying to figure out the proper syntax for passing a parameter in app.config in the sysdate function of my query.

Parameter Value: <add key="ADHERENCEDAY" value="1">

Works great (not passing a parameter)-> From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL '1' day, 'YYYYMMDD'))
Does not work -> From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ':ADHERENCEDAY' day, 'YYYYMMDD'))
I am only looking for the proper syntax to get this to work. I have included in my example only a few things that I have tried.

Maciej Los 1-Dec-23 14:54pm

I have seen what have you done. As i mentioned, the way to write proper sql statement depends on Oracle database. I'm not able to help you, because i have no access to Oracle database.

Maciej Los 1-Dec-23 14:54pm

[posted by mistake]