01 | December | 2010 | Charles Hooper's Oracle Notes

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)?

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_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';

——————-

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:

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)

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'))

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)

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)

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'))

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')

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')

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

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

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

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'))

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'))

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:

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

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

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

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1
  7    AND 1=2;

no rows selected

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '27-SEP-2010';

V1         D2                N3
---------- --------- ----------
A          27-SEP-10         65

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

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    D2 = '32-SEP-2010'
  7    AND 1=2;

no rows selected

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

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

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

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = 'A';
  N3 = 'A'
       *
ERROR at line 6:
ORA-01722: invalid number

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

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?

Comments : 23 Comments »

Categories : SQL

	Dird on Book Review: Oracle Database 1…
	Charles Hooper on Book Review: Oracle Database 1…
	Dird on Book Review: Oracle Database 1…
	Charles Hooper on Book Review: Oracle Database 1…
	Dird on Book Review: Oracle Database 1…
	jkstill on Brain Teaser: 10046 Extended S…
	Scrabble Word Finder… on The New Order Oracle Coding Ch…
	Charles Hooper on SQL – DENSE_RANK, PERCENT_RANK…
	Irshad on SQL – DENSE_RANK, PERCENT_RANK…
	Charles Hooper on Install Nagios on a Synology D…
	StevH on Install Nagios on a Synology D…
	Charles Hooper on On the Topic of Programming…
	Charles Hooper on On the Topic of Programming…
	David Cunningham on On the Topic of Programming…
	Charles Hooper on How Do You Find Information in…

Charles Hooper's Oracle Notes

An Invalid, or Do You Just Not Want to Work

Categories

Recent Articles

Misc. Articles

Popular Articles

Recent Comments

Blogroll

Hints for Posting Code Sections in Comments

Follow “Charles Hooper's Oracle Notes”

December 2010
M	T	W	T	F	S	S
« Nov				Jan »
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31