oracle query

I am not sure I understand what you are doing, but there are no parenthesis in the between and you should use explicit conversions:

select datavalue, timestamp from  numericlog where logname = 'xxxxxxxxxxx' and timestamp between to_timestamp('2015-06-06', 'yyyy-mm-dd') and to_timestamp('2015-06-08', 'yyyy-mm-dd')

i am getting this following error
Error:
Error while refreshing the data provider.

Cause of Error
ORA-28527: Heterogeneous Services datatype mapping error
ORA-02063: preceding line from EH

my records inside the table currently showing like this
53.83 May 23, 2015 8:21:04 PM EDT

What is the datatype of that column?  You refer to it as timestamp so that is what I used.  Seems like it isn't actually a timestamp.

It also looks like you are querying a remote database.  What RDBMS is that?

There are at least two problems in your query:
1. The "between" operator in Oracle does not expect or allow parentheses.  So the syntax should be more like this:
    select ... from ... where ... between [date_value1] and [date_value2]

2. Providing values like this: '2015-06-06' when you expect Oracle to use that as a date is always dangerous.  It is much safer to always use an explicit datatype conversion something like what johnsone suggested.

Then, the fact that you see data displayed like this:
53.83;May 23, 2015 8:21:04 PM EDT

does not tell us much.

We don't know if that is a single column value, or if that is two columns, with "53.83" in one column, and the rest of that string as the value for the second column.  If that is two columns, and the second column is actually an Oracle "date" or "timestamp", it is quite easy to query that in Oracle.

But, we need you to tell us whether that value is in fact a single column value, and if yes, what is the datatype?  Or, is that a representation of multiple database columns, and if so what are their datatypes?

And the fact that your error message includes: "ORA-28527: Heterogeneous Services datatype mapping error" indicates to us that there are at least two databases involved here, Oracle plus something else.  We don't know if your data is actually in Oracle, or not.  It looks like not, even though you are querying it through Oracle.

that is actually two columns sorry for the confusion i am looking only for timestamp.  It is synonym from Oracle, how can i get just date like mm-dd-yyyy only. and between dates

If it was a timestamp or date column in Oracle, the first query would have worked.  Since it didn't, there is a problem.

In Oracle to get a date in that format is easy:
select to_char(sysdate,'MM-DD-YYYY') from dual;

BUT, that makes it a STRING not a DATE so there are issues with BETWEEN.  One does an ASCII compare and one a DATE compare.

In Oracle dates don't have a 'format' until they are displayed.  There are a few things that can control how the date value is displayed.

Just because it looks like "May 23, 2015 8:21:04 PM EDT" doesn't mean it is stored that way.

We need to know the actual data type of the column:  date, timestamp, varchar2, ???

We also need to know the other database and data type to help with the ORA-28527.  As the error suggests, there appears to be a data type mapping issue.

romeiovasu,
The questions posed to you by the experts need replies if you want to get a solution to your problem. A describe on the numericlog table (or view) could help along with a describe on the target db table.

it is a timestamp.  And it is not coming from other database, it is coming from the same database it is a Synonym.

I don't see how you can get the "ORA-28527: Heterogeneous Services datatype mapping error" from a table in an Oracle database when connected to that database.

Heterogeneous Services is connecting an Oracle database to a non-Oracle database.

it is like a text file and it use as a synonym in oracle database.

Synonyms in Oracle are for a table or a view (or a procedure, etc.) but not for an individual column of a table or a view.

