1 Reply Latest reply: Apr 19, 2013 3:19 PM by rp0428 RSS

    question about sql statement

    1004200
      Hi expert,

      I have following sql statement, function 'hiroc_get_delta_amount1' and 'hiroc_get_delta_amount2' are separately used in select and where subclause. these two function are exactly same, except that there is a inserting log statement inside. for function 'hiroc_get_delta_amount1' , logs are supposed to write into log table1, whereas for function 'hiroc_get_delta_amount2' , logs are supposed to write into log table2. after running this sql, I got data loaded into log table2, however, there is no data loaded into log table1.

      could you please tell me why there is no data in log table2 for function

      1. sql statement;

      select
      pp.policy_premium_pk,
      pp.policy_fk,
      pp.policy_term_fk,
      pp.risk_fk,
      pp.coverage_fk,
      pp.transaction_log_fk,
      pp.coverage_component_code,
      hiroc_rpt_user.hiroc_get_delta_amount1(pp.policy_fk, pp.policy_term_fk, pp.risk_fk, pp.coverage_fk, pp.transaction_log_fk, pp.coverage_component_code),
      pp.rate_period_from_date
      from PRODBKUPDW_MART.rmv_policy_premium pp
      where pp.rate_period_type_code = 'TERM_COVG'
      and pp.coverage_component_code <> 'NETPREM'
      and hiroc_rpt_user.hiroc_get_delta_amount2(pp.policy_fk, pp.policy_term_fk, pp.risk_fk, pp.coverage_fk, pp.transaction_log_fk, pp.coverage_component_code) != 0
      group by pp.policy_premium_pk,
      pp.policy_premium_pk,
      pp.policy_fk,
      pp.policy_term_fk,
      pp.risk_fk,
      pp.coverage_fk,
      pp.transaction_log_fk,
      pp.coverage_component_code,
      pp.rate_period_from_date;

      2. log inserting statement used for both functions:

      (1) function 'hiroc_get_delta_amount1'

      insert into HIROC_RPT_USER.LOG_TEST1 values (v_start, sysdate,
      p_policy_fk,p_policy_term_history_fk,p_risk_fk,p_coverage_fk,p_transaction_log_fk,p_comp_code);
      COMMIT;


      (2) function 'HIROC_GET_DELTA_AMOUNT_1'

      insert into HIROC_RPT_USER.LOG_ZB_TEST_1 values (v_start, sysdate,
      p_policy_fk,p_policy_term_history_fk,p_risk_fk,p_coverage_fk,p_transaction_log_fk,p_comp_code);
      COMMIT;
        • 1. Re: question about sql statement
          rp0428
          WRONG FORUM!

          Welcome to the forum!

          Unfortunately you have posted to the SQL Developer forum which, as the forum title says, is NOT for SQL and PL/SQL questions.

          Please mark this thread ANSWERED and repost your question in the SQL and PL/SQL forum
          PL/SQL

          When you repost your question you should provide the DDL for the two functions, not just a query in them. And unless those functions use the AUTONOMOUS_TRANSACTION pragma and a COMMIT then any inserts to a 'log' table that they do will not be permanent anyway.

          You will also need to provide the row count that the SELECT query returns since if no rows are turned the first function may not even be being called.

          Which raises this question: why are you logging information as part of a SELECT query anyway? What goal are you trying to achieve that justfies the performance penalty you are paying?