SET TERM ^ ;

CREATE OR ALTER PROCEDURE SPC_TAMANHO_ARQUIVOS (
    TABELAI VARCHAR(100),
    SERVIDORI VARCHAR(100),
    DBI VARCHAR(100),
    USUARIOI VARCHAR(100),
    SENHAI VARCHAR(100),
    TEST_SIZE DECIMAL(15,4) = 2.5)
RETURNS (
    ID INTEGER,
    TABELA_ID INTEGER,
    ARQUIVO VARCHAR(100),
    TABELA VARCHAR(100),
    DATAHORA TIMESTAMP,
    TAM_BLOB_MB DECIMAL(15,4))
AS
DECLARE VARIABLE DYNAMIC_SQL VARCHAR(1000);
BEGIN
    DYNAMIC_SQL =
        'SELECT
            X.*
        FROM (
            SELECT
                T.ID,
                V.TABELA_ID,
                T.ARQUIVO,
                V.TABELA,
                T.DATAHORA,
                CASE
                    WHEN T.TAMANHO > 0 THEN T.TAMANHO / 1024 / 1024
                    WHEN OCTET_LENGTH(T.CONTEUDO) > 0 THEN CAST(OCTET_LENGTH(T.CONTEUDO) / 1048576.0 AS DECIMAL(15, 4))
                    ELSE NULL
                END AS TAM_BLOB_MB
            FROM TBARQUIVO T
            JOIN TBVINCULO V ON V.ARQUIVO_ID = T.ID
            WHERE V.TABELA = ''' || :TABELAI || '''
        ) X
        WHERE X.TAM_BLOB_MB >= ' || :TEST_SIZE || ' OR X.TAM_BLOB_MB IS NULL
        ORDER BY TAM_BLOB_MB;';

    -- EXECUTE THE EXTERNAL SQL STATEMENT
    FOR EXECUTE STATEMENT (:DYNAMIC_SQL)
        ON EXTERNAL :SERVIDORI || '/3050:' || :DBI
        WITH AUTONOMOUS TRANSACTION
        AS USER :USUARIOI PASSWORD :SENHAI
        INTO :ID, :TABELA_ID, :ARQUIVO, :TABELA, :DATAHORA, :TAM_BLOB_MB
    DO
    BEGIN
        SUSPEND;
    END
END
^

SET TERM ; ^