To query Oracle data based on a timestamp column and the "between" operator, you need to provide "where" clause values in the same datatype as the column you want the query to search by.  Here is an example to find records in a three-hour time span (8pm - 11pm on a particular date:
select [column1, column2]
from my_table
where [timestamp_column] between TO_TIMESTAMP ('May-23-2015 08:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')
  and  TO_TIMESTAMP ('May-23-2015 11:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')

Note, there are many different formats that you can use to provide the values to search for.  The important thing is to provide a format mask for TO_TIMESTAMP that exactly matches the data you provide.

From sqlplus please post the output from:
desc numericlog

Can you also post the output of this query:

select owner, object_type from all_objects
where object_name = 'NUMERICLOG';

Here is from the sqlplus.
Data shows like this
TIMESTAMP
----------------
25-MAY-15
26-MAY - 15
27-MAY-15
28-MAY-15
29-MAY-15

object_type
-----------------
SYNONYM

Why are you not posting what we are asking for?

I asked for a:
describe of the table, not a select from the table.

>>Data shows like this

I already explained that Oracle dates and timestamps don't have a format until displayed.

If that is a copy/paste, then your problem is in the data.

...
26-MAY - 15
...

Notice the spaces?  This cannot be a DATE and must be a VARCHAR2.

If it isn't a copy/paste, what is that supposed to tell us?

>>here is from the sqlplus.<<
What was the command you entered to get that output? Also, we would still like to see the output from the "desc numericlog" command from sqlplus.

Since the object name (numericlog) in your query is a synonym (and not the actual object name) can you run this query and post the results:

select owner, table_owner, table_name, db_link
from all_synonyms
where synonym_name = 'NUMERICLOG';

SQL> select owner, table_owner, table_name, db_link
  2  from all_synonyms
  3  where synonym_name = 'NUMERICLOG';

OWNER                          TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
DB_LINK
--------------------------------------------------------------------------------

OPS$OCSHIS                     OAUSER
NUMERICLOG
EH.COMPANYNAME.LOCAL

HISTORY                        OAUSER
NUMERICLOG
EH.COMPANYNAME.LOCAL

OWNER                          TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
DB_LINK
--------------------------------------------------------------------------------

Looks like a synonym to a table across a database link as we suspected.  What is EH.COMPANYNAME.LOCAL?  That is the target of the database link.  I suspect that isn't an Oracle database.

that is actually like a text file calling.

Why haven't you posted the table description that I've asked for?

>>that is actually like a text file calling.

A text file would be an External Table with no Heterogeneous Services needed.

and "actually like" isn't "actually".

What is it "actually"?

Honestly, without know what it is exactly and how heterogeneous services deals with it, I'm afraid we can't help much.

So the commands we would like to see the results of are:
desc oasuser.numericlog

and

desc oasuser.numericlog@eh.company.local

ok if i create a query like this.  It works instead of passing as mm/dd/yyyy if i pass dd/mm/yyyy it works.

Can you guys help me even though if pass the parameter as mm/dd/yyyy it should go as dd/mm/yyyy

SELECT
  NUMERICLOG.DATAVALUE,
  NUMERICLOG.TIMESTAMP
FROM
  NUMERICLOG
WHERE
  (
   NUMERICLOG.TIMESTAMP  BETWEEN  '06-10-2015 14:25:19'  AND  '06-10-2015 14:25:37'
   AND
   NUMERICLOG.LOGNAME  =  'LT31110:IO.IOVALUE,2s_2w'
  )

We have no way of knowing 'why' since you haven't provided all the information we've asked for.

My educated guess is the TIMESTAMP column isn't an actual Oracle TIMESTAMP and you are doing string/ascii comparisons not actual date/time comparisons.

>>Can you guys help me ...<<
Only if you help us help you and get us some answers to the numerous requests that have been made. Without that information, everything we supply will be a guess at best.

ok can you guys tell me what you are looking for.  So that i can get it.

If the TIMESTAMP column has a datatype of: DATE or TIMESTAMP, you definitely do *NOT* want to supply values for it like this:
WHERE
    NUMERICLOG.TIMESTAMP  BETWEEN  '06-10-2015 14:25:19'  AND  '06-10-2015 14:25:37'

Why not?  That forces Oracle to do an implicit datatype conversion, since Oracle can only compare numbers to numbers, characters to characters, dates to dates, etc.  If that column is actually a TIMESTAMP and your query provides a value like: '06-10-2015 14:25:37', Oracle sees a TIMESTAMP in the database and a CHARACTER string in your query.  (OK, to humans, your character string looks like it represents a "date" value, but when you present it to Oracle inside single quotes, Oracle sees it as a CHARACTER value.)  Since Oracle cannot compare a DATE to CHARACTER, it has to convert one of those values to the other datatype.  Since converting a CHARACTER string to a DATE can result in an error (like if you supply this string for example: '02-31-2015 34:25:37') Oracle always does the safe conversion, which is: DATE to CHARACTER.  

That causes two problems:
1. The database cannot use an index (if any) on the DATE or TIMESTAMP column (so your query may be slow)
2. The comparison is then an ASCII string comparison, not a DATE comparison.  When you want to search for a date range near the end of the year, this character string comparison will *NOT* give you the results you want:
WHERE
    NUMERICLOG.TIMESTAMP BETWEEN  '12-31-2015 21:25:19'  AND  '01-01-2016 02:25:37'

To avoid those problems you need to do an explicit datatype conversion like we suggested, more like this:
WHERE
    NUMERICLOG.TIMESTAMP between TO_TIMESTAMP ('May-23-2015 08:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')
   and  TO_TIMESTAMP ('May-23-2015 11:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')

If however this column is actually not a TIMESTAMP datatype, then you have a different problem.  Then you have a character string that you want Oracle to treat as a date.

We don't know what the datatype of your column is.  That is why we have asked you about that a few times.

it is coming from a text file.  
how to get desc numericlog from sql plus.  Can you guys give me a query.

Oracle does not allow you to query text files directly.  Oracle supports defining an "external table" that is a pointer to a text file, but then the "external table" definition includes the datatype for each column.

In your case, "numericlog" is a synonym to an object outside of your Oracle database.  We don't understand how Oracle's "Heterogeneous Services" are involved here, but the error message you posted indicates that "Heterogeneous Services" are involved.  This is usually to connect to a non-Oracle database (something like: SQL Server, DB2, etc.) but usually not to present a simple text file to Oracle.

>>how to get desc numericlog from sql plus.  Can you guys give me a query.

That is it.  There is no query.

From the SQL prompt, desc numericlog

SQL> desc numericlog

You have given us a single puzzle piece and asked us to keep guessing the picture.  We don't know, so please tell us what the whole picture is.

We're good but not that good.

Honestly, you have some very good people following this question, but we cannot help you without all the pieces.

SQL> desc numericlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 LOGNAME                                   NOT NULL VARCHAR2(32512 CHAR)
 OBJECTNAME                                NOT NULL VARCHAR2(32512 CHAR)
 QUALITY                                   NOT NULL NUMBER(10)
 DATAVALUE                                 NOT NULL FLOAT(49)
 INTERVALS                                 NOT NULL NUMBER(10)
 TIMESTAMP                                 NOT NULL DATE
 AGGREGATE                                 NOT NULL VARCHAR2(32)

So what happens if you use this:

NUMERICLOG.TIMESTAMP  BETWEEN  to_date('06-10-2015 14:25:19', 'mm-dd-yyyy hh24:mi:ss')  AND  to_date('06-10-2015 14:25:37', 'mm-dd-yyyy hh24:mi:ss')

Just for grins try this one:
desc oasuser.numericlog@eh.company.local

ERROR:
ORA-04043: object OASUSER.NUMERICLOG does not exist
ORA-02063: preceding line from EH

I'm far from a Heterogeneous Services (HS)  Expert so I cannot walk you though the specifics of where to look but everything is pointing to a mapping issue.

What do you know about the remote databases setup?  Is the remote table using HS to read the text file?

How is the mapping set up for it?

Take a look at this description of the error message:
http://psoug.org/oraerror/ORA-28527.htm

Try querying the HS_ALL_DD view on the remote database for the table to make sure the mappings are set up correctly.

http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5129.htm#REFRN29089

Please try the TO_DATE rather than the TO_TIMESTAMP version.  The mapping issue may be between DATE and TIMESTAMP.  Especially if there is a different RDBMS on the other end.  We all assumed that since the name of the column was TIMESTAMP that it was also the datatype.  If it is going through Heterogeneous Services, I wouldn't be surprised if it is trying to push that predicate and cannot translate it.

i am getting zero value.

"zero value"

Does that mean: 0 rows returned?  That is better than getting an Oracle error, correct?

That may indicate that the query doesn't find any rows that meet the criteria you entered.  Can you post the exact SQL query that you tried?

(When you posted the results of: "SQL> desc numericlog" that was very helpful.  That allows us to see the actual datatypes for the columns that Oracle is working with.)

ok now i am getting an error.  Here is the query.

SQL> select datavalue, timestamp
  2  from numericlog
  3  where logname = 'LT31110:IO:VALUE,2s_2w' and
  4  timestamp between to_date('10-06-2015 15:00:00', 'dd/mm/yyyy hh24:mi:ss')
  5  and to_date('10-06-2015 15:10:00', 'dd/mm/yyyy hh24:mi:ss')
  6  ;
where logname = 'LT31110:IO:VALUE,2s_2w' and
                *
ERROR at line 3:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using
OLEDB_SQL](pICommand->Execute:rc=-2147217900):Extended = Error getting ItemID.
ORA-02063: preceding 2 lines from EH

Now i got some values with
28783 rows selected.

SQL> select datavalue, timestamp from numericlog
  2  where logname = 'LT31110:IO.VALUE,2s_2w'
  3  and timestamp > to_date('10/06/2015 17:40:00', 'DD/MM/YYYY HH24:MI:SS');

If you are getting valid data returned in some cases now, then I believe your query problem is solved.  Now it looks more like a data problem.

only the date conversion i am still having issue converting between mm/dd/yyyy or from dd/mm/yyyy

it is a client system.  I am try to get the data on crystal reports using SAP BOBJ.   I will try with you date formats tomorrow.