Create a parameterised View with table functions without SQL Macro-CodePudding

We want to create a parameterised View for specific Dates. Since our Oracle Database is Version 16.0.0.0 we are not able to use the handy SQL Macro function.

Therefore we want to create a user defined type and table functions that work as a parameterised view. Something like

CREATE OR REPLACE FUNCTION get_s_ip (p_snapshotdate   DATE,
                                 p_data_source    VARCHAR2 (10 CHAR))
    RETURN s_ip_table
AS
BEGIN
    RETURN (SELECT lgl_nm        AS involved_party_legal_name,
                   ip_id_tp      AS involved_party_type,
                   ip_stc_tp     AS involved_party_structure_type
              FROM (SELECT H_IP_ID_HKEY,
                           lgl_nm,
                           ip_id_tp,
                           ip_stc_tp,
                           RANK ()
                               OVER (PARTITION BY H_IP_ID_HKEY
                                     ORDER BY bsn_eff_strt_dt DESC)    rnk
                      FROM collection.S_ip
                     WHERE     bsn_eff_strt_dt <=
                               TO_DATE (p_snapshotdate, 'YYYYMMDD')
                           AND RCRD_SRC = p_data_source)
             WHERE rnk = 1);
END;
/

I cant get my head around the user defined table and record types and how it works. In online research I only find these table functions that returns records created with curosr and loops.

Is there a better way to achieve the parameterised view workaround?

Thank you in advance

I created the type record and type table as follwed:

CREATE OR REPLACE TYPE COLLECTION.IP AS OBJECT
(
    involved_party_legal_name VARCHAR2 (50),
    involved_party_type VARCHAR2 (50),
    involved_party_structure_type VARCHAR2 (50)
);
/


CREATE OR REPLACE TYPE COLLECTION.S_IP_TABLE AS TABLE OF ip;
/

CodePudding user response：

It would be like this:

CREATE OR REPLACE FUNCTION get_s_ip (p_snapshotdate   DATE,
                             p_data_source    VARCHAR2 (10 CHAR))
   RETURN s_ip_table
AS
  ret s_ip_table;
BEGIN
    SELECT  COLLECTION.IP(lgl_nm, ip_id_tp, ip_stc_tp)
    BULK COLLECT INTO ret
    FROM (SELECT H_IP_ID_HKEY,
                       lgl_nm,
                       ip_id_tp,
                       ip_stc_tp,
                       RANK ()
                           OVER (PARTITION BY H_IP_ID_HKEY
                                 ORDER BY bsn_eff_strt_dt DESC)    rnk
                  FROM collection.S_ip
                 WHERE     bsn_eff_strt_dt <=
                           TRUNC(p_snapshotdate)
                       AND RCRD_SRC = p_data_source)
    WHERE rnk = 1;
    RETURN ret;
END;

But I am not sure whether this what you are really looking for. It does not return a view, it returns an object.

CodePudding user response：

Is there a better way to achieve the parameterised view workaround?

You can return a cursor:

CREATE OR REPLACE PROCEDURE get_s_ip (
  i_snapshotdate IN  COLLECTION.S_IP.bsn_eff_strt_dt%TYPE,
  i_data_source  IN  COLLECTION.S_IP.RCRD_SRC%TYPE,
  i_cursor       OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cursor FOR
    SELECT lgl_nm        AS involved_party_legal_name,
           ip_id_tp      AS involved_party_type,
           ip_stc_tp     AS involved_party_structure_type
    FROM   (
      SELECT lgl_nm,
             ip_id_tp,
             ip_stc_tp,
             RANK () OVER (PARTITION BY H_IP_ID_HKEY
                           ORDER BY bsn_eff_strt_dt DESC) AS rnk
     FROM    collection.S_ip
     WHERE   bsn_eff_strt_dt <= i_snapshotdate
     AND     RCRD_SRC        =  i_data_source
   )
   WHERE rnk = 1;
END;
/

CodePudding user response：

You could also use PIPELINED function returning your record type and inside the function using LOOP on a BULK COLLECT in an array of your RECORD with a limit, so you will have the advantage of the speed of BULK COLLECT but by limiting the size of the chunk you fetch each time, you will be able to limit the impact on the memory consumption. Also read https://blogs.oracle.com/connect/post/bulk-processing-with-bulk-collect-and-forall