An Invalid, or Do You Just Not Want to Work

1 12 2010

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?







Follow

Get every new post delivered to your Inbox.

Join 144 other followers