I’m not sure if you really ask for invalid? At the other end of the discussion I would say ‘do not expect anything, be clear what you want’. Therefore no implicit castings etc.
e.g. your tests with any date in string format will fail in my env as I use ‘YYYY-MM-DD HH24:MI’ as default format.
I would therefore suggest ‘be precise in what you want’.
This will limit the case, when it ‘does not act as expected at the time written’. (and this is my definition of ‘invalid’!)
Yes, of course this will not save against ‘logically invalid’ situations. But are they expected?😉
To be honest, I did not pass ‘TO_DATE(D2) = N3’.

I was very much hoping that someone would argue that the implicit casting of the VARCHAR to DATE makes that SQL statement invalid (I agree). That reminds me of a comment that I made in this book review: https://hoopercharles.wordpress.com/2010/06/06/book-review-oracle-sql-recipes-a-problem-solution-approach/
Invalid in this sense is that the SQL statement might not generate an error when being parsed, might not generate an error when being executed, and is logically valid – but only in the environment where the SQL statement is executed. As you stated, all that it takes is a change in the default date format.

It could be argued that the example provided by Damir should also fail because a VARCHAR cannot be multiplied by a number, yet changing that behavior would break programs that incorrectly define numeric bind variables as VARCHAR2, and probably cause problems for EXPLAIN PLAN when SQL statements are submitted with bind variables.

I am not sure that I understand your comment about “TO_DATE(D2) = N3” – if you are saying that I cannot explicitly convert a value that is already a DATE into a DATE value, and then compare that value to a number, I say why not? Simple algebra: 🙂

TO_DATE(D2) = N3
TO_DATE(D2) - N3 = 0

The result of subtracting a number from a date is… if we assume N3 is 65:

Actually, I saw a couple of recent threads on the OTN forums that included SQL statements with silly syntax like TO_DATE(D2) – that mistake really causes problems for the optimizer’s cardinality estimates. Therefore, it *could* be argued that the SQL statement with “TO_DATE(D2) = ’27-SEP-2010′” in the WHERE clause is invalid not just because of the implicit data type conversion, but also because of the affect on the cardinality estimates that are produced by the cost based optimizer.

An alternative definition of ‘invalid’ is whether it can be included as a static SQL statement in a PL/SQL stored procedure.
That doesn’t mean it can be parsed though, let alone executed. I blogged an example of that with GROUP BY here : http://blog.sydoracle.com/2010/10/fun-with-group-by.html

VPD can also come into play. A statement might be valid when executed with one policy but not with another.

I prefer to be guided by whether a statement is correct or not, with correctness being defined as always producing the desired result. The desired result MIGHT be an exception (and in the PL/SQL world that might be NO_DATA_FOUND or TOO_MANY_ROWS)

I followed the other arguments here also.
Based on these, and some more thinking about it, I’d like the PARSE as the separator.
My idea behind is this: use all your statements with bin variables – therefore you do not know anything about their values at parsing time (well, sometimes you can. but I also can explicit parse a statement without any bind variables provided).
If it is correct, everything which passes this line is not an invalid statement anymore but invalid data?
(in set theory we could argue this tuple of bind variables provided to the statement is not part of the set, (so it is ‘invalid’) and there is no need for the statement to execute this tuple anyhow).
A little bit philosophical now …

Thank you for your points of view on this topic. I was waiting to see if anyone else had an opinion to offer.

Martin, I was close to suggesting that another example of an “invalid” SQL statement is a SQL statement that is written in such a manner that it causes poor performance – that is only a small step away from, “Therefore, it *could* be argued that the SQL statement with “TO_DATE(D2) = ’27-SEP-2010′” in the WHERE clause is invalid not just because of the implicit data type conversion, but also because of the affect on the cardinality estimates that are produced by the cost based optimizer.” I follow your line of thought.

