Oracle内部加入收藏？

我想在下面的例子中加入表类型与其他表的集合 –

我有一个函数F_GetPendingFeeds,它返回feed_log类型的表集合.
我想加入这个返回的集合与其中一个表 –

CREATE OR REPLACE PACKAGE BODY L_DemoPkg
IS
   TYPE t_feedLog IS TABLE OF feed_log%ROWTYPE
                INDEX BY PLS_INTEGER;

   FUNCTION F_GetPendingFeeds
      RETURN t_feedLog
   IS
   lo_feedLog t_feedLog;
   BEGIN
      SELECT feed_log_seq,processed_dt,processed_by,create_dt,created_by
      BULK COLLECT INTO lo_feedLog
      FROM feed_log
      WHERE status_cd = 0;

      RETURN lo_feedLog;

   EXCEPTION
      WHEN OTHERS THEN
         --TODO: Log Exception
         RAISE;
   END F_GetPendingFeeds;


   PROCEDURE P_ProcessFeed
   IS
   o_pendingFeed t_feedLog;
   ln_totalRecords t_feedLog;

   BEGIN

      -- Get the list of pending feed ids
      o_pendingFeed := F_GetPendingFeeds();

      -- Check if new data is present for processing
      IF o_pendingFeed.COUNT = 0 THEN
         dbms_output.put_line('Feed processing failed. No data found.');
         RETURN;
      END IF;

      SELECT COUNT(*)
      INTO ln_totalRecords
      FROM  feed_details t1,table(o_pendingFeed) t2 --ERROR: ORA-22905: cannot access rows from a non-nested table item
      WHERE t1.feed_log_seq = t2.feed_log_seq;

   EXCEPTION
      WHEN OTHERS THEN
         --TODO: Log Exception
         RAISE;
   END P_ProcessFeed;
END;

我收到错误 –

PL/SQL: SQL Statement ignored
PL/SQL: ORA-22905: cannot access rows from a non-nested table
     item

请注意我想加入表 –

FROM  feed_details t1,table(o_pendingFeed) t2 --ERROR: ORA-22905: cannot access rows from a non-nested table item   WHERE t1.feed_log_seq = t2.feed_log_seq;

解决方法

SQL> CREATE TYPE r_feedLog IS OBJECT (foo NUMBER,bar VARCHAR2(20));
SQL> CREATE TYPE t_feedLog IS TABLE OF r_feedLog;

然后从包中删除t_feedLog的声明.

使用Oracle 12C,可以从包规范中定义的PL / SQL表中进行选择.

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!