Oracle 12c interprets SQL in a strange way (Inner Query)

Question

We've recently migrated our Oracle database from 10g to 12c (12.1.0.1.0). After considering an issue with some queries we deceided to further clean up the database and drop all unneeded objects.
Therefore I wrote a query that searches the database DDL for a certain text to show up, where a particular view or function is used.

SELECT 
  object_name, object_type, DBMS_METADATA.GET_DDL(object_type, object_name) as ddl_txt 
FROM user_objects 
WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER') 
  AND UPPER( DBMS_METADATA.GET_DDL(object_type, object_name) ) LIKE upper('%myFunction%')

This results in the following exception:

ORA-31600: invalid input value TYPE BODY for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5746
ORA-06512: at "SYS.DBMS_METADATA", line 8333
ORA-06512: at line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

The exeption occures because we have 'Body Type' objects in our database and they do not provide a ddl with the DBMS_METADATA.GET_DDL(). Running the query below brings out the exact same exception as from the initial query.

select dbms_metadata.get_ddl('TYPE BODY', 'myBodyStringType') from dual

So, I try to create an inner list to first reduce the list of all user object to the once I do really care by rewriting my query as followed:

select
  lst.*,
  DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name) as ddl_txt 
from (
      SELECT 
        object_name, object_type
      FROM user_objects 
      WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER') 
) lst
where upper(DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name)) like upper('%myFunction%')

The funny point is, that it brings out the same exception as shown above. I do not understand why that happens.

I expect Oracle to create the inner list first and consume the DBMS_METADATA.GET_DLL() function only with the remaining values since same values will result in an exception. Why is Oracle doing something else here?

To solve that particular issue I have to add an ORDER BY in the inner query what looks stupid to me. Why do I have to force Oracle to create an inner query first with using an ORDER BY?

select
  lst.*,
  DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name) as ddl_txt 
from (
      SELECT 
        object_name, object_type
      FROM user_objects 
      WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER')
      ORDER BY ROWNUM ASC
) lst
where upper(DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name)) like upper('%myFunction%')

Thanks in advance for any explanation on why that happens? - I have in mind, that the later query was running without any issues on Oracle 10g.
(I'm worried to have the same behavior on other reports that do calculation which might be wrong because of that behavior!).

We still have a ticket opened with Oracle and it looks like they currently cannot provide an answer. - I'll look forward to get an answer by Oracle. — skyfrog, Feb 16 '16 at 13:36
The ticket with Oracle is still open and Oracle does not provide anything usefull so far. The only thing they ask me to do (again, again and again) is to run the query with an old version of the optimizer. That does not solve the problem in our case. - I still hope that Oracle will come up with an answer. — skyfrog, Mar 17 '16 at 16:01

score 2 · Accepted Answer · answered Apr 11 '16 at 09:28

It's a bug. Oracle Support just confirmed to me that the exception occures due to a bug in Oracle Version 12.1.0.1 only.

There are two options to choose from:
1) update to Oracle Version 12.1.0.2 and the bug is fixed.
2) wait a couple weeks for a patch that Oracle is starting to work on soon. The patch will fix this issue in Oracle Version 12.1.0.1.

We did not decided which option we are taking, but I'm very confident that one or the other will work since Oracle Support did reproduce my problem.

score 1 · Answer 2 · answered Oct 29 '15 at 11:25

1

Most likely it's predicate pushing (I can't find a simple explanation sorry)

It's not running the inner recordset first then evaluating the remainder. It's pushing the outside where into the inside derived table. The query plan will tell you for sure.

By using ROWNUM you're forcing it to evaluate the inner recordset first. It's not the ORDER BY its the ROWNUM doing that. Instead of ORDER BY you could also do AND ROWNUM > 0 and it would do the same thing because it has to evaluate every row before it can evaluate the ROWNUM expression.

answered Oct 29 '15 at 11:25

Nick.Mc

18,304
6
61
91

2

You could just add rownum into the select list; you don't need to have it in the where clause. Either way, it has the same effect of forcing Oracle to materialize the subquery first. – Boneist Oct 29 '15 at 11:42
Not "materialize" in the sense that Oracle will build a temporary table for the intermediate result set, but it just evaluates the inner query separately, earlier, than the outer query when using rownum. – Rob van Wijk Oct 29 '15 at 12:52
What happens here sounds obvious to me, but it shouldn't or am I wrong? This currently looks more like a bug in Oracle optimizer to me? – skyfrog Oct 29 '15 at 13:26
@RobvanWijk yup, that's what I meant; thanks for clarifying for me! – Boneist Oct 29 '15 at 16:27
Cool I didn't know about the rownum in the select list thing. – Nick.Mc Oct 29 '15 at 23:42

Oracle 12c interprets SQL in a strange way (Inner Query)

2 Answers2