Gary, VPD is something that was completely off my radar. What about relying on public synonyms and the possiblity that an object will exist in the user’s schema with the same object name? Such a situation might make a SQL statement appear to be invalid for only a single user. Yet, if the schema name is prefixed to table names, that could limit the DBA’s ability to consolidate data from multiple databases into a single database with the help of multiple schemas (as would public synonyms I suppose).

My database can not only parse the unhinted and the hinted SQL statement, but it can also execute it. Note that the “Predicate Information” section is printed just below the execution plan – the same result that you received.

Now, let’s go back to the problem where I somehow forgot to collect fixed object statistics:

SELECT
*
FROM
SYS.TAB_STATS$;
no rows selected

Now to collect the fixed object statistics and re-check the above SQL statement:

Note that the “Predicate Information” section is NO LONGER printed just below the execution plan. The same changed behavior is also seen with AUTOTRACE.

From the 10053 trace file, the unhinted query after all transformations:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",'YYYY-MM-DD:HH24:MI:SS') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,'N',2,'Y','N') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,'N',4,'Y','N') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,'N',16,'Y','N') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE 'ORA$BASE' END "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_4"."NAME"<>'_default_auditing_options_' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND "U"."NAME"='PSFT' AND DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW' AND DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND "SYS_ALIAS_4"."NAME"="SYS_ALIAS_2"."NAME" AND '_NEXT_OBJECT'<>"SYS_ALIAS_2"."NAME" AND '_default_auditing_options_'<>"SYS_ALIAS_2"."NAME") UNION ALL (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"='PSFT' AND 'DATABASE LINK'='VIEW' AND 'VALID'='VALID' AND "L"."NAME"="SYS_ALIAS_2"."NAME")) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"('VIEW',"SYS_ALIAS_2"."NAME","U"."NAME") LIKE '%/*+%' AND "U"."NAME"='PSFT' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))

A Google search for ORA-31603 (mostly items found on forums.oracle.com) seems to point to a permissions problem when the error appears with dbms_metadata.get_ddl, but that should not be the case for you if you are executing the query as the SYS (internal) user.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,\'NEXT OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FUNCTION\',9,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE PARTITION\',20,\'INDEX PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA SOURCE\',29,\'JAVA CLASS\',30,\'JAVA RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB SUBPARTITION\',42,NVL( (SELECT \'REWRITE EQUIVALENCE\' "\'REWRITEEQUIVALENCE\'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),\'MATERIALIZED VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATION CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE GROUP\',82,\'MINING MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE FOLDER\',95,\'CUBE BUILD PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",\'YYYY-MM-DD:HH24:MI:SS\') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,\'N\',2,\'Y\',\'N\') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,\'N\',4,\'Y\',\'N\') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,\'N\',16,\'Y\',\'N\') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE \'ORA$BASE\' END "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>\'_NEXT_OBJECT\' AND "SYS_ALIAS_4"."NAME"<>\'_default_auditing_options_\' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))) AND "U"."NAME"=\'PSFT\' AND DECODE("SYS_ALIAS_4"."TYPE#",0,\'NEXT OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FUNCTION\',9,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE PARTITION\',20,\'INDEX PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA SOURCE\',29,\'JAVA CLASS\',30,\'JAVA RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB SUBPARTITION\',42,NVL( (SELECT \'REWRITE EQUIVALENCE\' "\'REWRITEEQUIVALENCE\'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),\'MATERIALIZED VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATION CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE GROUP\',82,\'MINING MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE FOLDER\',95,\'CUBE BUILD PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\')=\'VIEW\' AND DECODE("SYS_ALIAS_4"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\')=\'VALID\') UNION ALL (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",\'DATABASE LINK\' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",\'VALID\' "STATUS",\'N\' "TEMPORARY",\'N\' "GENERATED",\'N\' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"=\'PSFT\' AND \'DATABASE LINK\'=\'VIEW\' AND \'VALID\'=\'VALID\')) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"(\'VIEW\',"SYS_ALIAS_2"."NAME","U"."NAME") LIKE \'%/*+%\' AND "SYS_ALIAS_2"."NAME"="O"."OBJECT_NAME" AND "U"."NAME"=\'PSFT\' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))))

