Использование коллекции в SELECT в состоянии IN
У меня есть код, используя локально определенную коллекцию:
CREATE OR REPLACE FUNCTION WBAR_COUNT_TEST(IB_TAKE_WAITING IN BOOLEAN DEFAULT TRUE,
IB_TAKE_ERROR IN BOOLEAN DEFAULT TRUE,
IB_TAKE_KILLED IN BOOLEAN DEFAULT TRUE,
IB_TAKE_SUCCESS IN BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER IS
SUBTYPE ST_JOB_STATUS IS JOB_STATUS_HISTORY.STATUS%TYPE;
TYPE TTV_STATUSES IS TABLE OF ST_JOB_STATUS;
LTV_STATUSES TTV_STATUSES := TTV_STATUSES();
LN_RETURN_CODE PLS_INTEGER := 0;
PROCEDURE ADD_STATUS(IB_FLAG IN BOOLEAN,
IV_STATUS IN ST_JOB_STATUS) IS
BEGIN
IF IB_FLAG THEN
LTV_STATUSES.EXTEND;
LTV_STATUSES(LTV_STATUSES.LAST) := IV_STATUS;
END IF;
END ADD_STATUS;
BEGIN
ADD_STATUS(IB_TAKE_WAITING, 'WAITING');
ADD_STATUS(IB_TAKE_ERROR, 'ERROR');
ADD_STATUS(IB_TAKE_KILLED, 'KILLED');
ADD_STATUS(IB_TAKE_SUCCESS, 'SUCCESS');
SELECT COUNT(1)
INTO LN_RETURN_CODE
FROM JOB_STATUS_HISTORY H
WHERE H.STATUS IN (LTV_STATUSES);
RETURN LN_RETURN_CODE;
END WBAR_COUNT_TEST;
Я получаю: PLS-00642: local collection types not allowed in SQL statements
Есть ли способ обойти определение TTV_STATUSES
как глобальный тип в Oracle 11g R2
?
1 ответ
Если бы вы не тривиализировали этот пример, я не стал бы беспокоиться о накладных расходах на создание локальных типов, а просто использовал бы динамический SQL. Этот конкретный пример довольно безопасен, так как вы контролируете вставляемый текст, и, учитывая ограничение в 4 переменные, в кеше хранится максимум 15 запросов, поэтому в этом нет ничего особенного:
CREATE OR REPLACE FUNCTION WBAR_COUNT_TEST(IB_TAKE_WAITING IN BOOLEAN DEFAULT TRUE,
IB_TAKE_ERROR IN BOOLEAN DEFAULT TRUE,
IB_TAKE_KILLED IN BOOLEAN DEFAULT TRUE,
IB_TAKE_SUCCESS IN BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER IS
-- Character String large enough to hold maximum string length
LTV_STATUSES VARCHAR2(40) := NULL;
LN_RETURN_CODE PLS_INTEGER := 0;
PROCEDURE ADD_STATUS(IB_FLAG IN BOOLEAN,
IV_STATUS IN ST_JOB_STATUS) IS
BEGIN
IF IB_FLAG THEN
-- Concatenate desired values togather in IN-LIST format
IF LTV_STATUSES IS NULL THEN
LTV_STATUSES := ''''||IV_STATUS||'''';
ELSE
LTV_STATUSES := ','''||IV_STATUS||'''';
END IF;
END IF;
END ADD_STATUS;
BEGIN
ADD_STATUS(IB_TAKE_WAITING, 'WAITING');
ADD_STATUS(IB_TAKE_ERROR, 'ERROR');
ADD_STATUS(IB_TAKE_KILLED, 'KILLED');
ADD_STATUS(IB_TAKE_SUCCESS, 'SUCCESS');
-- Makes sure we don't try and run the query with NO values selected
IF LTV_STATUSES IS NOT NULL THEN
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM JOB_STATUS_HISTORY H WHERE H.STATUS in ('||LTV_STATUSES||')' into LN_RETURN_CODE;
END IF;
RETURN LN_RETURN_CODE;
END WBAR_COUNT_TEST;