Использование коллекции в SELECT в состоянии IN

Question

У меня есть код, используя локально определенную коллекцию:

 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?

Answer 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;