I suspect that the problem might be caused by missing fixed object statistics. However, you might compare the output of a 10053 trace on your system to see if it produces the same final query after transformation. If it does, verify that all of the objects listed in the final query after transformation actually exist. I wonder if there is maybe a missing synonym, missing permission, object name collision (someone created an object with the same name as a public synonym) or possibly one of the data dictionary catalog scripts failed to complete.
Let me know what y

Charles,
I tried to crosscheck on my system.
1st, I have also 798 rows in SYS.TAB_STATS$. So I assume I have no issues with the statistics?
2nd my unhinted select statement has the same Plan hash value: 739349040. I assume this is enough to say, they are the same.
The 10053 trace is uploaded here https://docs.google.com/leaf?id=0B6c1gsEhfcN1MGMxMWUxYTAtYTkzMS00Y2U1LWFjNjEtZTBkMGE5NzJiMmU0&sort=name&layout=list&num=50
Also there the Final query after transformations looks quite similar to me. I have some guesses right now (du you have a user PSFT with a set of views?) but they are not elaborated.
Maybe I manage to simplify the testcase, probably with generated tables to make it more digestive.
Martin

I checked both Linux and Window 11.2.0.1, as well as a freshly created template database that was created by the DBCA – that user does not exist in any of the databases. I had not considered that as a possible problem area, so I repeated the test when connected as the SYS user with a slightly modified version of the SQL statement that you provided:

For some reason, I am not able to access the 10053 trace file that you shared – you do you need to specify permissions so that I am able to access that file? If you leave off the WHERE clause, do you receive the same number of rows from the hinted and unhinted versions of your query?

I tried to lower the permissions on the google doc document as good as I could. but it seems a login is still required. To lower these barriers, I put the file at http://berx.at/traces/PSCRT022_ora_22249_ERROR_SQL.trc .
You will not find the user PSFT in any general Oracle DB. My particular DB was populated with exp/imp from a 9i Peoplesoft database.

Hi Charles,
Some hours without a keyboard but with my children made my brain working again. So I changed the way I attack my problem:
Based on the Final query after transformations here the part of interest:

