Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
SQL Tunning

SQL Tunning

2004-08-03       - By Powell, Mark D

Reply:     1     2     3     4     5     6     7     8     9     10     >>  

Lex, I do not agree with your example. I understand your point but Oracle
has supported using + and - of a number or simple numeric expression as long
as I can remember:

UT1 > l
1 select to_char(trunc(sysdate), 'YYYYMMDD HH24:MI:SS ') as D1,
2 trunc(sysdate) - 22/86400 as D2
3* from dual
UT1 > /

D1 D2
-- ---- ---- ---- -- ------
20040803 00:00:00 02-AUG-04

Looks like Oracle handles it correctly. We have used this syntax from 6.36
to now, 9.2.0.4 on AIX 5.2. I think we need to see the real SQL and Oracle
error to know what problem Wes was encountering.

HTH -- Mark D Powell --

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Lex de Haan
Sent: Tuesday, August 03, 2004 12:01 PM
To: oracle-l@(protected)
Subject: RE: SQL Tunning


you get errors because you cannot divide a DATE by a NUMBER --
trunc(sysdate) returns a DATE, and a DATE minus a NUMBER returns a DATE
again.
so you can only do things like:
trunc(sysdate) - (22/86400)

Kind regards,
Lex.

-- ---- ---- ---- ---- ---- ---- ---- ---- --
visit my website at http://www.naturaljoin.nl
-- ---- ---- ---- ---- ---- ---- ---- ---- --



-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Wes Brooks
Sent: Tuesday, August 03, 2004 16:54
To: oracle-l@(protected)
Subject: Re: SQL Tunning


Hello expert,

Thank you very much for all of your reply.

But in my case, the 1 is variable. I tried the following but it complains
that

SELECT TRUNC(SYSDATE)-22/86400 from dual;

TRUNC(SYS
-- ------
02-AUG-04 <--- Wrong date.

SELECT TRUNC(SYSDATE-22)/86400 from dual;
SELECT (TRUNC(SYSDATE)-22)/86400 from dual;

These two SQL statements gave the error:

ORA-00932 (See ORA-00932.ora-code.com): inconsistent datatypes

If I try this one, it works.

SELECT TRUNC(SYSDATE - 22), TO_DATE(TO_CHAR(TRUNC(SYSDATE-22+1),
'DD-MON-YYYY HH24:MI:SS '),
'DD-MON-YYYY HH24:MI:SS ') from dual;

Is there any way to improve this lengthly statement?

Thanks,
Wes



--- Edgar Chupit <edgar.chupit@(protected) > wrote:

> Hello Wes,
>
> WB > WHERE TRUNC(last_update_date) = TRUNC(SYSDATE - 1)
> WB > How to improve the performance? Do I need to create a new index field
on the table with
> TRUNC(last_update_date)?
>
> You have several opportunities:
> a) you can rewrite your query to this:
> where last_update_date between trunc(sysdate)-1 and
trunc(sysdate)-1/86400
> b) you can create function based index like:
> create index tt_idx on tt(trunc(last_update_date));
> c) you can add column to your table with values from
trunc(last_update_date)
>
> What option to choose mostly depends on your requirements.

-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --