News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Customizing GoldenGate processing using SQLEXEC and GETVAL

  • Posted by Gavin Soorma
  • On April 12, 2011
  • 2 Comments
  • colmap, getval, params, sqlexec

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

You need to be logged in to see this part of the content. Please Login to access.
 

2 Comments

AG
  • May 12 2012
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
Gavin Soorma
  • May 14 2012
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.

Leave Reply

Your email address will not be published. Required fields are marked *