The dba_views.owner and dba_views.view_name does NOT come from one table, but from a join of OBJ$ and SYS.USER$ (and some others). To get this tupel, Oracle joins USER$ (after the filter for NAME via I_USER1) with I_OBJ5. In this join (access(“O”.”SPARE3″=”U”.”USER#”)) at the same time it has all informations it needs to apply this filter also: filter(“DBMS_METADATA”.”GET_DDL”(‘VIEW’,”O”.”NAME”,”U”.”NAME”) LIKE ‘%TEST%’)
But the filter, if the data returned from I_OBJ5 is really a View (access(“O”.”OBJ#”=”V”.”OBJ#”)) is done later.
There is nothing bad about this action, only DBMS_METADATA.GET_DDL returns an error if the object named by 2nd and 3rd parameter does not match the 1st.
In my case it tries to provide the DDL for “SYS”.”/1000323d_DelegateInvocationHa” (which is a java related object) and fails.

It is interesting to note that the hash value for your plan matches the hash value that I received on 11.2.0.1 on 64 bit Linux, but the hash value for the plan does NOT match the hash value that I received on 11.2.0.1 on 64 bit Windows. With that in mind, what happens when I run the following script on 11.2.0.1 on 64 bit Linux?

That error message seems to match what you received, which suggests that this is a bug in the 11.2.0.1 Linux release which was fixed before the 11.2.0.1 Windows release (I think that the Windows release was launched 3 or 4 months later).

Here is the final query after transformation from 11.2.0.1 Linux:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",'YYYY-MM-DD:HH24:MI:SS') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,'N',2,'Y','N') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,'N',4,'Y','N') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,'N',16,'Y','N') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE 'ORA$BASE' END "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_4"."NAME"<>'_default_auditing_options_' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND "U"."NAME"='SYS' AND DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW' AND DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID') UNION ALL (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"='SYS' AND 'DATABASE LINK'='VIEW' AND 'VALID'='VALID')) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"('VIEW',"SYS_ALIAS_2"."NAME","U"."NAME") LIKE '%/*+%' AND "SYS_ALIAS_2"."NAME"="O"."OBJECT_NAME" AND "U"."NAME"='SYS' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
*************************

It might be worthwhile to search through Metalink to see if there is a one-off patch for Linux that corrects this specific problem, or investigate if it is possible to upgrade to 11.2.0.2. This is certainly an interesting problem.

dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%'
*
ERROR at line 18:
ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in
schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

Last night when I excuted this same SQL statement it retrieved 20 rows. This is the plan that I am currently receiving with AUTOTRACE enabled:

There is only one mention of object “/1000323d_DelegateInvocationHa” on Metalink, in this article:
“Bug 6964025: Exceptions while trying to configure JDBC Connection Validation in GF 3.0.1”

Checking DBA_OBJECTS, I see that “/1000323d_DelegateInvocationHa” is a Java class that is owned by the SYS user, and is also a public synonym – it should have been eliminated due to the o.object_type=’VIEW’ predicate in the WHERE clause. The problem is that the o.object_type=’VIEW’ predicate is not applied until execution plan ID 21, which in the unhinted plan is executed after the call to DBMS_METADATA.GET_DDL on line 8 of the execution plan – not all objects in the database are views, and not all objects are owned by one user.

In the above, I added the OBJECT_TYPE column to the list of columns retrieved by the inline view, and then passed that column into the DBMS_METADATA.GET_DDL call. That change allowed the SQL statement to complete – return 20 rows. Now the final query after transformation is this:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",'YYYY-MM-DD:HH24:MI:SS') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,'N',2,'Y','N') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,'N',4,'Y','N') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,'N',16,'Y','N') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE 'ORA$BASE' END "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_4"."NAME"<>'_default_auditing_options_' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND "U"."NAME"='SYS' AND DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW' AND DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID') UNION ALL (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"='SYS' AND 'DATABASE LINK'='VIEW' AND 'VALID'='VALID')) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"("O"."OBJECT_TYPE","SYS_ALIAS_2"."NAME","U"."NAME") LIKE '%TEST%' AND "SYS_ALIAS_2"."NAME"="O"."OBJECT_NAME" AND "U"."NAME"='SYS' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))

I still do not know why I was able to excute my test SQL statement last night without problem, but cannot execute it today – I thought that computer software was supposed to be logically consistent. 🙂 (Maybe I should spend some more time looking at the execution plans.)

Hmm, I do not have Oracle@Windows available anywhere around, so I tested some of the versions here.
To keep it simpler I used this test-statement:
select
owner,
view_name
from
(select
v.owner, v.view_name
from
dba_views v
where
v.owner =’SYS’ ) vv
where
dbms_metadata.get_ddl(‘VIEW’, vv.view_name, vv.owner) like ‘%TEST%’;

