Just to remeber: Replacing something with new line in Emacs

C-M-5 <type something> <enter> C-q C-j

Credits to the original post.

Oracle GoldenGate: Run SQLEXEC within MAP clause only once, accessing @GETENV parameters

As of version 11.2.3.0 of Oracle GoldenGate there is a restriction for standalone SQLEXEC statements, such statements can't access parameters normally accessed with @GETENV function.

To bypass this restriction we should make SQLEXEC within MAP clause to run only once per every replicat invocation.

This can be achieved using global temporary table as target and filtering upon result of @GETENV( "STATS", "TABLE",.. ) function.

So the setup is the following:

At first create global temporary table

create global temporary table gg.tmp_gg_dummy ( id number( 14, 0 ) ) on commit delete rows;  
alter table gg.tmp_gg_dummy add constraint tmp_gg_dummy_pk primary key ( id );  

This table will be the target table for multiple source tables. As it is created as GTT, we don't have to manually managed inserted records.

Then add the following as the last mapping into your replicat parameter file

map m.*, target gg.tmp_gg_dummy, handlecollisions, colmap ( id = @GETENV ("RECORD", "FILERBA") )   
sqlexec( id set_first_seq_no1, spname pk_replication_accounting.set_first_seq_no,
params(a_fileseqno = @GETENV( "GGFILEHEADER", "FILESEQNO" ) ), afterfilter ),   
filter (@getenv( "STATS", "TABLE", "GG.TMP_GG_DUMMY", "DML" ) = 0), insertallrecords;   

If not added last, this mapping will cause resolution errors.

Let's examine, how it works.

Mapping is resolved using *, so every source table in "M" scheme will match and every operation upon source table will be replicated to gg.tmp_gg_dummy.

INSERTALLRECORDS instructs to transform every operation into INSERT.

HANDLECOLLISIONS prevents PK errors.

As column mapping we need arbitrary value, so we use relative byte address of a record.

The filter will match only for the first DML operation as after replicating one record @getenv( "STATS", "TABLE", "GG.TMP_GG_DUMMY", "DML" ) will return values greater than zero.

And finally we instruct GG to run stored procedure only if the filtering was successful with AFTERFILTER keyword.

Inside SQLEXEC we successfully obtain FILESEQNO value.

Looking at the report file we clearly see that stored procedure was executed only once.

From Table M.EVENTTAIL to GG.EVENTTAIL
       #                   inserts:         26  
       #                   updates:         13  
       #                   deletes:         0  
       #                  discards:         0  
From Table M.EVENTTAIL to GG.TMP_GG_DUMMY:  
       #                   inserts:         0  
       #                   updates:         1  
       #                   deletes:         0  
       #                  discards:         0  
  Stored procedure set_first_seq_no1:  
         attempts:         1  
       successful:         1  

Edit
Seems that I have missed SQLEXEC optional parameter that instructs GG to execute stored procedure only once per invocation: it's EXEC ONCE. Blame on me, but that proves that GG is flexible enough making it possible for one to repeat this functionality with filter clause.
There are more options in addition to ONCE: MAP, TRANSACTION and SOURCEROW so it's worth checking the reference.
Edit 2

More considerate look at EXEC ONCE and it's description Executes the procedure or query once during the course of the Oracle GoldenGate run, upon the first invocation of the associated MAP statement. The results remain valid for as long as the process remains running. made me understand that one should create "* to temp table" mapping anyway, plus if you don't want to populate temp table with useless records during invocation, you should use filter with DML stats, or unconditionally ignore all records for that map statement (that's a viable option).