December 1, 2010
Another recent blog article forced me to Stop, Think, … and just about understand (in case you are wondering about the blog title, the definition of invalid).
Consider the following table definition:
CREATE TABLE T3( V1 VARCHAR2(10), D2 DATE, N3 NUMBER); INSERT INTO T3 VALUES( CHR(65), TRUNC(SYSDATE)-65, 65); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3') SELECT * FROM T3; V1 D2 N3 ---------- --------- ---------- A 27-SEP-10 65
—
Which of the following SQL statements are valid SQL statements for the above table (E is not an option)?
A:
SELECT * FROM T3 WHERE TO_NUMBER(V1)=1;
B:
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=TO_NUMBER('A');
C:
SELECT * FROM T3 WHERE TO_NUMBER(V1)=1 AND ROWNUM=2;
D:
SELECT * FROM T3 WHERE TO_NUMBER(V1)=1 AND 1=2;
E:
SELECT * FROM T3 WHERE TO_NUMBER(V1)=1 AND 1=2;
F:
SELECT * FROM T3 WHERE TO_DATE(D2) = '27-SEP-2010';
G:
SELECT * FROM T3 WHERE TO_DATE(D2) = '32-SEP-2010';
H:
SELECT * FROM T3 WHERE D2 = '32-SEP-2010' AND 1=2;
I:
SELECT * FROM T3 WHERE TO_DATE(D2) = N3;
J:
SELECT * FROM T3 WHERE TO_DATE(D2) = N3 AND TO_CHAR(1)='2';
K:
SELECT * FROM T3 WHERE TO_DATE(D2) = N3 AND 1=2;
L:
SELECT * FROM T3 WHERE N3 = 'A';
M:
SELECT * FROM T3 WHERE N3 = '27-SEP-2010';
N:
SELECT * FROM T3 WHERE N3 = '32-SEP-2010';
——————-
Stop and think about it for a moment, which of the above are valid SQL statements?
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
We could use the AUTOTRACE feature in SQL*Plus to tell which are valid, and which are not:
SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=1;
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=TO_NUMBER('A');
SELECT
*
FROM
T3 WHERE TO_NUMBER(V1)=1
AND ROWNUM=2;
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=1
AND 1=2;
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = '27-SEP-2010';
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = '32-SEP-2010';
SELECT
*
FROM
T3
WHERE
D2 = '32-SEP-2010'
AND 1=2;
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = N3;
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = N3
AND TO_CHAR(1)='2';
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = N3
AND 1=2;
SELECT
*
FROM
T3
WHERE
N3 = 'A';
SELECT
*
FROM
T3
WHERE
N3 = '27-SEP-2010';
SELECT
*
FROM
T3
WHERE
N3 = '32-SEP-2010';
The results might look like this:
A:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 TO_NUMBER(V1)=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("V1")=1)
B:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 TO_NUMBER(V1)=TO_NUMBER('A');
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("V1")=TO_NUMBER('A'))
C:
SQL> SELECT
2 *
3 FROM
4 T3 WHERE TO_NUMBER(V1)=1
5 AND ROWNUM=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1538339754
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=2)
3 - filter(TO_NUMBER("V1")=1)
D:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 TO_NUMBER(V1)=1
7 AND 1=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3859223164
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter(TO_NUMBER("V1")=1)
F:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 TO_DATE(D2) = '27-SEP-2010';
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(INTERNAL_FUNCTION("D2"))=TO_DATE(' 2010-09-27
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
G:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 TO_DATE(D2) = '32-SEP-2010';
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(INTERNAL_FUNCTION("D2"))='32-SEP-2010')
H:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 D2 = '32-SEP-2010'
7 AND 1=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3859223164
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("D2"='32-SEP-2010')
I:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = N3; TO_DATE(D2) = N3 * ERROR at line 6: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
J:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = N3 7 AND TO_CHAR(1)='2'; TO_DATE(D2) = N3 * ERROR at line 6: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
K:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = N3 7 AND 1=2; TO_DATE(D2) = N3 * ERROR at line 6: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
L:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 N3 = 'A';
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N3"=TO_NUMBER('A'))
M:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 N3 = '27-SEP-2010';
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N3"=TO_NUMBER('27-SEP-2010'))
N:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 N3 = '32-SEP-2010';
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N3"=TO_NUMBER('32-SEP-2010'))
So, which SQL statements are valid, and which are not? Did you guess correctly? Are you surprised, or still not sure?
How about if we do this just to confirm:
SET AUTOTRACE OFF
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_SQL_OK';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=1;
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=TO_NUMBER('A');
SELECT
*
FROM
T3 WHERE TO_NUMBER(V1)=1
AND ROWNUM=2;
SELECT
*
FROM
T3
WHERE
TO_NUMBER(V1)=1
AND 1=2;
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = '27-SEP-2010';
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = '32-SEP-2010';
SELECT
*
FROM
T3
WHERE
D2 = '32-SEP-2010'
AND 1=2;
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = N3;
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = N3
AND TO_CHAR(1)='2';
SELECT
*
FROM
T3
WHERE
TO_DATE(D2) = N3
AND 1=2;
SELECT
*
FROM
T3
WHERE
N3 = 'A';
SELECT
*
FROM
T3
WHERE
N3 = '27-SEP-2010';
SELECT
*
FROM
T3
WHERE
N3 = '32-SEP-2010';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
The output looks like this:
A:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_NUMBER(V1)=1; TO_NUMBER(V1)=1 * ERROR at line 6: ORA-01722: invalid number
B:
SQL> SELECT
2 *
3 FROM
4 T3
5 WHERE
6 TO_NUMBER(V1)=TO_NUMBER('A');
TO_NUMBER(V1)=TO_NUMBER('A')
*
ERROR at line 6:
ORA-01722: invalid number
C:
SQL> SELECT 2 * 3 FROM 4 T3 WHERE TO_NUMBER(V1)=1 5 AND ROWNUM=2; T3 WHERE TO_NUMBER(V1)=1 * ERROR at line 4: ORA-01722: invalid number
D:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_NUMBER(V1)=1 7 AND 1=2; no rows selected
F:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = '27-SEP-2010'; V1 D2 N3 ---------- --------- ---------- A 27-SEP-10 65
G:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = '32-SEP-2010'; TO_DATE(D2) = '32-SEP-2010' * ERROR at line 6: ORA-01847: day of month must be between 1 and last day of month
H:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 D2 = '32-SEP-2010' 7 AND 1=2; no rows selected
I:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = N3; TO_DATE(D2) = N3 * ERROR at line 6: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
J:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = N3 7 AND TO_CHAR(1)='2'; TO_DATE(D2) = N3 * ERROR at line 6: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
K:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 TO_DATE(D2) = N3 7 AND 1=2; TO_DATE(D2) = N3 * ERROR at line 6: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
L:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 N3 = 'A'; N3 = 'A' * ERROR at line 6: ORA-01722: invalid number
M:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 N3 = '27-SEP-2010'; N3 = '27-SEP-2010' * ERROR at line 6: ORA-01722: invalid number
N:
SQL> SELECT 2 * 3 FROM 4 T3 5 WHERE 6 N3 = '32-SEP-2010'; N3 = '32-SEP-2010' * ERROR at line 6: ORA-01722: invalid number
So, which SQL statements are invalid? Last chance.
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
—-
Good idea, let’s take a look inside the 10046 trace file to see if it provides any critical clues:
WAIT #6: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208311587 WAIT #6: nam='SQL*Net message from client' ela= 409 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208313312 CLOSE #6:c=0,e=0,dep=0,type=1,tim=106208312194 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208313394 WAIT #0: nam='SQL*Net message from client' ela= 1137 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208314551 ===================== PARSING IN CURSOR #2 len=44 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=2056319931 ad='1e367f68' sqlid='94uqsttx91wxv' SELECT * FROM T3 WHERE TO_NUMBER(V1)=1 END OF STMT PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208314830 FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 STAT #2 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #2: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208315213 WAIT #2: nam='SQL*Net break/reset to client' ela= 124 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208315356 CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208312194 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208315926 WAIT #0: nam='SQL*Net message from client' ela= 1229 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208317175 ===================== PARSING IN CURSOR #4 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=4036914934 ad='1e3647e4' sqlid='b34gs4zs9wvrq' SELECT * FROM T3 WHERE TO_NUMBER(V1)=TO_NUMBER('A') END OF STMT PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208317422 FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208317617 WAIT #4: nam='SQL*Net break/reset to client' ela= 125 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208317761 CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208312194 WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208318310 WAIT #0: nam='SQL*Net message from client' ela= 1084 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208319413 ===================== PARSING IN CURSOR #8 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=3419871603 ad='1e3641f4' sqlid='2g02uyv5xf6bm' SELECT * FROM T3 WHERE TO_NUMBER(V1)=1 AND ROWNUM=2 END OF STMT PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194 EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194 WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208319705 FETCH #8:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194 STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=0 us)' STAT #8 id=2 cnt=0 pid=1 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=0 us)' STAT #8 id=3 cnt=0 pid=2 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #8: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208319939 WAIT #8: nam='SQL*Net break/reset to client' ela= 112 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208320070 CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208312194 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208320807 WAIT #0: nam='SQL*Net message from client' ela= 1240 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322066 ===================== PARSING IN CURSOR #3 len=54 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=1521821591 ad='1e363c04' sqlid='3ty5saddba9wr' SELECT * FROM T3 WHERE TO_NUMBER(V1)=1 AND 1=2 END OF STMT PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194 EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194 WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322296 FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194 STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=0 us)' STAT #3 id=2 cnt=0 pid=1 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #3: nam='SQL*Net message from client' ela= 366 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322774 CLOSE #3:c=0,e=0,dep=0,type=1,tim=106208312194 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322830 WAIT #0: nam='SQL*Net message from client' ela= 1249 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208324098 ===================== PARSING IN CURSOR #2 len=56 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=1963953631 ad='1e3634ec' sqlid='du7hngjuhz3fz' SELECT * FROM T3 WHERE TO_DATE(D2) = '27-SEP-2010' END OF STMT PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326129 FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=4161002650,tim=106208312194 WAIT #2: nam='SQL*Net message from client' ela= 182 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326423 FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=7 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326523 WAIT #2: nam='SQL*Net message from client' ela= 411 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208327021 CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208312194 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208327083 WAIT #0: nam='SQL*Net message from client' ela= 28084 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208355185 ===================== PARSING IN CURSOR #4 len=56 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=1226802804 ad='1e362dd4' sqlid='1hqqd0x4jz1mn' SELECT * FROM T3 WHERE TO_DATE(D2) = '32-SEP-2010' END OF STMT PARSE #4:c=15625,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668 EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668 WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208355455 FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668 STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208355669 WAIT #4: nam='SQL*Net break/reset to client' ela= 134 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208355822 CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208339668 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208356681 WAIT #0: nam='SQL*Net message from client' ela= 1259 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208357960 ===================== PARSING IN CURSOR #8 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=2831431159 ad='1e3627e4' sqlid='3u7tx3knc8dgr' SELECT * FROM T3 WHERE D2 = '32-SEP-2010' AND 1=2 END OF STMT PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668 EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668 WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358205 FETCH #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668 STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=0 us)' STAT #8 id=2 cnt=0 pid=1 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #8: nam='SQL*Net message from client' ela= 439 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358756 CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208339668 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358815 WAIT #0: nam='SQL*Net message from client' ela= 1489 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208360322 WAIT #3: nam='SQL*Net break/reset to client' ela= 5 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208363231 WAIT #3: nam='SQL*Net break/reset to client' ela= 133 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208363398 WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208363421 WAIT #3: nam='SQL*Net message from client' ela= 701 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208364163 CLOSE #3:c=0,e=0,dep=0,type=0,tim=106208339668 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208364233 WAIT #0: nam='SQL*Net message from client' ela= 1322 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208365574 WAIT #6: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208366129 WAIT #6: nam='SQL*Net break/reset to client' ela= 126 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208366285 WAIT #6: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208366308 WAIT #6: nam='SQL*Net message from client' ela= 679 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208367019 CLOSE #6:c=0,e=0,dep=0,type=0,tim=106208339668 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208367086 WAIT #0: nam='SQL*Net message from client' ela= 1229 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208368340 WAIT #2: nam='SQL*Net break/reset to client' ela= 4 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208368838 WAIT #2: nam='SQL*Net break/reset to client' ela= 122 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208368989 WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369011 WAIT #2: nam='SQL*Net message from client' ela= 679 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369721 CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208339668 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369786 WAIT #0: nam='SQL*Net message from client' ela= 1010 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208370816 ===================== PARSING IN CURSOR #4 len=37 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=1569184524 ad='1e361d0c' sqlid='54544w5fsgqsc' SELECT * FROM T3 WHERE N3 = 'A' END OF STMT PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668 EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208371121 FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208371337 WAIT #4: nam='SQL*Net break/reset to client' ela= 108 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208371463 CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208370919 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208372094 WAIT #0: nam='SQL*Net message from client' ela= 1072 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208373186 ===================== PARSING IN CURSOR #8 len=47 dep=0 uid=185 oct=3 lid=185 tim=106208370919 hv=2630877256 ad='1e36173c' sqlid='5xks2pufd0028' SELECT * FROM T3 WHERE N3 = '27-SEP-2010' END OF STMT PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 WAIT #8: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208373423 FETCH #8:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 STAT #8 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #8: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208373612 WAIT #8: nam='SQL*Net break/reset to client' ela= 102 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208373732 CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208370919 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208374372 WAIT #0: nam='SQL*Net message from client' ela= 1083 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208375474 ===================== PARSING IN CURSOR #3 len=47 dep=0 uid=185 oct=3 lid=185 tim=106208370919 hv=1567527007 ad='1e36116c' sqlid='c5w7qdxfqx42z' SELECT * FROM T3 WHERE N3 = '32-SEP-2010' END OF STMT PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208375702 FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919 STAT #3 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)' WAIT #3: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208375886 WAIT #3: nam='SQL*Net break/reset to client' ela= 102 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208376006 CLOSE #3:c=0,e=0,dep=0,type=0,tim=106208370919 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208376714 WAIT #0: nam='SQL*Net message from client' ela= 719 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208377453 PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=106208370919 EXEC #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=106208370919
It is a little painful that not even the PARSE call for some of the SQL statements completed, therefore the SQL statement is not written to the 10046 trace file.
Maybe I should have clarified what I was asking for? When we say that a SQL statement is invalid, do we mean:
- It generates an error message when it is executed.
- It generates an error message when it is parsed.
- It generates no error message, yet is logically invalid due to impossible values: 1=2 ; September 32, 2010 ; TO_NUMBER(‘A’)
- Something else?
Recent Comments