RE: Force implicit data conversion
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'stephen van linge'" <swvanlinge@xxxxxxxxx>, <dmarc-noreply@xxxxxxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Mar 2015 14:12:33 -0500
Are you talking about
“During SELECT FROM operations, Oracle converts the data from the column to the
type of the target variable.”
?
if so, that is NOT the comparison, but rather a target variable that needs to
receive the value returned by the select.
keyword: target in target variable.
mwf
From: "" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "swvanlinge@xxxxxxxxx"
for DMARC)
Sent: Monday, March 02, 2015 1:52 PM
To: dmarc-noreply@xxxxxxxxxxxxx; mwf@xxxxxxxx; 'Oracle-L Freelists'
Subject: Re: Force implicit data conversion
I'm on Oracle 11gR2 (11.2.0.4) and the documentation
(http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements002.htm#SQLRF00214)
says it'll convert the column to the variable's data type. However, if I make
the variable (date) equal to the timestamp column with ms's either rounded or
chopped, it doesn't pull up the record, which makes me think it might not be
the case.
I'll try the other way around (find a timestamp with 0 ms and see if the date
field will be converted to timestamp). Weird.
Thanks,
Stephen
_____
From: stephen van linge <dmarc-noreply@xxxxxxxxxxxxx>
To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>; "dmarc-noreply@xxxxxxxxxxxxx"
<dmarc-noreply@xxxxxxxxxxxxx>; 'Oracle-L Freelists' <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 2, 2015 10:38 AM
Subject: Re: Force implicit data conversion
No, I need it specifically to be an implicit conversion, aka something Oracle
is doing on its' own.
Also, the trace file does not show this. I just don't see a way to tell if the
data types are changing in the where clause, and if they are, what's changing
to what data type? A way to see this should be sufficient.
Thanks for your help,
Stephen
_____
From: Mark W. Farnham <mwf@xxxxxxxx>
To: dmarc-noreply@xxxxxxxxxxxxx; 'Oracle-L Freelists' <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 2, 2015 10:29 AM
Subject: RE: Force implicit data conversion
do you mean, for example using the cast function?:
SQL> r
1 select
2 --+ gather_plan_statistics
3 * from time_size
4* where cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD')
ID DATETRUNC DATEUNTRU TIMETRUNC
---------- --------- ---------
------------------------------------------------------------------------
TIMEUNTRUNC
---------------------------------------------------------------------------
1 09-MAY-11 09-MAY-11 09-MAY-11 12.00.00.000000 AM
09-MAY-11 09.58.59.000000 AM
3 ~ 09-MAY-11 ~
09-MAY-11 10.58.51.000000 AM
1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM
19-DEC-12 01.33.47.000000 AM
1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM
19-DEC-12 02.01.05.000000 AM
SQL> save q_cast_time
Created file q_cast_time.sql
SQL> @q_xplan
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 9gqfbr6653j64, child number 0
-------------------------------------
select --+ gather_plan_statistics * from time_size where
cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD')
Plan hash value: 1526675273
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 4
|00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| TIME_SIZE | 1 | 1 | 3 (0)| 4
|00:00:00.01 | 8 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CAST(INTERNAL_FUNCTION("TIMEUNTRUNC") AS date)>TO_DATE('
1970-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of stephen van linge (Redacted sender "swvanlinge@xxxxxxxxx" for DMARC)
Sent: Monday, March 02, 2015 1:09 PM
To: Oracle-L Freelists
Subject: Force implicit data conversion
Hi all,
This is an academic question. Given a table A with column B (timestamp data
type) and date parameter C, how can i force an implicit conversion within a
select statement of B to date type?
I'm looking at this:
SELECT *
FROM A
WHERE B = C
and I'm not sure if B is being converted to date, or if C is being converted to
timestamp...
Thanks,
Stephen
Other related posts:
