Users often need to identify the owner and the last user who modified specific views or procedures within Data Virtualization (DV). While system tables like ALL_TABLES and ALL_PROCEDURES provide some metadata, retrieving the ownerName and lastModifiedUserName attributes requires interacting with the getResource system API. This article provides SQL scripts to efficiently extract this information for all views and procedures under a specified folder path.

• Create the following custom SQL scripts in DV Studio to retrieve owner and last modified user details for views and procedures.

• Script 1: get_folder_views_metadata
  The input is a folder path. The output would list all the views under that folder, with the owner and the last modified user details

PROCEDURE get_folder_views_metadata(
    IN folderPath VARCHAR(4096),
    OUT resultCursor PIPE (          
        viewName VARCHAR(255),
        viewPath VARCHAR(4096),
        ownerDomain VARCHAR(255),
        ownerName VARCHAR(255),
        lastModifiedUserDomain VARCHAR(255),
        lastModifiedUserName VARCHAR(255)
    )
)
BEGIN
    DECLARE request XML;
    DECLARE response XML;
    DECLARE fault XML; 
    DECLARE fullPath VARCHAR(4096);
    
    DECLARE oDomain VARCHAR(255);
    DECLARE oName VARCHAR(255);
    DECLARE lmDomain VARCHAR(255);
    DECLARE lmName VARCHAR(255);

    FOR v AS 
        SELECT TABLE_NAME, PARENT_PATH 
        FROM /services/databases/system/model/ALL_TABLES 
        WHERE TABLE_TYPE = 'VIEW' 
          AND PARENT_PATH LIKE folderPath || '%'
    DO

        SET fullPath = v.PARENT_PATH || '/' || v.TABLE_NAME;

        SET request = 
            '<resource:getResource xmlns:resource="http://www.compositesw.com/services/system/admin/resource" xmlns:common="http://www.compositesw.com/services/system/util/common">' ||
            '  <resource:path>' || CAST(XMLTEXT(fullPath) AS VARCHAR) || '</resource:path>' ||
            '  <resource:type>TABLE</resource:type>' || 
            '  <resource:detail>FULL</resource:detail>' ||
            '</resource:getResource>';

        CALL /services/webservices/system/admin/resource/operations/getResource(request, response, fault);

        SET oDomain  = CAST(XPATH(response, '//*[local-name()="ownerDomain"]/text()') AS VARCHAR);
        SET oName    = CAST(XPATH(response, '//*[local-name()="ownerName"]/text()') AS VARCHAR);
        SET lmDomain = CAST(XPATH(response, '//*[local-name()="attribute"][*[local-name()="name"]="lastModifiedUserDomain"]/*[local-name()="value"]/text()') AS VARCHAR);
        SET lmName   = CAST(XPATH(response, '//*[local-name()="attribute"][*[local-name()="name"]="lastModifiedUserName"]/*[local-name()="value"]/text()') AS VARCHAR);

        INSERT INTO resultCursor (viewName, viewPath, ownerDomain, ownerName, lastModifiedUserDomain, lastModifiedUserName)
        VALUES (v.TABLE_NAME, fullPath, oDomain, oName, lmDomain, lmName);

    END FOR;

END

• Script 2: get_folder8.procedures_metadata
  The input is a folder path. The output would list all the procedures under that folder, with the owner and the last modified user details

PROCEDURE get_folder_procedures_metadata(
    IN folderPath VARCHAR(4096),
    OUT resultCursor PIPE (
        procedureName VARCHAR(255),
        procedurePath VARCHAR(4096),
        ownerDomain VARCHAR(255),
        ownerName VARCHAR(255),
        lastModifiedUserDomain VARCHAR(255),
        lastModifiedUserName VARCHAR(255)
    )
)
BEGIN
    DECLARE request XML;
    DECLARE response XML;
    DECLARE fault XML; 
    DECLARE fullPath VARCHAR(4096);
    
    DECLARE oDomain VARCHAR(255);
    DECLARE oName VARCHAR(255);
    DECLARE lmDomain VARCHAR(255);
    DECLARE lmName VARCHAR(255);

    FOR p AS 
        SELECT PROCEDURE_NAME, PARENT_PATH 
        FROM /services/databases/system/model/ALL_PROCEDURES 
        WHERE PARENT_PATH LIKE folderPath || '%'
          AND PARENT_PATH NOT LIKE '/system/%' 
          AND PARENT_PATH NOT LIKE '/services/databases/system/%'
    DO

        SET fullPath = p.PARENT_PATH || '/' || p.PROCEDURE_NAME;

        SET request = 
            '<resource:getResource xmlns:resource="http://www.compositesw.com/services/system/admin/resource" xmlns:common="http://www.compositesw.com/services/system/util/common">' ||
            '  <resource:path>' || CAST(XMLTEXT(fullPath) AS VARCHAR) || '</resource:path>' ||
            '  <resource:type>PROCEDURE</resource:type>' ||  -- Changed from TABLE to PROCEDURE
            '  <resource:detail>FULL</resource:detail>' ||
            '</resource:getResource>';

        CALL /services/webservices/system/admin/resource/operations/getResource(request, response, fault);

        SET oDomain  = CAST(XPATH(response, '//*[local-name()="ownerDomain"]/text()') AS VARCHAR);
        SET oName    = CAST(XPATH(response, '//*[local-name()="ownerName"]/text()') AS VARCHAR);
        SET lmDomain = CAST(XPATH(response, '//*[local-name()="attribute"][*[local-name()="name"]="lastModifiedUserDomain"]/*[local-name()="value"]/text()') AS VARCHAR);
        SET lmName   = CAST(XPATH(response, '//*[local-name()="attribute"][*[local-name()="name"]="lastModifiedUserName"]/*[local-name()="value"]/text()') AS VARCHAR);

        INSERT INTO resultCursor (procedureName, procedurePath, ownerDomain, ownerName, lastModifiedUserDomain, lastModifiedUserName)
        VALUES (p.PROCEDURE_NAME, fullPath, oDomain, oName, lmDomain, lmName);

    END FOR;

END

This article provides custom SQL scripts for Data Virtualization Studio to retrieve owner and last modified user details for views and procedures within specified folder paths. These scripts leverage the getResource system API to access resource metadata.