Forum Stats

  • 3,873,324 Users
  • 2,266,535 Discussions
  • 7,911,506 Comments

Discussions

SQLEXEC Cannot Pass Out Parameter to Filter at Extract Parameter File

user13371969
user13371969 Member Posts: 11
edited Mar 9, 2011 2:40PM in GoldenGate
Hi all,

I would like to do a record existing filtering by lookup demo.xx2 table at extract process in order to reduce the extract record to trail file. After setup, it is fail to pass out the ck_exist.existcnt from the SQL to filter. I tried to use procedure and the result is the result. Am I setup incorrect? Please advice

EXTRACT XTST01
USERID [email protected], PASSWORD oracle
RMTHOST ogg, MGRPORT 7809
RMTTRAIL /ogg/dirdat/r1
TABLE demo.xx,
SQLEXEC (ID ck_exist, QUERY "select count(1) existcnt from demo.xx2 where id_no=:v_id_no", PARAMS (v_id_no = id_no)),
FILTER (@GETVAL (ck_exist.existcnt) > 0);


Thanks for your kindly help.

Jerry
«1

Answers

  • stevencallan
    stevencallan Member Posts: 3,459
    Not sure how to interpret the procedure part where you say "the result is the result."

    The ggs user has select privileges on the other schema? The query works in regular SQL? What if the count is one million? Why spend the time counting a large amount when all the check is doing is looking for something greater than zero (hint: use a stopkey).
  • user13371969
    user13371969 Member Posts: 11
    edited Feb 20, 2011 10:00AM
    Thanks for the reply. The problem I am facing is that I need to lookup another table to check if the id is exists. If the record does not exists at the lookup table, it is discarded. I can do the filtering using SQL query result at REPLICATE parameter file but I cannot do it at EXTRACT parameter file. Is it possible to do it at EXTRACT parameter. I tried to change the SQL query to call procedure and the result is the fail as well.

    Thanks,
    Jerry

    Edited by: user13371969 on Feb 20, 2011 7:00 AM
  • stevencallan
    stevencallan Member Posts: 3,459
    Okay, so what is the error? Troubleshooting steps:

    view report xtst01

    Add REPORT

    Ensures that all errors associated with the stored procedure or query are reported to
    the discard file. The report is useful for tracing the cause of the error. It includes both
    an error description and the value of the parameters passed to and from the procedure
    or query. Oracle GoldenGate continues processing after reporting the error.

    To use a discard file
    Include the DISCARDFILE parameter in the Extract or Replicat parameter file. You must
    supply a name for the file. The parameter has options that control the maximum file size,
    after which the process abends, and whether new content overwrites or appends to existing
    content.
  • Thanks for your kindly help. There is no error found at discard file, ggserv.log and report as well. There is no records insert into trail file at /ogg/dirdat folder when I insert any number to the target table. How can I check if the output parameter from the SQL query clause is correct? Some forums said it cannot pass the output from SQL query at EXTRACT process. Is it correct?

    Jerry
  • stevencallan
    stevencallan Member Posts: 3,459
    Straight from the reference guide:

    "A standalone SQLEXEC statement cannot be used to get input parameters from records
    or pass output parameters. You can use stored procedures and queries with
    parameters by using a SQLEXEC statement within a TABLE or MAP statement."

    As your statement is not a standalone statement, but is within a TABLE statement, you can get values for output parameters.

    Try using TRACE.
  • Thanks a lot. I just tested some cases and found that the problem seems happened at the FIILTER clause. The output parameter cannot pass into Filter e.g FILTER (@GETVAL (ck_exist.existcnt) > 0). Do you have similar experience? Please advice.

    Jerry
  • stevencallan
    stevencallan Member Posts: 3,459
    GETVAL extracts parameters from a stored procedure as input to a FILTER or COLMAP clause. Re-write the query as a stored procedure if you don't think a query works. However:

    GETVAL
    Use the @GETVAL function to extract values from a stored procedure or query so that they
    can be used as input to a FILTER or COLMAP clause of a MAP or TABLE statement.
    Whether or not a parameter value can be extracted with @GETVAL depends upon the
    following:
    1. Whether or not the stored procedure or query executed successfully.
    2. Whether or not the stored procedure or query results have expired.

    If it is still not working, you may want to re-check the original query and ensure it works.
  • Hi stevencallan, Thank you so much for you help. I tried to pass the output parameter to a table column and both procedure or SQL query are works. When I pass the parameter to the FILTER clause, all the records are filtered and no records is returned. Do you have experience on that?

    Jerry
  • stevencallan
    stevencallan Member Posts: 3,459
    So, two options:

    Figure out why the existing filter does not work
    Code around it.

    Since you're looking for count > 0, another way is to include HAVING (from SQL, not GG).

    In this example (going to a flat file, not replicat, so add extract xtst01, sourceistable), I used the employees table with 107 records (from the HR sample schema).

    EXTRACT XTST01
    USERID ggs, PASSWORD ggs
    --RMTHOST oel32, MGRPORT 7809
    --RMTTRAIL ./dirdat/r1
    FORMATASCII, NONAMES, DELIMITER '|'
    EXTFILE ./dirdat/otn.txt, PURGE
    TABLE SRC.EMPLOYEES,
    --get records where the department count is > 10, returns about 79 out out 107
    SQLEXEC (ID ck_exist, QUERY "select count(*) existcnt &
    from src.employees where department_id=:v_id_no having count(*) > 10", PARAMS (v_id_no = department_id));
    --,FILTER (@GETVAL (ck_exist.existcnt) > 10 );


    Output to ./dirdat/otn.txt:

    From Table SRC.EMPLOYEES:
    # inserts: 107
    # updates: 0
    # deletes: 0
    # discards: 0

    Stored procedure ck_exist:
    attempts: 107
    successful: 79
  • stevencallan
    stevencallan Member Posts: 3,459
    edited Feb 21, 2011 3:24PM
    Doh. You need BEFOREFILTER. The default is AFTERFILTER.

    EXTRACT XTST01
    USERID ggs, PASSWORD ggs
    --RMTHOST oel32, MGRPORT 7809
    --RMTTRAIL ./dirdat/r1
    FORMATASCII, NONAMES, DELIMITER '|'
    EXTFILE ./dirdat/otn.txt, PURGE
    --TRACE ./dirrpt/otn_rpt.txt
    --example extracts departments with more than 10 employees
    TABLE SRC.EMPLOYEES,
    SQLEXEC (ID ck_exist, QUERY "select count(*) existcnt from src.employees &
    where department_id=:v_id_no", PARAMS (v_id_no = department_id),BEFOREFILTER),&
    FILTER ( @GETVAL (ck_exist.existcnt > 10) );

    Be sure to tip your waitress on the way out.
This discussion has been closed.