대량 수집이 있는 테이블 함수가 잘못된 데이터 유형을 슬로우함

대량 수집이 있는 테이블 함수가 잘못된 데이터 유형을 슬로우함

함수를 작성하고 있는데 select query에서 사용할 수 있도록 테이블 함수로 포장하고 싶습니다.

다음은 제 유형 선언과 함수의 몇 줄입니다.

CREATE OR REPLACE PACKAGE TYPES 
    AS 
        TYPE CURSORTYPE IS REF CURSOR; 

        TYPE vbugsrec
          IS
             RECORD (
                bug_id     bugs.bug_id%TYPE,
                facility   bugs.facility%TYPE
             );

          TYPE vbugstable
          IS
             TABLE OF vbugsrec
                INDEX BY BINARY_INTEGER;

    END;


      /
    CREATE OR REPLACE PACKAGE BODY CustomQueries
    AS
       FUNCTION pendverifylist (myldapid   IN userpass.ldapalias%TYPE,
                                maxrows    IN PLS_INTEGER:= CustomQueries.maxrecords)
          RETURN types.vbugstable
       IS
          datarows    types.vbugstable; 
          var_useralias userpass.ldapalias%TYPE
                := UPPER (pendverifylist.myldapid) ;

        CURSOR pendverify_cur (
             cursor_var_alias         IN            userpass.ldapalias%TYPE,
             cursor_var_mybugstatus   IN            bugs.bug_status%TYPE,
             cursor_var_wild          IN            qa_list.component%TYPE
          )
          IS
             SELECT   buglist.bug_id, buglist.facility
               FROM   bugs buglist,
                      (SELECT   qa.product, qa.component
                         FROM   qa_list qa, userpass UP
                        WHERE   qa.qa_id = UP.userid
                                AND UP.ldapalias = cursor_var_alias) plist
              WHERE       buglist.bug_status = cursor_var_mybugstatus
                      AND buglist.smr_state IN (SELECT   fs.finalstate
                                                  FROM   finalstates fs)
                      AND buglist.facility = plist.product
                      AND (buglist.product LIKE plist.component
                           OR plist.component = cursor_var_wild);

       BEGIN

          OPEN pendverifylist.pendverify_cur (cursor_var_alias         => pendverifylist.var_useralias,
                                              cursor_var_mybugstatus   => CustomQueries.default_bugstatus,
                                              cursor_var_wild          => CustomQueries.wildcard);

          FETCH pendverifylist.pendverify_cur
             BULK COLLECT INTO   pendverifylist.datarows
             LIMIT LEAST (GREATEST (0, pendverifylist.maxrows),
                          CustomQueries.MAXRECORDS);

          CLOSE pendverifylist.pendverify_cur;

          RETURN pendverifylist.datarows;

       END pendverifylist;

    END CustomQueries;
    /

아래와 같은 TABLE 기능을 사용하고자 할 때 오류가 발생합니다.ORA-00902: 잘못된 데이터 유형

SELECT * FROM TABLE(CUSTOMQUERIES.PENDVERIFYLIST ( 'product', 50 ));

여기서 제가 잘못하고 있는 것을 도와줄 수 있는 사람이 있나요?

잘 부탁드립니다.

일반 SQL에서 허용되지 않는 패키지 수준 유형을 사용하려고 합니다.패키지에 선언된 형식은 PL/SQL 외부(또는 PL/SQL 내의 일반 SQL 문에서도)에 표시되거나 유효하지 않습니다.현재 진행 중인 작업에 대한 요약 버전:

create or replace package types as
    type my_rec_type is record (dummy dual.dummy%type);
    type my_table_type is table of my_rec_type index by binary_integer;
end types;
/

create or replace package p42 as
    function get_table return types.my_table_type;
end p42;
/

create or replace package body p42 as
    function get_table return types.my_table_type is
        my_table types.my_table_type;
    begin
        select * bulk collect into my_table from dual;
        return my_table;
    end get_table;
end p42;
/

select * from table(p42.get_table);

SQL Error: ORA-00902: invalid datatype

패키지 내에서도 테이블 기능을 사용하려는 절차가 있으면 오류가 발생합니다.추가한 경우:

    procedure test_proc is
    begin
        for r in (select * from table(get_table)) loop
            null;
        end loop;
    end test_proc;

패키지 본문 컴파일이 실패할 수 있습니다.ORA-22905: cannot access rows from a non-nested table item.

SQL 명령을 사용하여 유형을 패키지가 아닌 스키마 수준에서 선언해야 합니다.

create type my_obj_type is object (dummy varchar2(1));
/

create type my_table_type is table of my_obj_type;
/

