I want to join collection of table type with other tables in following example -
I have a function F_GetPendingFeeds which returns the table collection of type feed_log. I want to join this returning collection with one of the table -
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;
I am receiving error as -
PL/SQL: SQL Statement ignored
PL/SQL: ORA-22905: cannot access rows from a non-nested table
item
Please notice that I want to join collection with table -
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;
Prior to Oracle 12C you could only select from collections that have been created on the server using CREATE TYPE
e.g.
SQL> CREATE TYPE r_feedLog IS OBJECT (foo NUMBER, bar VARCHAR2(20));
SQL> CREATE TYPE t_feedLog IS TABLE OF r_feedLog;
Then remove the declaration of t_feedLog
from your package.
With Oracle 12C it is possible to select from PL/SQL tables defined in a package specification.
you have several errors here. Firstly, to access an array in a TABLE
cast you need to use a SQL array (well, you could use a PL/SQL table still, but that only works for a pipelined function, as Oracle will create the SQL types silently for you; but even in that case its still neater to use a SQL array). so you'd need to do:
SQL> create type r_feedlog is object
2 (
3 feed_log_seq number,
4 processed_dt date,
5 processed_by varchar2(10),
6 create_dt date,
7 created_by varchar2(10)
8 );
9 /
Type created.
SQL> create type t_feedLog as table of r_feedlog;
2 /
Type created.
and then use that and NOT a pl/sql index-by table. secondly
ln_totalRecords t_feedLog;
should be a number not a collection as your selecting count(*) into it. Also :
BULK COLLECT INTO lo_transferFeedDef
should be
BULK COLLECT INTO lo_feedLog
you could have the function as pipelined of course i.e. something like:
CREATE OR REPLACE PACKAGE L_DemoPkg
as
type r_feedlog is record(feed_log_seq number,
processed_dt date,
processed_by varchar2(10),
create_dt date,
created_by varchar2(10));
type t_feedLog is table of r_feedlog;
function F_GetPendingFeeds return t_feedLog pipelined;
procedure P_ProcessFeed;
end;
/
and within the package body:
FUNCTION F_GetPendingFeeds
RETURN t_feedLog pipelined
IS
lo_feedLog r_feedlog;
BEGIN
for r_row in (SELECT feed_log_seq
, processed_dt
, processed_by
, create_dt
, created_by
FROM feed_log
WHERE status_cd = 0)
loop
lo_feedLog.feed_log_seq := r_row.feed_log_seq;
lo_feedLog.processed_dt := r_row.processed_dt;
lo_feedLog.processed_by := r_row.processed_by;
lo_feedLog.create_dt := r_row.create_dt;
lo_feedLog.created_by := r_row.created_by;
pipe row(lo_feedLog);
end loop;
END F_GetPendingFeeds;
within the procedure you could then just:
SELECT COUNT(*)
INTO ln_totalRecords
FROM feed_details t1
, table(F_GetPendingFeeds()) t2
WHERE t1.feed_log_seq = t2.feed_log_seq;
The above was keeping a pl/sql array. if you had the SQL array, the function would be a bit more compact:
FUNCTION F_GetPendingFeeds
RETURN t_feedLog pipelined
IS
BEGIN
for r_row in (SELECT r_feedlog(feed_log_seq
, processed_dt
, processed_by
, create_dt
, created_by) data
FROM feed_log
WHERE status_cd = 0)
loop
pipe row(r_row.data);
end loop;
END F_GetPendingFeeds;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With