2011-09-07

Oracle Goldengate SQLEXEC output parameters

Environment
AIX 5.3.0.0
Oracle database 10.2.0.5
Goldengate 11.1.1.1

Problem
Replicat failed and the report file contains:
ERROR OGG-00353 Could not find stored procedure param (parameter does not exist in sp (seqnumout))

Solution
When using SQLEXEC with SELECT statement, make sure that alias in SELECT statement is the same as output parameter in GETVAL. It is seqnumout in the example below.

MAP HOU.HOUSEHOLD_PERS1, TARGET STG.HOUSEHOLD_PERS1,
SQLEXEC (ID seqnum, QUERY "select STG.HOUSEHOLD_PERS1_SEQ.nextval seqnumout from dual", NOPARAMS),
COLMAP (USEDEFAULTS,
GG_OPERATION = @GETENV ("GGHEADER","OPTYPE"),
GG_BEFORE_AFTER = @GETENV ("GGHEADER","BEFOREAFTERINDICATOR"),
GG_COMMITTIME = @GETENV ("GGHEADER","COMMITTIMESTAMP"),
SEQ# = @GETVAL(seqnum.seqnumout),
TKN_SCN = @TOKEN ("TKN_SCN"),
TKN_RSN = @TOKEN ("TKN_RSN")
);

3 comments:

  1. I am calling Stored procedure using SQLEXEC command. Procedure (replicat side) updates the columns based on the values came from source (extract side). But I am getting following error:

    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

    ReplyDelete
    Replies
    1. Hi AG,
      For mapping SCRIPT_NEW:
      - I think it should be NOPARAMS, when selecting NEXVAL from sequence
      - Try to use explicit column mapping, without USEDEFAULTS
      - I would also disable NOT NULL and other constraints to see what is inserted into the target

      For calling procedure get_channel_id:
      - does GG connect as owner of the procedure? If not, can the GG user see it (synonym/privilege)?

      Delete
  2. Step by step instructions to Solve Oracle Import Error through Remote DBA Services
    At whatever point you will do import into your Oracle 10g server from a similar fare document then you will get a blunder message which imply that "Mistake 1400" and you additionally not ready to embed NULL into section. For this issue in the event that you are searching for help then I recommend you to connect with Cognegic's Online Oracle DB Support or Database Administration for Oracle. We truly take every one of the issues and offering gigantic help in regards to your Oracle Database. Cash doesn't make a difference for us in light of the fact that our principle witticism is to investigate your issue under your financial plan.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete