I'm attempting to do a LISTAGG in iSQL connecting to an oracle DB.
The following query runs fine and returns 106 results.
SELECT DISTINCT owner FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM', 'EXFSYS', 'DBSNMP', 'SQLTXPLAIN', 'XDB', 'WMSYS', 'PERFSTAT', 'OUTLN');
Trying to then LISTAGG I get the following error:
Method 1:
SELECT LISTAGG(DISTINCT owner, ', ') WITHIN GROUP (ORDER BY owner) FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM', 'EXFSYS', 'DBSNMP', 'SQLTXPLAIN', 'XDB', 'WMSYS', 'PERFSTAT', 'OUTLN');
returns:
[ISQL]ERROR: Could not SQLExecute
Method 2:
SELECT LISTAGG(owner, ', ') WITHIN GROUP (ORDER BY owner) FROM (SELECT DISTINCT owner FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM', 'EXFSYS', 'DBSNMP', 'SQLTXPLAIN', 'XDB', 'WMSYS', 'PERFSTAT', 'OUTLN'));
returns:
LISTAGG(OWNER,',')WITHINGROUP(ORDERBYOWNER)
Do functions like LISTAGG
even work from iSQL utilizing an ODBC connection to Oracle.
edit - I found the following looks like it's not allowed? Allowed Functions
Oracle Database Gateway for ODBC assumes that ODBC driver provider that is being used supports the following minimum set of SQL functions:
AVG(exp)
LIKE(exp)
COUNT(*)
MAX(exp)
MIN(exp)
NOT
Per my comments in the quesiton isql cannot access system functions in Oracle.