Cursor that will get assets count for a particular project display name prefix

assets_count.sql

SET SERVEROUTPUT ON;

DECLARE

    CURSOR c_get_project_id (p_display_name IN VARCHAR2) IS
        SELECT project_id, display_name FROM atgpub.epub_project WHERE display_name LIKE p_display_name AND completion_date IS NOT NULL AND completion_date > sysdate - 5  ORDER BY completion_date DESC;
 
    CURSOR c_get_count_assets ( p_project_id IN VARCHAR2 ) IS
        SELECT COUNT(*) FROM (
        WITH project_details AS
            (SELECT ep.project_id, ad.ID AS workspace_id
            FROM atgpub.epub_project ep
            INNER JOIN atgpub.avm_devline ad ON (ad.NAME = ep.workspace)
            INNER JOIN atgpub.epub_pr_history eph ON (eph.project_id = ep.project_id)
            INNER JOIN atgpub.epub_history eh ON (eh.history_id = eph.history))
        SELECT *
        FROM (
            SELECT pd.project_id,  'category' AS TYPE,
                    AT.category_id AS repository_id,  AT.asset_version, AT.is_head
            FROM project_details pd
            INNER JOIN atgpub.dcs_category AT ON (AT.workspace_id = pd.workspace_id)
            WHERE pd.project_id = p_project_id
            UNION
            SELECT pd.project_id, 'product' AS TYPE,
                    AT.product_id AS repository_id,  AT.asset_version, AT.is_head
            FROM project_details pd
            INNER JOIN atgpub.dcs_product AT ON (AT.workspace_id = pd.workspace_id)
            WHERE pd.project_id = p_project_id
            UNION
            SELECT pd.project_id, 'sku' AS TYPE,
                    AT.sku_id AS repository_id,  AT.asset_version, AT.is_head
            FROM project_details pd
            INNER JOIN atgpub.dcs_sku AT ON (AT.workspace_id = pd.workspace_id)
            WHERE pd.project_id = p_project_id
        ));

    c_get_project_id_cursor_type   c_get_project_id%rowtype;

v_assets_count    NUMBER;

BEGIN

    dbms_output.put_line('projectId, display_name,assets_count');
    OPEN c_get_project_id ('CatalogByR3FeedStibo%');
    LOOP
        FETCH c_get_project_id INTO c_get_project_id_cursor_type;
        EXIT WHEN c_get_project_id%notfound;
        
        OPEN c_get_count_assets (c_get_project_id_cursor_type.project_id);
        LOOP
            FETCH c_get_count_assets INTO v_assets_count;
            EXIT WHEN c_get_count_assets%notfound;
            dbms_output.put_line(c_get_project_id_cursor_type.project_id || ',' || c_get_project_id_cursor_type.display_name || ','|| v_assets_count );
        END LOOP;
        CLOSE c_get_count_assets;
    END LOOP;
    CLOSE c_get_project_id;
  
END;