Using a Virtual Schema inside a PL/SQL function causes internal server error

Detail name	Value
Changelog Number	17225
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.23, Exasol 8.21.0
Resolution Date	2023-08-04

Description

Using a virtual schema in a query within a PL/SQL function causes an internal server error. The expected behavior is an error message as virtual schemas are currently not supported within PL/SQL functions.

Preparation

CREATE SCHEMA TEST;
OPEN SCHEMA TEST;
--/
CREATE OR REPLACE PYTHON3 ADAPTER SCRIPT test.adapter AS
import json
def adapter_call(request):
    res = json.loads(request)
    if res['type'] == 'createVirtualSchema':
        res['schemaMetadata'] = {'tables':[{'name':'T','columns':[{'name':'C','dataType':{'type':'CHAR','size':2}}]}]}
    elif res['type'] == 'getCapabilities':
        res['capabilities'] = ['LITERAL_STRING']
    elif res['type'] == 'pushdown':
        res['sql'] = "SELECT 'OK'"
    elif res['type'] == 'dropVirtualSchema':
        res['type'] == res['type']
    else:
        raise ValueError('Unsupported callback:' + request)
    return json.dumps(res)
/
;

DROP VIRTUAL SCHEMA IF EXISTS test_vs CASCADE;
CREATE VIRTUAL SCHEMA test_vs USING test.adapter;

--/
CREATE OR REPLACE FUNCTION test.func() RETURNS DECIMAL(10,0)
BEGIN
    RETURN SELECT COUNT(*) FROM test_vs.t;
END
/

Example

-- throws an internal server error
SELECT test.func() AS c; 

Workaround

Do not use virtual schemas inside PL/SQL functions.

Fix

Those queries throw the error “Use of virtual schema in PL/SQL function.”