I tested 11.2.0.2 @ Linux 64bit, 11.2.0.1 @ Linux 64bit, 11.1.0.7 @ Solaris 64bit, 10.2.0.4 @ Solaris 64bit, 9.2.0.8 @ Solaris 64bit.
I found this errorstack everywhere. (To not spam this Reply, I put the results into http://berx.at/traces/sql_20110101.txt ).
From my point of view Windows is the alien😉
Do you have any posibility to croscheck with other Versions/OS?
Martin

Good news (or maybe bad news). Today 11.2.0.1 on Windows is now giving me the same error message as on Linux – see my previous two comments.

I only spent a couple of minutes reviewing the execution plan where the SQL statement fails to execute. However, in one of my previous comments I mentioned that the execution plan indicated that Oracle was processing this line in the execution plan on plan ID line 8:

dbms_metadata.get_ddl(‘VIEW’, vv.view_name, vv.owner)

That was processed *before* it filtered the list of objects to eliminate everything that was not a VIEW (that was on plan ID line 21). The owner is filtered on plan ID lines 7, 19 and 31, so line 7 should have been able to restrict the objects to just those owned by SYS when line 8 of the execution plan was executed through a nested loops join. Please try the *fixed* version of the SQL statement that I provided to you:

Do you agree it’s a problem of the function dbms_metadata.get_ddl() in case it gets some parameter combinations it does not expect?

To come back to the topic of this blog: what’s ‘invalid’ here? I don’t think we can blame the optimizer for that, can we?

There might be some ways to circumvent it: you show one, but this still can fail if the optimizer decides to push the filter too ‘deep’.
My ‘no_merge’ hint will also prevent this behavior. But I’d expect a SQL to return the same values (and fail the same way) regardless of any hint?
Oracle could also associate some extended statistics to the function dbms_stats.get_ddl to make it so ‘expensive’ to the optimizer, the optimizer would always try to elaborate the smallest result-set possible before apply the ‘expensive’ get_ddl function to the ‘totally elaborated’ sub-result.
What do you think?
Is there anything we can dwell deeper?
(I’d like to play ma preferred support-game *g*)

You then code a PL/SQL function (maybe someone more skilled with PL/SQL than me can code an example) with a couple of IN parameters. If the word ‘VIEW’ is passed in as a parameter, the PL/SQL function takes one code path, maybe it selects all rows from the view. If the word ‘INDEX’ is passed in as a parameter, the PL/SQL function takes a different code path, maybe selecting a list of columns that are in the index definition. If you then code error checking into the PL/SQL function so that it returns an error message when the user submits an impossible combination (‘VIEW’ and ‘T1_TEST_C1_IND’, for example), that is probably considered good programming practice – possibly better than returning a NULL or random garbage.

I believe that the above is what is happening in your SQL statement. DBA_VIEWS and DBA_OBJECTS are not simple tables, but instead are complex views that are built from several underlying objects. Through an unfortunate optimization, the optimizer has decided to pass the object names through the PL/SQL function (DBMS_METADATA.GET_DDL in your case) before that object list is reduced to just include the list of object names that *are* views.

See if the following will work – it worked OK for me on 11.2.0.1 (64 bit Windows), although it was slow:

I (re-)tested both of your statements – both worked fine.
I assume our observations are now quite consistent. My initial goal to find what’s going on there is met.

You raised a good question about good programming practice. Basically dbms_metadata.get_ddl is supposed to be used in the SELECT part of a query, my utilisation as a filter is quite un-common. So should the (any) function be coded to fulfill the designed situation (and throw an error if anything is wrong) of should it stay any situation where it can be used? Quite a nice question about programming principles.

I made some poor assumptions yesterday when Oracle on Windows was able to execute my test SQL statement and Linux could not execute the test SQL statement – it must be a year 2011 problem because Oracle on Windows cannot execute the test SQL statement today either. 🙂

I did not spend as much time looking at the execution plan that you provided as I should have, instead I wasted time heading in other directions trying to determine why the test SQL statement seemed to work in some cases and not in other cases.

I think that the reason why your SQL statement which had a plan hash value of 739349040 failed is simply because a hash join was performed on plan ID line 4 of the execution plan, rather than a nested loops join. That hash join is what joins the DBA_OBJECTS view to the DBMS_METADATA.GET_DDL function that is on plan ID line 30 of the execution plan – I think that a nested loops join here would have been able to provide the list of objects (drive into the second object) that were SYS owned views, while a hash join is supposed to fully scan the second object listed in the hash join.

In earlier testing, if I did not specify the /*+ NO_QUERY_TRANSFORMATION */ hint in my modified query, it would fail with the same error message that we encountered earlier.

I surmise the bug is in Oracle instrumentation – if it is going to be doing overly helpful stuff transforming deep under the covers, it needs to be better about informing what problem it hit. I don’t see at all why using a supplied package as a filter would be bad programming practice. I’d think it would be good programming practice.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: