Let us see how we can use the SQLEXEC parameter of GoldenGate to execute both an SQL query as well as a stored procedure and then using the @GETVAL function, we can populate a column in the target database which is not present on the source table.
Using a simple example to illustrate this, let us suppose we have two tables – one a lookup table called COUNTRY_CODES which has the country_name and country_id columns and another table called CUSTOMERS which only has the country_id column.
We would like to customize the GoldenGate processing and also display the country_name along with the country_id in the CUSTOMERS table itself on the target database.
Let us look at two ways of doing this – one using a SQL query and the other case where we use a stored procedure and pass a parameter to the stored procedure.
Case 1 – using SQL Query
Here we will use a SQL statement to obtain the value for the column COUNTRY_NAME in the CUSTOMERS table on the target database.
This is our Extract parameter file:
EXTRACT gavinext
USERID idit_prd, PASSWORD idit_prd
RMTHOST indb02, MGRPORT 7809
RMTTRAIL ./dirdat/xx
TABLE idit_prd.customers;
This is the Replicat parameter file:
REPLICAT gavinrep
SETENV (NLS_LANG=”AMERICAN_AMERICA.WE8ISO8859P1″)
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
MAP idit_prd.customers, TARGET idit_prd.customers, &
SQLEXEC (ID lookup, &
QUERY “select country_name cname from country_code where country_id =:v_country_id”,&
PARAMS (v_country_id = country_id)),&
COLMAP (USEDEFAULTS, country_name = @GETVAL (lookup.cname) );
Case 2 – Using a database stored procedure
We have a procedure called GET_COUNTRY which accepts the COUNTRY_ID value as a parameter and returns the COUNTRY_NAME as an OUT parameter.
This is the source code nof the database procedure, GET_COUNTRY:
create or replace procedure get_country
(v_country_id IN number, v_country_name OUT varchar2 )
is
begin
select country_name into v_country_name from country_code where country_id= v_country_id;
end;
/
We we call this procedure from GoldenGate using the SQLEXEC parameter in the Replicat parameter file and we see how by passing the parameter to the variable v_country_id and using the @GETVAL function, the COUNTRY_NAME column is being populated in the target database.
REPLICAT gavinrep
SETENV (NLS_LANG=”AMERICAN_AMERICA.WE8ISO8859P1″)
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
MAP idit_prd.customers, TARGET idit_prd.customers, &
SQLEXEC (SPNAME GET_COUNTRY, &
PARAMS (v_country_id = country_id)),&
COLMAP (USEDEFAULTS, country_name = @getval (GET_COUNTRY.V_COUNTRY_NAME) );
Hi, I am trying the case#2, calling a procedure but failing with the following errors:
create or replace procedure scott.get_script_id
(script_id_p OUT number )
is
begin
select scott.script_id_s.nextval into script_id_p from dual;
end;
/
*******Report File***********
MAP resolved (entry mark.SCRIPT_NEW):
MAP mark.SCRIPT_NEW, TARGET scott.script_new, SQLEXEC (ID seqnum,QUERY “select scott.script_id_s.nextval script_id_p from dual”, PARAMS(script_id_p = script_id)), COLMAP (USEDEFAULTS,
script_id = @GETVAL (seqnum.script_id_p));
Using the following default columns with matching names:
D_SCRIPT_ID=D_SCRIPT_ID, SCRIPT_ID=SCRIPT_ID, SCRIPT_NAME=SCRIPT_NAME
Using the following key columns for target table scott.SCRIPT_NEW: SCRIPT_ID.
2012-05-11 12:50:05 WARNING OGG-00869 OCI Error ORA-01400: cannot insert NULL into (“SCOTT”.”SCRIPT_NEW”.”SCRIPT_NAME”) (status = 1400), SQL .
2012-05-11 12:50:05 WARNING OGG-01004 Aborted grouped transaction on ‘SCOTT.SCRIPT_NEW’, Database error 1400 (ORA-01400: cannot insert NULL into (“SCOTT”.”SCRIPT_NEW”.”SCRIPT_NAME”)).
2012-05-11 12:50:05 WARNING OGG-01003 Repositioning to rba 1747 in seqno 0.
***********
And :
create or replace procedure get_channel_id
(d_script_id_p IN number, channel_id_p OUT number, script_id_p OUT number )
is
begin
select scott.channel_id_s.nextval into channel_id_p from dual;
select script_id into script_id_p from script_new where d_script_id= d_script_id_p;
end;
/
*******Report File***********
MAP resolved (entry DASH.CHANNEL_NEW):
MAP MARK.CHANNEL_NEW, TARGET SCOTT.channel_new, SQLEXEC (spname get_channel_id, PARAMS (d_script_id_p = d_script_id)), COLMAP (USEDEFAULTS, channel_id = @getval (get_channel_id.channel
_id_p), script_id = @getval (get_channel_id.script_id_p));
..
…
2012-05-11 11:52:45 ERROR OGG-00251 Stored procedure/function get_channel_id does not exist (ORA-04043: object get_channel_id does not exist).
Please guide me if I am missing anything in calling these procedures.
Thanks in Advance,
AG
In the first issue check the structure of the table. Have you defined a primary key for the table. Have you used the ADD TRANDATA command to enable suplemental logging.
For the second issue, the GoldenGate schema owner needs to have execute privs on the provedure owned by the application schema owner. If no public synonym exists then fully qualify the procedure name with the schema name as well.