I’m trying to build a query for me to use as a template for running data extracts & give me runtime information.

The "@"&RFLD.&RFLE..&REXT.";" section is intended to call any simple SQL query.

So far it works as expected, but I would like it to add the number of rows that was retrieved by the query in @"&RFLD.&RFLE..&REXT.";. For example, @"PathAddressBook.sql" would retrieve 20 rows, @"PathItemMaster.sql" would retrieve 150, @"PathItemBranch.sql" would retrieve 1000, & so on.

The issue I’m at is that I cannot put “@” within a BEGIN - END section, & I wasn’t successful retrieving SQL%ROWCOUNT outside of the same section.

I will greatly appreciate any ideas. Here my full code:

SET SERVEROUTPUT ON SIZE UNLIMITED;
SET ECHO OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;
SET PAGES 0;
SET LINES 32767;
SET TRIMOUT ON;
SET TRIMSPOOL ON;
SET SQLFORMAT CSV;
--SET MARKUP CSV ON DELIMITER ',' QUOTE ON;

    VARIABLE VDATESTR VARCHAR2(30);
    
    SET TERMOUT ON;
    DECLARE
        VDATESTR VARCHAR(30);
        VDATE VARCHAR(20);
    BEGIN
        SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') INTO VDATE FROM DUAL;
            :VDATESTR := VDATE;
        DBMS_OUTPUT.PUT_LINE('Start Date-Time: ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZH'));
    END;
    /

COLUMN RUN_FOLD NEW_VALUE RFLD HEADING Run_Folder;
COLUMN RUN_FILE NEW_VALUE RFLE HEADING Run_File;
COLUMN RUN_EXTN NEW_VALUE REXT HEADING Run_Ext;

    BEGIN
        DBMS_OUTPUT.PUT_LINE('.-.-.-.');
        DBMS_OUTPUT.PUT_LINE('SQL Query File...');
    END;
    /
        ACCEPT PRUN_FOLD CHAR PROMPT '..... Folder Name? ';
        ACCEPT PRUN_FILE CHAR PROMPT '..... File Name (NO extension)? ';    
    SET TERMOUT OFF;
    SELECT RUN_FOLD || DECODE(SUBSTR(RUN_FOLD, -1, 1), '', NULL, '') RUN_FOLD, SUBSTR(RUN_FILE, 1, NVL(NULLIF(INSTR(RUN_FILE, '.'), 0), LENGTH(RUN_FILE) + 1) - 1) RUN_FILE, 'SQL' RUN_EXTN 
        FROM (SELECT TRIM('&PRUN_FOLD.') RUN_FOLD, '&PRUN_FILE.' RUN_FILE FROM DUAL);

SET TERMOUT ON;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('.-.-.-.');
        DBMS_OUTPUT.PUT_LINE('Spool Output File...');
    END;
    /

COLUMN SPL_FOLD NEW_VALUE SFLD HEADING Spool_Folder;
COLUMN SPL_FILE NEW_VALUE SFLE HEADING Spool_File;
COLUMN SPL_EXTN NEW_VALUE SEXT HEADING Spool_Ext;

        ACCEPT SSPL_FOLD CHAR PROMPT '..... Folder Name? ';
        ACCEPT SSPL_FILE CHAR PROMPT '..... File Name (NO extension - CSV only)? ';
    SET TERMOUT OFF;
    SELECT SPL_FOLD || DECODE(SUBSTR(SPL_FOLD, -1, 1), '', NULL, '') SPL_FOLD,
        SUBSTR(SPL_FILE, 1, NVL(NULLIF(INSTR(SPL_FILE, '.'), 0), LENGTH(SPL_FILE) + 1) - 1) || TO_CHAR(SYSTIMESTAMP, '_YYYYMMDD_HH24MITZH') SPL_FILE, 'CSV' SPL_EXTN
            FROM (SELECT TRIM('&SSPL_FOLD.') SPL_FOLD, '&SSPL_FILE.' SPL_FILE FROM DUAL);

SPOOL "&SFLD.&SFLE..&SEXT.";

@"&RFLD.&RFLE..&REXT.";

SPOOL OFF;

    SET TERMOUT ON;
    DECLARE
        VDATESSTR VARCHAR(30);
        VDATES TIMESTAMP;
        VDATEE TIMESTAMP;
        VDATE_DIF INTERVAL DAY TO SECOND;
    BEGIN 
        SELECT TO_TIMESTAMP(:VDATESTR, 'YYYY-MM-DD HH24:MI:SS'), SYSTIMESTAMP INTO VDATES, VDATEE FROM DUAL;
        DBMS_OUTPUT.PUT_LINE('.-.-.-.');
        DBMS_OUTPUT.PUT_LINE('File Name: &SFLE.');
        DBMS_OUTPUT.PUT_LINE('End Date-Time: ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZH'));
        
        VDATE_DIF := VDATEE - VDATES;
        DBMS_OUTPUT.PUT_LINE('Cycle Time: ' || VDATE_DIF);
    END;
    /

SET PAGES 70;
--SET MARKUP CSV OFF;