create or replace package p42 as
    function get_table return my_table_type;
end p42;
/

create or replace package body p42 as
    function get_table return my_table_type is
        my_table my_table_type;
    begin
        select my_obj_type(dummy) bulk collect into my_table from dual;
        return my_table;
    end get_table;
end p42;
/

select * from table(p42.get_table);

DUMMY
-----
X

실제로 스키마 레벨에 유형이 있을 필요는 없습니다.기능을 PIPLINEED로 정의하기만 하면 됩니다.

-- DEFINITION IN PCKG HEADER
create or replace PACKAGE "AAA" IS

  TYPE t_record IS RECORD (
   aaa VARCHAR(20 CHAR),
   bbb VARCHAR(50 CHAR),
   ccc VARCHAR(10 CHAR)
  );

  TYPE t_collection is table of t_record;

  FUNCTION get_records(p_in1 DATE, p_in2 DATE) RETURN t_collection PIPELINED;

END AAA;

-- PCKG BODY
create or replace PACKAGE BODY AAA AS

FUNCTION get_records(p_in1 DATE, p_in2 DATE) RETURN t_collection PIPELINED AS 
    CURSOR k1 is SELECT aaa,bbb,ccc FROM table;
BEGIN
  FOR rec IN k1
  LOOP
     pipe row( (rec) );
  END LOOP;    
END get_records
END AAA;

-- CALLING FUNCTION OUTSIDE OF PCKG
select * from TABLE(AAA.get_records(par1, par2));

알렉스 풀 덕분입니다.이것이 제가 결국 가지고 온 것입니다.

CREATE OR REPLACE TYPE vbugsrec
      IS
         OBJECT (
            bug_id     NUMBER(9),
            facility   VARCHAR2(256)
         );

CREATE OR REPLACE TYPE vbugstable
      IS
         TABLE OF vbugsrec;
/
CREATE OR REPLACE PACKAGE BODY CustomQueries
AS
   FUNCTION pendverifylist (myldapid   IN userpass.ldapalias%TYPE,
                            maxrows    IN PLS_INTEGER:= CustomQueries.maxrecords)
      RETURN vbugstable
   IS
      datarows    vbugstable := vbugstable(); 

      var_useralias userpass.ldapalias%TYPE:= UPPER (pendverifylist.myldapid) ;

      TYPE temp_rec IS RECORD (
                          bug_id     bugs.bug_id%TYPE,
                          facility   bugs.facility%TYPE
                       );

      TYPE temp_records
      IS
         TABLE OF temp_rec
            INDEX BY BINARY_INTEGER;

      temporary_records   temp_records;

    CURSOR pendverify_cur (
         cursor_var_alias         IN            userpass.ldapalias%TYPE,
         cursor_var_mybugstatus   IN            bugs.bug_status%TYPE,
         cursor_var_wild          IN            qa_list.component%TYPE
      )
      IS
         SELECT   buglist.bug_id, buglist.facility
           FROM   bugs buglist,
                  (SELECT   qa.product, qa.component
                     FROM   qa_list qa, userpass UP
                    WHERE   qa.qa_id = UP.userid
                            AND UP.ldapalias = cursor_var_alias) plist
          WHERE       buglist.bug_status = cursor_var_mybugstatus
                  AND buglist.smr_state IN (SELECT   fs.finalstate
                                              FROM   finalstates fs)
                  AND buglist.facility = plist.product
                  AND (buglist.product LIKE plist.component
                       OR plist.component = cursor_var_wild);

   BEGIN

      OPEN pendverifylist.pendverify_cur (cursor_var_alias         => pendverifylist.var_useralias,
                                          cursor_var_mybugstatus   => CustomQueries.default_bugstatus,
                                          cursor_var_wild          => CustomQueries.wildcard);

      FETCH pendverifylist.pendverify_cur
         BULK COLLECT INTO   temporary_records
         LIMIT LEAST (GREATEST (0, pendverifylist.maxrows),
                      CustomQueries.MAXRECORDS);

      CLOSE pendverifylist.pendverify_cur;

      IF temporary_records.COUNT <> 0
      THEN
          FOR rec_idx IN temporary_records.FIRST .. temporary_records.LAST
          LOOP
             datarows.EXTEND;
             datarows (datarows.LAST) :=
                vbugsrec (temporary_records (rec_idx).bug_id,
                                temporary_records (rec_idx).facility);
          END LOOP;
      END IF;

      RETURN pendverifylist.datarows;

   END pendverifylist;

END CustomQueries;
/

언급URL : https://stackoverflow.com/questions/17033160/table-function-with-bulk-collection-throws-invalid-datatype