Sample: Stored procedure user exit

create or replace procedure 
PROD.AUDIT_STPROC (
   result       OUT INT,
   returnMsg    OUT CHAR,
   s$entry      IN NUMBER,
   s$srcSysId   IN CHAR,
   s$srcTabId   IN CHAR,
   s$tgtTabId   IN CHAR,
   j$ENTT       IN CHAR,
   a$IDNO       IN NUMBER,
   a$PRICE      IN NUMBER,
   a$DESC       IN CHAR,
   a$LONGDESC   IN CHAR,
   a$TRANSDATE  IN DATE,
   d$IDNO       IN NUMBER,
   d$PRICE      IN NUMBER,
   d$DESC       IN CHAR,
   d$LONGDESC   IN CHAR,
   d$TRANSDATE  IN DATE
   )

The parameters you declare and want to pass to your stored procedure must be valid data types.

The following parameters have been declared in this stored procedure:

s$entry

Retrieves the entry point at which the stored procedure was called. In this example, CDC Replication calls the user exit at every entry point.

s$srcSysId

Retrieves the location of source data.

s$srcTabId

Retrieves the name of the source table.

s$tgtTabId

Retrieves the name of the target table.

j$ENTT

Retrieves the journal code that indicates the type of operation on the source table.

a$

Retrieves the after image of the IDNO, PRICE, DESC, LONGDESC, and TRANSDATE source columns.

d$

Retrieves the transformed data of the IDNO, PRICE, DESC, LONGDESC, and TRANSDATA target columns.

IS
   ENTRYPOINT VARCHAR(50);
   BEGIN
   CASE s$entry
WHEN 16 THEN ENTRYPOINT := 
'User Exit program called Before Insert';
WHEN 1048576 THEN ENTRYPOINT := 
'User Exit program called After Insert';
WHEN 64 THEN ENTRYPOINT := 
'User Exit program called Before Update';
WHEN 4194304 THEN ENTRYPOINT := 
'User Exit program called After Update';
END CASE;

This stored procedure user exit can be invoked from these entry points.

 insert into PROD.AUDIT_TABLE1 
values (
s$entry, s$srcSysId, 
s$srcTabId, s$tgtTabId,
j$ENTT, a$IDNO, a$PRICE, a$DESC, 
a$LONGDESC, a$TRANSDATE, d$IDNO, 
d$PRICE, d$DESC, d$LONGDESC, d$TRANSDATE,
ENTRYPOINT);

This stored procedure user exit will INSERT these values into PROD.AUDIT_TABLE1.

 result := 0;
   returnMsg := 'OK';
END AUDIT_STPROC;

This stored procedure user exit is successful and returns a '0' value.