Hello All,
Orcl Ver: 11g R2.
I am leveraging the Oracle's pipelined table function.
It's working fine for static SQL.
create or replace package test13_pkg as
type r_disc_req is record(disc_line_id number,
req_id number);
type t_disc_req is table of r_disc_req;
function F_GetDiscReq return t_disc_req pipelined;
procedure P_ProcessDiscReq;
end;
CREATE OR REPLACE PACKAGE BODY test13_pkg
AS
FUNCTION F_GetDiscReq
RETURN t_disc_req
PIPELINED
IS
lo_disc_req r_disc_req;
BEGIN
FOR r_row IN (SELECT disc_line_id, req_id
FROM edms_disc_lines_stg
WHERE ROWNUM < 10)
LOOP
lo_disc_req.disc_line_id := r_row.disc_line_id;
lo_disc_req.req_id := r_row.req_id;
PIPE ROW (lo_disc_req);
END LOOP;
END F_GetDiscReq;
PROCEDURE P_ProcessDiscReq
AS
ln_totalRecords NUMBER;
BEGIN
SELECT COUNT (*)
INTO ln_totalRecords
FROM edms_disc_lines_stg t1, TABLE (F_GetDiscReq ()) t2
WHERE t1.disc_line_id = t2.disc_line_id AND t1.req_id = t2.req_id;
DBMS_OUTPUT.put_line (ln_totalRecords);
END;
END;
begin
test13_pkg.P_ProcessDiscReq();
end;
---------------------------------------------------------------------------------------------
How do I leverage it for dynamic sql?
FUNCTION F_GetDiscReq (p_filter1 IN NUMBER, p_filter2 IN NUMBER, p_filter3 IN NUMBER)
RETURN t_disc_req
PIPELINED
IS
lo_disc_req r_disc_req;
l_sql varchar2(4000) := 'SELECT disc_line_id, req_id
FROM edms_disc_lines_stg';
l_where_clause varchar2(4000) := 'WHERE 1 = 1 ';
BEGIN
IF p_filter1 IS NOT NULL THEN
l_where_clause := l_where_clause||'AND filter1 = '||p_filter1;
END IF;
IF p_filter2 IS NOT NULL THEN
l_where_clause := l_where_clause||'AND filter1 = '||p_filter2;
END IF;
IF p_filter3 IS NOT NULL THEN
l_where_clause := l_where_clause||'AND filter1 = '||p_filter3;
END IF;
l_sql := l_sql||l_where_clause;
--I'm stuck here.
FOR r_row IN (l_sql)
LOOP
lo_disc_req.disc_line_id := r_row.disc_line_id;
lo_disc_req.req_id := r_row.req_id;
PIPE ROW (lo_disc_req);
END LOOP;
END F_GetDiscReq;
Could you please guide me here?
Thx
Shank.