• 30. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
      Vadim Tropashko-Oracle

      Note that the Logging Page tab is missing too. This tab appears when there is erroneous dictionary query; for example in my test case:

       

      false positive.png

       

      But if Bobby exhibited it from the very beginning we wouldn't have fun of 2-page long investigation, wouldn't we?

      • 31. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
        BobbyJ

        I tried your suggestion and renamed the system folder under user appdata. SQLDev recreated a new directory and I had to create new connections. Initially the Statements did not appear here either using the fresh system folder. I then closed and re-opened SQLDev. This time when SQLDev started the Messages - Log window was open and the Statements tab appeared.  It didn't appear the first time when I first opened View>Log. Only after restarting SQLDev with the log window open did the Statements tab appear.


        I then renamed the new system folder to "old" and reverted back to my original system folder. Low and behold this time when SQLDev started the Statements tab appeared since the Messages - Log window was already open. I don't know if this is a bug or not. It just seems inconsistent.

         

        Regarding the original question, now that I am able to see the Statements tab, I was able to see there are 73 parameters for the "select owner,object_name from all_objects..." statement.

        • 32. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
          Vadim Tropashko-Oracle

          This refutes my earlier hypothesis. Any failed (red marked) queries at the Messages - Log page (similar to the one I underlined in green at the previous screenshot)?

          • 33. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
            Glen Conway

            Statements tab only available or JDBC THIN connections, not Thick.

            I recall reading about the Statements tab not being available with OCI/Thick connections at some point in the past, but in my experience it works in 18.1.  And I have had occasion to download some older releases, too.  Here is Statements tab working on OCI/Thick in 4.2 (12.2 instant client accessing 11g XE):

            Capture.JPG

            • 34. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
              BobbyJ

              Only one entry/error on the Logging Page tab, no entries on the Messages tab:

               

              SEVERE4720oracle.dbtools.worksheet.commands.scriptrunner.task.DBCommandRunnerTaskoracle.dbtools.raptor.newscriptrunner.SQLPLUS.runGoToLine(SQLPLUS.java:116)
              • 35. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                Vadim Tropashko-Oracle

                Can you please check if this query with 73 parameters returns the tables that are marked as missing? I understand that executing the entire query manually is too laborious, but does a simplified version with reduced number of question marks returns the table names it is supposed to?

                • 36. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                  BobbyJ

                  I'm sorry, but I'm not following what you are asking. The worksheet contains many queries that are not related in any way. I execute specific queries from the worksheet, as needed. The statement log entry I referenced was the one you listed in your earlier note. The a couple of the tables that are highlighted as "undefined table" are in the parameter output.

                  • 37. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                    _Dylan_

                    After reading thought this post, I noticed that I'm getting what I think is false positives as well inside a package.

                    • In one scenario, when I don't prefix a table in a select with the table owner (same owner as the table) I get an "undefined table" warning when I hover over it. But it compiles fine, and the background SQL doing the check looks like it's returning a row. There are other places where I'm not prefixing with an owner and things are OK. I can add an owner, it just seems inconsistent.
                    • In another scenario, I'm nvl'ing/coalescing a pair of MAX(col_names) with input-parameters/constant. The hover note says "SELECT list inconsistent with GROUP BY; amend GROUP BY clause to: NVL(param, const)". If I add the suggested group by (just to see if it silences things), it then appends additional fields to the suggested list. No matter what I added, the note keeps extending it's recommendation. This could be a parse issue.

                     

                    Including the DDL for the package and a table it's dependent on. Alerts are on package body lines 93 and 234.

                     

                    CREATE TABLE cdc_batch_etl_op_ts_ranges (
                      job_ctrl_id        NUMBER(28,0) NOT NULL,
                      table_name          VARCHAR2(30) NOT NULL,
                      unk_src_op_type_cnt INTEGER NOT NULL,
                      old_etl_op_ts_cnt  INTEGER NOT NULL,
                      row_cnt            INTEGER NOT NULL,
                      dup_cnt            INTEGER NOT NULL,
                      prev_tgt_apply_ts  TIMESTAMP(6) NOT NULL,
                      curr_tgt_apply_ts  TIMESTAMP(6) NOT NULL,
                      min_src_op_ts      TIMESTAMP(6),
                      max_src_op_ts      TIMESTAMP(6),
                      CONSTRAINT pk_cdc_batch_windows PRIMARY KEY (job_ctrl_id, table_name));
                    

                     

                    CREATE OR REPLACE PACKAGE cdc_pkg AS
                    
                      TYPE tab_processing_param_ty IS RECORD (
                        max_src_op_ts       cdc_batch_etl_op_ts_ranges.max_src_op_ts%type,
                        prev_batch_start_ts cdc_batch_etl_op_ts_ranges.prev_tgt_apply_ts%type,
                        curr_batch_start_ts cdc_batch_etl_op_ts_ranges.curr_tgt_apply_ts%type
                      );
                    
                      TYPE cdc_check_results_ty IS RECORD(
                        row_cnt             cdc_batch_etl_op_ts_ranges.row_cnt%type,
                        min_src_op_ts       cdc_batch_etl_op_ts_ranges.min_src_op_ts%type,
                        max_src_op_ts       cdc_batch_etl_op_ts_ranges.max_src_op_ts%type,     
                        unk_src_op_type_cnt cdc_batch_etl_op_ts_ranges.unk_src_op_type_cnt%type,
                        old_etl_op_ts_cnt   cdc_batch_etl_op_ts_ranges.old_etl_op_ts_cnt%type
                      );
                    
                      PROCEDURE gen_merge (
                        p_table_name_in   IN all_tab_columns.table_name%TYPE
                      );
                    
                      PROCEDURE get_tab_processing_params(
                        p_table_name_in               IN all_tab_columns.table_name%TYPE,
                        p_curr_batch_strt_ts_in       IN TIMESTAMP DEFAULT NULL,
                        p_prev_batch_strt_ts_in       IN TIMESTAMP DEFAULT NULL,
                        p_table_processing_prams_out  OUT tab_processing_param_ty
                      );
                    
                      PROCEDURE get_tab_check_results(
                        p_table_name_in     IN all_tab_columns.table_name%TYPE,
                        p_params_in         IN tab_processing_param_ty,
                        p_check_results_out OUT cdc_check_results_ty
                      );
                      
                      PROCEDURE get_dup_count(
                        p_table_name_in         IN all_tab_columns.table_name%TYPE,
                        p_curr_batch_strt_ts_in IN TIMESTAMP DEFAULT NULL,
                        p_prev_batch_strt_ts_in IN TIMESTAMP DEFAULT NULL,
                        p_dup_cnt_out           OUT cdc_batch_etl_op_ts_ranges.dup_cnt%type
                      );
                    
                      PROCEDURE map_results(
                        p_job_ctrl_id_in              IN cdc_batch_etl_op_ts_ranges.job_ctrl_id%type,
                        p_table_name_in               IN all_tab_columns.table_name%TYPE,
                        p_table_processing_prams_in   IN tab_processing_param_ty,
                        p_check_results_in            IN cdc_check_results_ty,
                        p_dup_cnt_in                  IN cdc_batch_etl_op_ts_ranges.dup_cnt%type,
                        p_audit_rec_out               OUT cdc_batch_etl_op_ts_ranges%rowtype
                      );
                    
                      FUNCTION audit_key_exists (
                        p_job_ctrl_id_in in cdc_batch_etl_op_ts_ranges.job_ctrl_id%type,
                        p_table_name_in  in cdc_batch_etl_op_ts_ranges.table_name%type
                      ) RETURN BOOLEAN;
                      
                      PROCEDURE ins_audit_rec(
                        p_audit_rec_in in cdc_batch_etl_op_ts_ranges%rowtype
                    );
                    
                      PROCEDURE upd_audit_rec(
                        p_audit_rec_in in cdc_batch_etl_op_ts_ranges%rowtype
                    );
                    
                      PROCEDURE check_cdc_data (
                        p_table_name_in           IN all_tab_columns.table_name%TYPE,
                        p_curr_batch_strt_ts_in   IN TIMESTAMP DEFAULT NULL,
                        p_prev_batch_strt_ts_in   IN TIMESTAMP DEFAULT NULL  
                      );
                    
                    END cdc_pkg;
                    

                     

                    create or replace PACKAGE BODY cdc_pkg AS
                    -------------------------------------------------------------------------------------------------------------------------------- Global Values
                      gc_src_owner all_tab_columns.owner%type := 'DM_CDC_OWNER';
                      gc_tgt_owner all_tab_columns.owner%type := 'DM_ODS';
                    -------------------------------------------------------------------------------------------------------------------------------- Private Specs
                      FUNCTION get_current_job_ctrl_id
                        RETURN cdc_batch_etl_op_ts_ranges.job_ctrl_id%type
                      AS
                      BEGIN
                        RETURN 0;
                      END get_current_job_ctrl_id;
                    -------------------------------------------------------------------------------------------------------------------------------- Public Specs
                      PROCEDURE gen_merge(p_table_name_in in all_tab_columns.table_name%type) AS
                        lc_tgt_etl_cols constant varchar2(24) := ',ETL_OP_TS,ETL_OP_TYPE';
                        lc_tgt_etl_values constant varchar2(154) := ',FROM_TZ(SRC_OP_TS, ''GMT'') AT TIME ZONE ''US/EASTERN'', DECODE(SRC_OP_TYPE, ''DL'', ''DELETE'', ''UP'', ''UPDATE'', ''PT'', ''INSERT'', ''?'')';
                        lc_merge_template constant varchar2(32767) := 'MERGE INTO <TGT_OWN>.<TAB> TGT
                    USING (
                      SELECT * 
                      FROM <SRC_OWN>.<TAB>
                      WHERE TGT_APPLY_TS >= <LAST_BATCH_START_DT>
                      AND TGT_APPL_TS < <THIS_BATCH_START_DT>
                      ) SRC
                    ON (
                    <JOIN_COND>
                    )
                    WHEN MATCHED THEN UPDATE SET
                    <UPD_LIST>
                    WHEN NOT MATCHED THE INSERT (<INS_LIST>)
                    VALUES <VAL_LIST>';
                    
                        lv_shared_cols dbms_utility.name_array;
                        lv_join_cond varchar2(32767);
                        lv_ins_list varchar2(32767);
                        lv_ins_values varchar2(32767);
                        lv_stmnt clob;
                    
                      BEGIN
                        --Get overlapping columns
                        SELECT column_name
                        BULK COLLECT INTO lv_shared_cols
                        FROM all_tab_columns
                        WHERE owner in (gc_src_owner, gc_tgt_owner)
                        AND table_name = p_table_name_in;
                    
                        -- Generate join conditions based on the primary key column(s)
                        SELECT LISTAGG('  SRC.' || column_name || ' = TGT.' || column_name, ' AND' || chr(10) || '  ') WITHIN GROUP(ORDER BY column_position) AS lst
                        INTO lv_join_cond
                        FROM all_ind_columns
                        WHERE table_owner = gc_tgt_owner
                              AND table_name = p_table_name_in
                              AND index_name LIKE 'PK!_%' ESCAPE '!'
                        GROUP BY index_owner,
                                 index_name;
                    
                        -- Generate the list of columns to insert into.
                        SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY COLUMN_ID) || lc_tgt_etl_cols as lst
                        INTO lv_ins_list
                        FROM all_tab_columns
                        WHERE owner in (gc_src_owner, gc_tgt_owner)
                        AND table_name = p_table_name_in
                        GROUP BY table_name;
                    
                        -- Generate the list of columns being sourced for the input.
                        SELECT LISTAGG('SRC.' || column_name, ',') WITHIN GROUP (ORDER BY COLUMN_ID) || lc_tgt_etl_values as lst
                        INTO lv_ins_values
                        FROM all_tab_columns
                        WHERE owner in (gc_src_owner, gc_tgt_owner)
                        AND table_name = p_table_name_in
                        GROUP BY table_name;
                    
                        lv_stmnt := replace(lc_merge_template, '<TGT_OWN>', gc_tgt_owner);
                        lv_stmnt := replace(lv_stmnt, '<SRC_OWN>', gc_src_owner);
                        lv_stmnt := replace(lv_stmnt, '<TAB>', p_table_name_in);
                        lv_stmnt := replace(lv_stmnt, '<JOIN_COND>', lv_join_cond);
                        lv_stmnt := replace(lv_stmnt, '<INS_LIST>', lv_ins_list);
                        lv_stmnt := replace(lv_stmnt, '<INS_VALUES>', lv_ins_values);
                    
                        DBMS_OUTPUT.PUT_LINE(lv_stmnt);
                    
                      END gen_merge;
                    --------------------------------------------------------------------------------------------------------------------------------  
                      PROCEDURE get_tab_processing_params(
                        p_table_name_in               IN all_tab_columns.table_name%TYPE,
                        p_curr_batch_strt_ts_in       IN TIMESTAMP DEFAULT NULL,
                        p_prev_batch_strt_ts_in       IN TIMESTAMP DEFAULT NULL,
                        p_table_processing_prams_out  OUT tab_processing_param_ty
                      )
                      AS
                        -- Floors SYSDATE to a Sunday. M-Sa will return "last sunday" and Su will return "today"
                        lc_latest_sunday constant date := next_day(trunc(sysdate - case when trim(to_char(sysdate, 'DAY')) = 'SUNDAY' THEN 1 ELSE 7 END, 'IW'), 'SUNDAY');
                        lc_curr_job_ctrl_id CONSTANT cdc_batch_etl_op_ts_ranges.job_ctrl_id%type := get_current_job_ctrl_id;    
                      BEGIN
                        SELECT
                        -- There is a check against the max existing src_op_ts processed. If nothing has been processed for this table yet, put the
                        -- value into the future so that everything in the current batch falls below it.
                        NVL(
                          MAX(max_src_op_ts), 
                          TO_TIMESTAMP('31-DEC-9999', 'DD-MON-YYYY')
                        ) AS max_src_op_ts,
                        -- If no input has been provided, and this table has not yet been processed, put the last batch start in the past so that everything will be picked up.
                        COALESCE(
                          p_prev_batch_strt_ts_in, 
                          MAX(curr_tgt_apply_ts), 
                          TO_TIMESTAMP('01-JAN-1800', 'DD-MON-YYYY')
                        ) AS prev_batch_start_ts,
                        -- If no batch start date has been provided then floor sysdate to the previous Sunday.
                        NVL(
                          p_curr_batch_strt_ts_in,
                          lc_latest_sunday
                        ) AS curr_batch_start_ts
                        INTO p_table_processing_prams_out
                        FROM etl_owner.cdc_batch_etl_op_ts_ranges
                        WHERE table_name = p_table_name_in
                        AND job_ctrl_id < lc_curr_job_ctrl_id;
                        
                        DBMS_OUTPUT.PUT_LINE(
                          to_char(p_table_processing_prams_out.max_src_op_ts, 'DD-MON-YYYY') || ',' || 
                          to_char(p_table_processing_prams_out.curr_batch_start_ts, 'DD-MON-YYYY') || ',' || 
                          to_char(p_table_processing_prams_out.prev_batch_start_ts, 'DD-MON-YYYY')
                        );
                        
                      END get_tab_processing_params;
                    --------------------------------------------------------------------------------------------------------------------------------
                      PROCEDURE get_tab_check_results(
                        p_table_name_in     IN all_tab_columns.table_name%TYPE,
                        p_params_in         IN tab_processing_param_ty,
                        p_check_results_out OUT cdc_check_results_ty
                      )
                      AS
                        -- Dynamic CDC source select where
                        -- :1 is the txn timestamp High-water-mark for SRC_OP_TS for this table.
                        -- :2 is the previous batch start day (usually last Sunday 00:00)
                        -- :3 is the current batch start day (usually this Sunday 00:00)
                        lc_stmnt constant varchar2(32767) := q'$SELECT 
                      COUNT(*) AS ROW_CNT,
                      MIN(SRC_OP_TS) AS MIN_SRC_OP_TS,
                      MAX(SRC_OP_TS) AS MAX_SRC_OP_TS,
                      COUNT(CASE WHEN NVL(SRC_OP_TYPE, '?') NOT IN ('DL', 'UP', 'PT') THEN 1 END) AS UNK_SRC_OP_TYPE_CNT,
                      COUNT(CASE WHEN SRC_OP_TS >= :1 THEN 1 END) AS OLD_ETL_OP_TS_CNT
                    FROM DM_CDC_OWNER.$' || p_table_name_in || q'$
                    WHERE TGT_APPLY_TS >= :2
                    AND TGT_APPLY_TS < :3$';
                      
                      BEGIN
                        EXECUTE IMMEDIATE lc_stmnt INTO p_check_results_out 
                          USING p_params_in.max_src_op_ts, p_params_in.prev_batch_start_ts, p_params_in.curr_batch_start_ts;
                          
                        DBMS_OUTPUT.PUT_LINE(lc_stmnt);   
                    
                        DBMS_OUTPUT.PUT_LINE(
                          p_check_results_out.row_cnt || ',' || 
                          to_char(p_check_results_out.min_src_op_ts, 'DD-MON-YYYY') || ',' || 
                          to_char(p_check_results_out.max_src_op_ts, 'DD-MON-YYYY') || ',' || 
                          p_check_results_out.unk_src_op_type_cnt || ',' || 
                          p_check_results_out.old_etl_op_ts_cnt
                        );
                      END get_tab_check_results;
                    --------------------------------------------------------------------------------------------------------------------------------
                      PROCEDURE get_dup_count(
                        p_table_name_in         IN all_tab_columns.table_name%TYPE,
                        p_curr_batch_strt_ts_in IN TIMESTAMP DEFAULT NULL,
                        p_prev_batch_strt_ts_in IN TIMESTAMP DEFAULT NULL,
                        p_dup_cnt_out           OUT cdc_batch_etl_op_ts_ranges.dup_cnt%type
                      )
                      AS
                        lv_stmnt varchar2(32767);
                      BEGIN
                        SELECT 'SELECT COUNT(*)
                    FROM (
                      SELECT 
                        ' || column_names || ',
                        SRC_OP_TS,
                        COUNT(*)
                      FROM DM_CDC_OWNER.' || table_name || '
                      WHERE TGT_APPLY_TS >= :1
                      AND TGT_APPLY_TS < :2
                      GROUP BY
                        ' || column_names || ',
                        SRC_OP_TS
                      HAVING COUNT(*) > 1
                    )'
                        INTO lv_stmnt
                        FROM (
                          SELECT 
                            table_owner, 
                            table_name, 
                            index_name, 
                            LISTAGG(column_name,',' || chr(10) || '    ') WITHIN GROUP(ORDER BY column_position) AS column_names
                          FROM all_ind_columns
                          WHERE index_owner = gc_tgt_owner
                                AND table_owner = gc_tgt_owner
                                AND table_name = p_table_name_in
                                AND index_name LIKE 'PK!_%' ESCAPE '!'
                          GROUP BY table_owner,
                                   table_name, 
                                   index_name
                        );
                        dbms_output.put_line(lv_stmnt);
                        execute immediate lv_stmnt into p_dup_cnt_out using p_prev_batch_strt_ts_in, p_curr_batch_strt_ts_in;
                        
                      END get_dup_count;
                    --------------------------------------------------------------------------------------------------------------------------------
                      PROCEDURE map_results(
                        p_job_ctrl_id_in              IN cdc_batch_etl_op_ts_ranges.job_ctrl_id%type,
                        p_table_name_in               IN all_tab_columns.table_name%TYPE,
                        p_table_processing_prams_in   IN tab_processing_param_ty,
                        p_check_results_in            IN cdc_check_results_ty,
                        p_dup_cnt_in                  IN cdc_batch_etl_op_ts_ranges.dup_cnt%type,
                        p_audit_rec_out               OUT cdc_batch_etl_op_ts_ranges%rowtype
                      )
                      AS
                      BEGIN
                    
                        p_audit_rec_out.job_ctrl_id         := p_job_ctrl_id_in;
                        p_audit_rec_out.table_name          := p_table_name_in;
                        p_audit_rec_out.unk_src_op_type_cnt := p_check_results_in.unk_src_op_type_cnt;
                        p_audit_rec_out.old_etl_op_ts_cnt   := p_check_results_in.old_etl_op_ts_cnt;
                        p_audit_rec_out.row_cnt             := p_check_results_in.row_cnt;
                        p_audit_rec_out.dup_cnt             := p_dup_cnt_in;
                        p_audit_rec_out.prev_tgt_apply_ts   := p_table_processing_prams_in.prev_batch_start_ts;
                        p_audit_rec_out.curr_tgt_apply_ts   := p_table_processing_prams_in.curr_batch_start_ts;
                        p_audit_rec_out.min_src_op_ts       := p_check_results_in.min_src_op_ts;
                        p_audit_rec_out.max_src_op_ts       := p_check_results_in.max_src_op_ts;
                    
                      END map_results;
                    --------------------------------------------------------------------------------------------------------------------------------
                      FUNCTION audit_key_exists (
                        p_job_ctrl_id_in in cdc_batch_etl_op_ts_ranges.job_ctrl_id%type,
                        p_table_name_in  in cdc_batch_etl_op_ts_ranges.table_name%type
                      ) RETURN BOOLEAN
                      AS
                        lv_cnt pls_integer;
                      BEGIN
                        SELECT COUNT(*)
                        INTO lv_cnt
                        FROM cdc_batch_etl_op_ts_ranges
                        WHERE table_name = p_table_name_in
                        AND job_ctrl_id = p_job_ctrl_id_in;
                        
                        RETURN CASE WHEN lv_cnt = 0 THEN FALSE ELSE TRUE END;
                      END audit_key_exists;
                    --------------------------------------------------------------------------------------------------------------------------------
                      PROCEDURE ins_audit_rec(
                        p_audit_rec_in in cdc_batch_etl_op_ts_ranges%rowtype
                      )
                      AS
                      BEGIN
                        INSERT INTO cdc_batch_etl_op_ts_ranges (
                          job_ctrl_id,
                          table_name,
                          unk_src_op_type_cnt,
                          old_etl_op_ts_cnt,
                          row_cnt,
                          dup_cnt,
                          prev_tgt_apply_ts,
                          curr_tgt_apply_ts,
                          min_src_op_ts,
                          max_src_op_ts)
                        VALUES (
                          p_audit_rec_in.job_ctrl_id,
                          p_audit_rec_in.table_name,
                          p_audit_rec_in.unk_src_op_type_cnt,
                          p_audit_rec_in.old_etl_op_ts_cnt,
                          p_audit_rec_in.row_cnt,
                          p_audit_rec_in.dup_cnt,
                          p_audit_rec_in.prev_tgt_apply_ts,
                          p_audit_rec_in.curr_tgt_apply_ts,
                          p_audit_rec_in.min_src_op_ts,
                          p_audit_rec_in.max_src_op_ts
                        );
                      END ins_audit_rec;
                    --------------------------------------------------------------------------------------------------------------------------------
                      PROCEDURE upd_audit_rec(
                        p_audit_rec_in in cdc_batch_etl_op_ts_ranges%rowtype
                      )
                      AS
                      BEGIN
                        UPDATE cdc_batch_etl_op_ts_ranges SET
                          unk_src_op_type_cnt = p_audit_rec_in.unk_src_op_type_cnt,
                          old_etl_op_ts_cnt   = p_audit_rec_in.old_etl_op_ts_cnt,
                          row_cnt             = p_audit_rec_in.row_cnt,
                          dup_cnt             = p_audit_rec_in.dup_cnt,
                          prev_tgt_apply_ts   = p_audit_rec_in.prev_tgt_apply_ts,
                          curr_tgt_apply_ts   = p_audit_rec_in.curr_tgt_apply_ts,
                          min_src_op_ts       = p_audit_rec_in.min_src_op_ts,
                          max_src_op_ts       = p_audit_rec_in.max_src_op_ts
                        WHERE job_ctrl_id     = p_audit_rec_in.job_ctrl_id
                        AND table_name = p_audit_rec_in.table_name;
                    
                      END upd_audit_rec;
                    --------------------------------------------------------------------------------------------------------------------------------
                      PROCEDURE check_cdc_data (
                        p_table_name_in           IN all_tab_columns.table_name%TYPE,
                        p_curr_batch_strt_ts_in   IN TIMESTAMP DEFAULT NULL,
                        p_prev_batch_strt_ts_in   IN TIMESTAMP DEFAULT NULL  
                      )
                      AS
                        lv_drv        tab_processing_param_ty;
                        lv_result     cdc_check_results_ty;
                        lv_dup_cnt    cdc_batch_etl_op_ts_ranges.dup_cnt%type;
                        lv_audit_rec  cdc_batch_etl_op_ts_ranges%rowtype;
                      BEGIN
                      
                        get_tab_processing_params(p_table_name_in, p_curr_batch_strt_ts_in, p_prev_batch_strt_ts_in, lv_drv);
                        get_tab_check_results(p_table_name_in, lv_drv, lv_result);
                        get_dup_count(p_table_name_in, lv_drv.curr_batch_start_ts, lv_drv.prev_batch_start_ts, lv_dup_cnt);
                        map_results(
                          p_job_ctrl_id_in              => get_current_job_ctrl_id,
                          p_table_name_in               => p_table_name_in,
                          p_table_processing_prams_in   => lv_drv,
                          p_check_results_in            => lv_result,
                          p_dup_cnt_in                  => lv_dup_cnt,
                          p_audit_rec_out               => lv_audit_rec
                        );   
                        
                        IF audit_key_exists(get_current_job_ctrl_id, p_table_name_in) THEN
                          upd_audit_rec(lv_audit_rec);
                        ELSE
                          ins_audit_rec(lv_audit_rec);
                        END IF;
                        
                      END check_cdc_data;
                    --------------------------------------------------------------------------------------------------------------------------------
                    END cdc_pkg;
                    
                    • 38. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                      Vadim Tropashko-Oracle

                      Thank you for clean test case, Dylan. I have reproduced the missing table problem. I'm not sure how to fix  "group by" issue. It would certainly fail without the suggested "group by" clause

                       

                         

                          SELECT

                          NVL(

                            MAX(max_src_op_ts), 

                            TO_TIMESTAMP('31-DEC-9999', 'DD-MON-YYYY')

                          ) AS max_src_op_ts,

                          COALESCE(

                            prev_tgt_apply_ts, 

                            MAX(curr_tgt_apply_ts), 

                            TO_TIMESTAMP('01-JAN-1800', 'DD-MON-YYYY')

                          ) AS prev_batch_start_ts,

                          NVL(

                            Min_Src_Op_Ts,

                            Max_Src_Op_Ts

                          ) AS curr_batch_start_ts

                          FROM hr.cdc_batch_etl_op_ts_ranges group by NVL( Min_Src_Op_Ts, Max_Src_Op_Ts )

                       

                      It would run only if you add prev_tgt_apply_ts to the suggested group by list, but at least the [incomplete] suggestion was helpful.

                      • 39. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                        _Dylan_

                        In the query in the package, the NVLs are parameters/constants, so it's allowable. I think the equivalent would be more like...

                            SELECT

                            NVL(

                              MAX(Hire_Date),

                              TO_TIMESTAMP('31-DEC-9999', 'DD-MON-YYYY')

                            ) AS max_src_op_ts,

                            NVL(

                              :FIRST_NAME,

                              :FIRST_NAME_DEFAULT

                            ) AS curr_batch_start_ts

                            FROM Employees

                         

                        I have these related dates in a single variable. In theory, I can take that last piece out of the select as it's not driven by anything in the underlying table. It was just easier to do all in one spot.

                        • 40. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                          Vadim Tropashko-Oracle

                          I agree this group by advice is also a bug, thank you.

                          • 41. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                            Jim 13131

                            It also flags info+ commands as a syntax error. Like this little script:

                             

                            info+ dual;

                            select * from dual;

                             

                            info+.JPG

                            • 42. Re: Question: What do the little colored blocks next to the worksheet scroll bar mean/do?
                              thatJeffSmith-Oracle

                              Please start NEW threads when introducing additional questions/bugs.

                              1 2 3 上一个 下一个