This content has been marked as final. Show 3 replies
Hi Laurent. I'll ask about the missing feedback section.
I'll also have to ask about the use of this function. The doc says you need to embed it in a CAST function to get the proper results. Still, if it seems to work, maybe we're missing out on a benefit. I'll try to find out more and let you know.
I'm using 10g2 and I've found (empirically) the following:
1. COLLECT (DISTINCT col) works in an SQL statement but not in a PL/SQL cursor.
2. COLLECT (col ORDER BY col DESC) works in an SQL statement
3. COLLECT (DISTINCT col ORDER BY col DESC) does not work (the elements are not unique although they are ordered)
These are not documented Oracle options. Therefore, I recommend using the object-relation operators, e.g.
CAST (COLLECT (DISTINCT col) AS MY_TABLE_TYPE)
The queries are roughly with the same efficiency, but the SET operator is documented and works in PL/SQL.
SET (CAST (COLLECT (col) AS MY_TABLE_TYPE))
Hi Laurent. Yes, this was a doc bug that has been fixed in the 18.104.22.168 doc. Both those clauses are permitted.