How to get the complete DDL stored procedure (input/output parameters and body) in Firebird 2.5

I am trying to retrieve a complete DDL stored procedure from Firebird 2.5 database but the result is incomplete.

The original stored procedure source code is:

SET TERM ^ ;

create or alter procedure PREENCHE_EFETIVO
as
declare variable EMPRESA SYS_GLOBAL_EMPRESA;
declare variable FILIAL SYS_GLOBAL_FILIAL;
declare variable NUMERO CONTAS_RECEBER_NUMERO;
declare variable PREFIXO CONTAS_RECEBER_PREFIXO;
declare variable PARCELA CONTAS_RECEBER_PARCELA;
declare variable CLIENTE CLIENTES_CODIGO;
declare variable EFETIVO date;
BEGIN
FOR
    SELECT A.EMPRESA,A.FILIAL,A.NUMERO,A.PREFIXO,A.PARCELA,A.PROPRIETARIO,A.MOVIMENTACAO FROM MOVIMENTOS_BANCARIOS A INNER JOIN
    CONTAS_RECEBER B ON (A.EMPRESA=B.EMPRESA AND A.FILIAL=B.FILIAL AND A.NUMERO=B.NUMERO AND A.PREFIXO=B.PREFIXO
    AND A.PROPRIETARIO=B.CLIENTE AND A.PARCELA=B.PARCELA) INNER JOIN
    MANUTENCAO_COMISSOES C ON ( A.EMPRESA=C.EMPRESA AND A.FILIAL = C.FILIAL AND A.NUMERO=C.NUMERO AND
    A.PREFIXO=C.PREFIXO AND A.PARCELA=C.PARCELA AND A.PROPRIETARIO=C.CLIENTE ) INTO
    :EMPRESA, :FILIAL,:NUMERO, :PREFIXO,:PARCELA, :CLIENTE,:EFETIVO
    DO
    BEGIN
      UPDATE MANUTENCAO_COMISSOES SET EFETIVO=:EFETIVO WHERE
      EMPRESA=:EMPRESA AND FILIAL=:FILIAL AND NUMERO=:NUMERO AND PREFIXO=:PREFIXO AND PARCELA=:PARCELA AND
      CLIENTE=:CLIENTE;
    END
END^
SET TERM ; ^

This is the SQL statement uses to get the source code:

select RDB$PROCEDURE_SOURCE from RDB$PROCEDURES where RDB$SYSTEM_FLAG = 0

This is the result:

declare variable EMPRESA SYS_GLOBAL_EMPRESA;
declare variable FILIAL SYS_GLOBAL_FILIAL;
declare variable NUMERO CONTAS_RECEBER_NUMERO;
declare variable PREFIXO CONTAS_RECEBER_PREFIXO;
declare variable PARCELA CONTAS_RECEBER_PARCELA;
declare variable CLIENTE CLIENTES_CODIGO;
declare variable EFETIVO date;
BEGIN
FOR
    SELECT A.EMPRESA,A.FILIAL,A.NUMERO,A.PREFIXO,A.PARCELA,A.PROPRIETARIO,A.MOVIMENTACAO FROM MOVIMENTOS_BANCARIOS A INNER JOIN
    CONTAS_RECEBER B ON (A.EMPRESA=B.EMPRESA AND A.FILIAL=B.FILIAL AND A.NUMERO=B.NUMERO AND A.PREFIXO=B.PREFIXO
    AND A.PROPRIETARIO=B.CLIENTE AND A.PARCELA=B.PARCELA) INNER JOIN
    MANUTENCAO_COMISSOES C ON ( A.EMPRESA=C.EMPRESA AND A.FILIAL = C.FILIAL AND A.NUMERO=C.NUMERO AND
    A.PREFIXO=C.PREFIXO AND A.PARCELA=C.PARCELA AND A.PROPRIETARIO=C.CLIENTE ) INTO
    :EMPRESA, :FILIAL,:NUMERO, :PREFIXO,:PARCELA, :CLIENTE,:EFETIVO
    DO
    BEGIN
      UPDATE MANUTENCAO_COMISSOES SET EFETIVO=:EFETIVO WHERE
      EMPRESA=:EMPRESA AND FILIAL=:FILIAL AND NUMERO=:NUMERO AND PREFIXO=:PREFIXO AND PARCELA=:PARCELA AND
      CLIENTE=:CLIENTE;
    END
END

What is the correct way to retrieve the original stored procedure? It is needed because we have to do some changes in run time by our application.

Solution

You can't do that directly. Firebird only stores the body of the stored procedure, not the entire original DDL. You will need to reverse engineer it yourself from the metadata stored in the database, or use a library or tool that does it for you. For example, isql -x allows you to extract DDL to recreate a database.

To get information on the parameters of the stored procedure, you need to query RDB$PROCEDURE_PARAMETERS and RDB$FIELDS.

As an aside, if your application needs to do runtime modifications of the stored procedure, you could store the "original" in your application itself.

You may also want to consider reading up on MERGE as a replacement for that stored procedure.