Oracle 12.2 introduced a nice little feature – when selecting from a table-function you no longer have to use the TABLE function:
Before 12.2, here is what you had to do:
SELECT * FROM TABLE([your-collection-or-table-function])
Starting with 12.2 the code becomes shorter:
SELECT * FROM [your-collection-or-table-function]
Example (Oracle 12.2):
SELECT * FROM sys.odcinumberlist(5,2,6,3,78)
Result:
COLUMN_VALUE
------------
5
2
6
3
78
Note that if you try the last query in Oracle 12.1 or any prior release, you will get the following error:
FROM sys.odcinumberlist(5,2,6,3,78)
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
This is an indication that you may need to check your Oracle version:
SELECT * FROM v$version
BANNER ----------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for 32-bit Windows: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.
sqlpatterns 