Search code examples
oracle-databaseoracle11gisql

isql error could not sqlExecute on listagg


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

Solution

  • Per my comments in the quesiton isql cannot access system functions in Oracle.