Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Peter.

Asked: May 17, 2016 - 12:04 am UTC

Last updated: May 26, 2016 - 3:58 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

I have a need to compare data in a number of pairs of data files. I created two external table definitions and a view that uses these external tables. My process copies a pair of files to the corresponding external table's location and then queries the view. It repeats this process for each pair of files.

The problem I'm having is that I intermittently get an ORA-00942 (table or view does not exist) error when selecting from the view. I'm unable to consistently replicate this error. I've gotten as many as 5 ORA-00942 errors in a row with the next query then working.

I'm wondering if there is some caching issue between the OS and database that requires me to wait a certain amount of time after the OS copy commands complete before accessing the external tables. I can think of no other possible cause. Nothing has changed between getting an ORA-00942 and a successful view query (except a little time). I'm currently doing this manually (cp commands in a Unix shell) followed by switching to SQL Developer to run the query, but plan to automate this in a package by using a preprocessor external table to do the copy commands (passing file pair names with a control file written via UTL_FILE).

and Connor said...

Something very strange going on there, because if there is an issue with the *files*, then I don't think you would get ora-942 anyway.


SQL> drop table ext_t;

Table dropped.

SQL>
SQL> create table ext_t( x varchar2(100)
  2  )
  3  ORGANIZATION external
  4  (
  5    TYPE oracle_loader
  6    DEFAULT DIRECTORY TEMP
  7    ACCESS PARAMETERS
  8    (
  9      RECORDS DELIMITED BY NEWLINE
 10      BADFILE 't.bad'
 11      LOGFILE 't.log'
 12      READSIZE 1048576
 13      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
 14      REJECT ROWS WITH ALL NULL FIELDS
 15      (
 16        X CHAR(255)
 17      )
 18    )
 19    location
 20    (
 21      'no_such_file.dat'
 22    )
 23  )REJECT LIMIT UNLIMITED
 24  /

Table created.

SQL>
SQL> select * from ext_t;
select * from ext_t
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file no_such_file.dat in TEMP not found


SQL>
SQL>


ora-942 suggests either total non-existence, or, privileges issue.

Any chance you can send us a full test case ?


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Fixed without understanding

Peter Nosko, May 24, 2016 - 3:00 pm UTC

Thanks for your reply, Connor. Unfortunately, I am unable to post a test case. However, I will share what I did to eliminate the problem, even though I don't understand the how and why.

Again, I have two external tables, one at the detail level and the other being a summary of the detail file. I need to validate that the files match and that the summary is correct. I created this view below to summarize the detail file and spit of any groups that either don't match or are missing from one of the files.

Let me explain the first named subquery. The largest of the detail files is typically about 100K rows. My thinking, which could be entirely bogus is that I wanted to bring the table into memory before selecting the 55 code/amount pairs instead of reading selecting a few million rows from the external table. Was I mistakenly trying to spoon-feed the database here?

CREATE OR REPLACE VIEW v_civ_work_acctg_validate AS
    WITH wcanoc1   AS (
                       SELECT /*+PARALLEL(4)*/ * 
                         FROM civ_work_ext
                       )
        ,wcanoc2   AS (
SELECT can_no ,base_pay_oc          AS oc ,base_pay_amt           AS amount FROM wcanoc1 WHERE base_pay_amt          != 0 UNION ALL
SELECT can_no ,ot_pay_oc            AS oc ,ot_pay_amt             AS amount FROM wcanoc1 WHERE ot_pay_amt            != 0 UNION ALL
SELECT can_no ,nd_pay_oc            AS oc ,nd_pay_amt             AS amount FROM wcanoc1 WHERE nd_pay_amt            != 0 UNION ALL
SELECT can_no ,hol_pay_oc           AS oc ,hol_pay_amt            AS amount FROM wcanoc1 WHERE hol_pay_amt           != 0 UNION ALL
SELECT can_no ,sd_pay_oc            AS oc ,sd_pay_amt             AS amount FROM wcanoc1 WHERE sd_pay_amt            != 0 UNION ALL
SELECT can_no ,incen_pay_oc         AS oc ,incen_pay_amt          AS amount FROM wcanoc1 WHERE incen_pay_amt         != 0 UNION ALL
SELECT can_no ,pd_pay_oc            AS oc ,pd_pay_amt             AS amount FROM wcanoc1 WHERE pd_pay_amt            != 0 UNION ALL
SELECT can_no ,sev_pay_oc           AS oc ,sev_pay_amt            AS amount FROM wcanoc1 WHERE sev_pay_amt           != 0 UNION ALL
SELECT can_no ,oth_ben_oc           AS oc ,oth_ben_amt            AS amount FROM wcanoc1 WHERE oth_ben_amt           != 0 UNION ALL
SELECT can_no ,oth_pay_ab_oc        AS oc ,oth_pay_ab_amt         AS amount FROM wcanoc1 WHERE oth_pay_ab_amt        != 0 UNION ALL
SELECT can_no ,alow_trnees_oc       AS oc ,alow_trnees_amt        AS amount FROM wcanoc1 WHERE alow_trnees_amt       != 0 UNION ALL
SELECT can_no ,norm_lv_oc           AS oc ,norm_lv_amt            AS amount FROM wcanoc1 WHERE norm_lv_amt           != 0 UNION ALL
SELECT can_no ,ad_lv_us_oc          AS oc ,ad_lv_us_amt           AS amount FROM wcanoc1 WHERE ad_lv_us_amt          != 0 UNION ALL
SELECT can_no ,lslp_oc              AS oc ,lslp_amt               AS amount FROM wcanoc1 WHERE lslp_amt              != 0 UNION ALL
SELECT can_no ,csr_oc               AS oc ,csr_amt                AS amount FROM wcanoc1 WHERE csr_amt               != 0 UNION ALL
SELECT can_no ,fica_oc              AS oc ,fica_amt               AS amount FROM wcanoc1 WHERE fica_amt              != 0 UNION ALL
SELECT can_no ,fegli_oc             AS oc ,fegli_amt              AS amount FROM wcanoc1 WHERE fegli_amt             != 0 UNION ALL
SELECT can_no ,fehba_oc             AS oc ,fehba_amt              AS amount FROM wcanoc1 WHERE fehba_amt             != 0 UNION ALL
SELECT can_no ,emp_awd_oc           AS oc ,emp_awd_amt            AS amount FROM wcanoc1 WHERE emp_awd_amt           != 0 UNION ALL
SELECT can_no ,unif_allow_oc        AS oc ,unif_allow_amt         AS amount FROM wcanoc1 WHERE unif_allow_amt        != 0 UNION ALL
SELECT can_no ,cola_oc              AS oc ,cola_amt               AS amount FROM wcanoc1 WHERE cola_amt              != 0 UNION ALL
SELECT can_no ,qtr_ded_oc           AS oc ,qtr_ded_amt            AS amount FROM wcanoc1 WHERE qtr_ded_amt           != 0 UNION ALL
SELECT can_no ,yoc_oc               AS oc ,yoc_amt                AS amount FROM wcanoc1 WHERE yoc_amt               != 0 UNION ALL
SELECT can_no ,sk_lv_acrd_oc        AS oc ,sk_lv_acrd_amt         AS amount FROM wcanoc1 WHERE sk_lv_acrd_amt        != 0 UNION ALL
SELECT can_no ,sk_lv_usd_oc         AS oc ,sk_lv_usd_amt          AS amount FROM wcanoc1 WHERE sk_lv_usd_amt         != 0 UNION ALL
SELECT can_no ,sk_lv_tr_io_oc       AS oc ,sk_lv_tr_io_amt        AS amount FROM wcanoc1 WHERE sk_lv_tr_io_amt       != 0 UNION ALL
SELECT can_no ,merit_pay_oc         AS oc ,merit_pay_amt          AS amount FROM wcanoc1 WHERE merit_pay_amt         != 0 UNION ALL
SELECT can_no ,ses_awards_oc        AS oc ,ses_awards_amt         AS amount FROM wcanoc1 WHERE ses_awards_amt        != 0 UNION ALL
SELECT can_no ,annuity_oc           AS oc ,annuity_pay            AS amount FROM wcanoc1 WHERE annuity_pay           != 0 UNION ALL
SELECT can_no ,mp_cash_award_oc     AS oc ,mp_cash_award_amt      AS amount FROM wcanoc1 WHERE mp_cash_award_amt     != 0 UNION ALL
SELECT can_no ,subs_laund_ded_oc    AS oc ,subs_laund_ded_amt     AS amount FROM wcanoc1 WHERE subs_laund_ded_amt    != 0 UNION ALL
SELECT can_no ,tdsp_emplr_mand_oc   AS oc ,tdsp_emplr_mand_amt    AS amount FROM wcanoc1 WHERE tdsp_emplr_mand_amt   != 0 UNION ALL
SELECT can_no ,tdsp_emplr_mtch_oc   AS oc ,tdsp_emplr_mtch_amt    AS amount FROM wcanoc1 WHERE tdsp_emplr_mtch_amt   != 0 UNION ALL
SELECT can_no ,fers_oc              AS oc ,fers_agy_ded           AS amount FROM wcanoc1 WHERE fers_agy_ded          != 0 UNION ALL
SELECT can_no ,trvl_sav_awd_oc      AS oc ,trvl_sav_awd_amt       AS amount FROM wcanoc1 WHERE trvl_sav_awd_amt      != 0 UNION ALL
SELECT can_no ,geo_adj_oc           AS oc ,geo_adj_amt            AS amount FROM wcanoc1 WHERE geo_adj_amt           != 0 UNION ALL
SELECT can_no ,staff_diff_oc        AS oc ,staff_diff_amt         AS amount FROM wcanoc1 WHERE staff_diff_amt        != 0 UNION ALL
SELECT can_no ,super_diff_oc        AS oc ,super_diff_amt         AS amount FROM wcanoc1 WHERE super_diff_amt        != 0 UNION ALL
SELECT can_no ,epms_awd_oc          AS oc ,epms_awd_amt           AS amount FROM wcanoc1 WHERE epms_awd_amt          != 0 UNION ALL
SELECT can_no ,retent_allw_oc       AS oc ,retent_allw_amt        AS amount FROM wcanoc1 WHERE retent_allw_amt       != 0 UNION ALL
SELECT can_no ,recruit_bns_oc       AS oc ,recruit_bns_amt        AS amount FROM wcanoc1 WHERE recruit_bns_amt       != 0 UNION ALL
SELECT can_no ,reloc_bns_oc         AS oc ,reloc_bns_amt          AS amount FROM wcanoc1 WHERE reloc_bns_amt         != 0 UNION ALL
SELECT can_no ,brs_ret_oc           AS oc ,brs_ret_amt            AS amount FROM wcanoc1 WHERE brs_ret_amt           != 0 UNION ALL
SELECT can_no ,vsip_oc              AS oc ,vsip_amt               AS amount FROM wcanoc1 WHERE vsip_amt              != 0 UNION ALL
SELECT can_no ,vsip_csrs_oc         AS oc ,vsip_csrs_amt          AS amount FROM wcanoc1 WHERE vsip_csrs_amt         != 0 UNION ALL
SELECT can_no ,fringe_park_oc       AS oc ,fringe_park_amt        AS amount FROM wcanoc1 WHERE fringe_park_amt       != 0 UNION ALL
SELECT can_no ,on_call_oc           AS oc ,on_call_amt            AS amount FROM wcanoc1 WHERE on_call_amt           != 0 UNION ALL
SELECT can_no ,phys_dent_spec_oc    AS oc ,phys_dent_spec_pay     AS amount FROM wcanoc1 WHERE phys_dent_spec_pay    != 0 UNION ALL
SELECT can_no ,tea_emplr_tv_oc      AS oc ,tea_emplr_tv_amt       AS amount FROM wcanoc1 WHERE tea_emplr_tv_amt      != 0 UNION ALL
SELECT can_no ,tea_emplr_pk_oc      AS oc ,tea_emplr_pk_amt       AS amount FROM wcanoc1 WHERE tea_emplr_pk_amt      != 0 UNION ALL
SELECT can_no ,tea_emplr_cashout_oc AS oc ,tea_emplr_cashout_amt  AS amount FROM wcanoc1 WHERE tea_emplr_cashout_amt != 0 UNION ALL
SELECT can_no ,child_sup_sub_oc     AS oc ,child_sup_sub_amt      AS amount FROM wcanoc1 WHERE child_sup_sub_amt     != 0 UNION ALL
SELECT can_no ,rel_comp_ls_oc       AS oc ,rel_comp_ls_amt        AS amount FROM wcanoc1 WHERE rel_comp_ls_amt       != 0 UNION ALL
SELECT can_no ,cr_hrs_oc            AS oc ,cr_hrs_amt             AS amount FROM wcanoc1 WHERE cr_hrs_amt            != 0 UNION ALL
SELECT can_no ,cr_hrs_ls_oc         AS oc ,cr_hrs_ls_amt          AS amount FROM wcanoc1 WHERE cr_hrs_ls_amt         != 0 
                       )
        ,wcanoc3   AS (
SELECT can_no
      ,oc
      ,SUM(amount)                                                          AS amount
  FROM wcanoc2 
 GROUP BY can_no, oc
 HAVING SUM(amount) != 0
                       )
        ,acanoc    AS (
SELECT can_no
      ,object_class_code                                                    AS oc
      ,CASE WHEN reversal_code = '1' THEN amount ELSE amount * -1 END       AS amount
  FROM civ_acctg_ext
                       )
        SELECT NVL(w.can_no,a.can_no)          AS can_no
              ,NVL(w.oc    ,a.oc)              AS oc
              ,w.amount                        AS work_amount
              ,a.amount                        AS acctg_amount
              ,NVL(a.amount,0)-NVL(w.amount,0) AS diff_amount
          FROM      wcanoc3 w
          FULL JOIN acanoc  a ON a.can_no = w.can_no AND NVL(a.oc,'XXXXX') = NVL(w.oc,'XXXXX')
         WHERE w.can_no IS NULL OR w.oc IS NULL OR a.can_no IS NULL OR a.oc IS NULL OR a.amount != w.amount
         ORDER BY 1, 2;


After getting ORA-00942 errors intermittently when selecting from the view, on a whim, I eliminated that first named subquery.

CREATE OR REPLACE VIEW v_civ_work_acctg_validate AS
    WITH wcanoc1   AS (
SELECT can_no ,base_pay_oc          AS oc ,base_pay_amt           AS amount FROM civ_work_ext WHERE base_pay_amt          != 0 UNION ALL
SELECT can_no ,ot_pay_oc            AS oc ,ot_pay_amt             AS amount FROM civ_work_ext WHERE ot_pay_amt            != 0 UNION ALL
SELECT can_no ,nd_pay_oc            AS oc ,nd_pay_amt             AS amount FROM civ_work_ext WHERE nd_pay_amt            != 0 UNION ALL
SELECT can_no ,hol_pay_oc           AS oc ,hol_pay_amt            AS amount FROM civ_work_ext WHERE hol_pay_amt           != 0 UNION ALL
SELECT can_no ,sd_pay_oc            AS oc ,sd_pay_amt             AS amount FROM civ_work_ext WHERE sd_pay_amt            != 0 UNION ALL
SELECT can_no ,incen_pay_oc         AS oc ,incen_pay_amt          AS amount FROM civ_work_ext WHERE incen_pay_amt         != 0 UNION ALL
SELECT can_no ,pd_pay_oc            AS oc ,pd_pay_amt             AS amount FROM civ_work_ext WHERE pd_pay_amt            != 0 UNION ALL
SELECT can_no ,sev_pay_oc           AS oc ,sev_pay_amt            AS amount FROM civ_work_ext WHERE sev_pay_amt           != 0 UNION ALL
SELECT can_no ,oth_ben_oc           AS oc ,oth_ben_amt            AS amount FROM civ_work_ext WHERE oth_ben_amt           != 0 UNION ALL
SELECT can_no ,oth_pay_ab_oc        AS oc ,oth_pay_ab_amt         AS amount FROM civ_work_ext WHERE oth_pay_ab_amt        != 0 UNION ALL
SELECT can_no ,alow_trnees_oc       AS oc ,alow_trnees_amt        AS amount FROM civ_work_ext WHERE alow_trnees_amt       != 0 UNION ALL
SELECT can_no ,norm_lv_oc           AS oc ,norm_lv_amt            AS amount FROM civ_work_ext WHERE norm_lv_amt           != 0 UNION ALL
SELECT can_no ,ad_lv_us_oc          AS oc ,ad_lv_us_amt           AS amount FROM civ_work_ext WHERE ad_lv_us_amt          != 0 UNION ALL
SELECT can_no ,lslp_oc              AS oc ,lslp_amt               AS amount FROM civ_work_ext WHERE lslp_amt              != 0 UNION ALL
SELECT can_no ,csr_oc               AS oc ,csr_amt                AS amount FROM civ_work_ext WHERE csr_amt               != 0 UNION ALL
SELECT can_no ,fica_oc              AS oc ,fica_amt               AS amount FROM civ_work_ext WHERE fica_amt              != 0 UNION ALL
SELECT can_no ,fegli_oc             AS oc ,fegli_amt              AS amount FROM civ_work_ext WHERE fegli_amt             != 0 UNION ALL
SELECT can_no ,fehba_oc             AS oc ,fehba_amt              AS amount FROM civ_work_ext WHERE fehba_amt             != 0 UNION ALL
SELECT can_no ,emp_awd_oc           AS oc ,emp_awd_amt            AS amount FROM civ_work_ext WHERE emp_awd_amt           != 0 UNION ALL
SELECT can_no ,unif_allow_oc        AS oc ,unif_allow_amt         AS amount FROM civ_work_ext WHERE unif_allow_amt        != 0 UNION ALL
SELECT can_no ,cola_oc              AS oc ,cola_amt               AS amount FROM civ_work_ext WHERE cola_amt              != 0 UNION ALL
SELECT can_no ,qtr_ded_oc           AS oc ,qtr_ded_amt            AS amount FROM civ_work_ext WHERE qtr_ded_amt           != 0 UNION ALL
SELECT can_no ,yoc_oc               AS oc ,yoc_amt                AS amount FROM civ_work_ext WHERE yoc_amt               != 0 UNION ALL
SELECT can_no ,sk_lv_acrd_oc        AS oc ,sk_lv_acrd_amt         AS amount FROM civ_work_ext WHERE sk_lv_acrd_amt        != 0 UNION ALL
SELECT can_no ,sk_lv_usd_oc         AS oc ,sk_lv_usd_amt          AS amount FROM civ_work_ext WHERE sk_lv_usd_amt         != 0 UNION ALL
SELECT can_no ,sk_lv_tr_io_oc       AS oc ,sk_lv_tr_io_amt        AS amount FROM civ_work_ext WHERE sk_lv_tr_io_amt       != 0 UNION ALL
SELECT can_no ,merit_pay_oc         AS oc ,merit_pay_amt          AS amount FROM civ_work_ext WHERE merit_pay_amt         != 0 UNION ALL
SELECT can_no ,ses_awards_oc        AS oc ,ses_awards_amt         AS amount FROM civ_work_ext WHERE ses_awards_amt        != 0 UNION ALL
SELECT can_no ,annuity_oc           AS oc ,annuity_pay            AS amount FROM civ_work_ext WHERE annuity_pay           != 0 UNION ALL
SELECT can_no ,mp_cash_award_oc     AS oc ,mp_cash_award_amt      AS amount FROM civ_work_ext WHERE mp_cash_award_amt     != 0 UNION ALL
SELECT can_no ,subs_laund_ded_oc    AS oc ,subs_laund_ded_amt     AS amount FROM civ_work_ext WHERE subs_laund_ded_amt    != 0 UNION ALL
SELECT can_no ,tdsp_emplr_mand_oc   AS oc ,tdsp_emplr_mand_amt    AS amount FROM civ_work_ext WHERE tdsp_emplr_mand_amt   != 0 UNION ALL
SELECT can_no ,tdsp_emplr_mtch_oc   AS oc ,tdsp_emplr_mtch_amt    AS amount FROM civ_work_ext WHERE tdsp_emplr_mtch_amt   != 0 UNION ALL
SELECT can_no ,fers_oc              AS oc ,fers_agy_ded           AS amount FROM civ_work_ext WHERE fers_agy_ded          != 0 UNION ALL
SELECT can_no ,trvl_sav_awd_oc      AS oc ,trvl_sav_awd_amt       AS amount FROM civ_work_ext WHERE trvl_sav_awd_amt      != 0 UNION ALL
SELECT can_no ,geo_adj_oc           AS oc ,geo_adj_amt            AS amount FROM civ_work_ext WHERE geo_adj_amt           != 0 UNION ALL
SELECT can_no ,staff_diff_oc        AS oc ,staff_diff_amt         AS amount FROM civ_work_ext WHERE staff_diff_amt        != 0 UNION ALL
SELECT can_no ,super_diff_oc        AS oc ,super_diff_amt         AS amount FROM civ_work_ext WHERE super_diff_amt        != 0 UNION ALL
SELECT can_no ,epms_awd_oc          AS oc ,epms_awd_amt           AS amount FROM civ_work_ext WHERE epms_awd_amt          != 0 UNION ALL
SELECT can_no ,retent_allw_oc       AS oc ,retent_allw_amt        AS amount FROM civ_work_ext WHERE retent_allw_amt       != 0 UNION ALL
SELECT can_no ,recruit_bns_oc       AS oc ,recruit_bns_amt        AS amount FROM civ_work_ext WHERE recruit_bns_amt       != 0 UNION ALL
SELECT can_no ,reloc_bns_oc         AS oc ,reloc_bns_amt          AS amount FROM civ_work_ext WHERE reloc_bns_amt         != 0 UNION ALL
SELECT can_no ,brs_ret_oc           AS oc ,brs_ret_amt            AS amount FROM civ_work_ext WHERE brs_ret_amt           != 0 UNION ALL
SELECT can_no ,vsip_oc              AS oc ,vsip_amt               AS amount FROM civ_work_ext WHERE vsip_amt              != 0 UNION ALL
SELECT can_no ,vsip_csrs_oc         AS oc ,vsip_csrs_amt          AS amount FROM civ_work_ext WHERE vsip_csrs_amt         != 0 UNION ALL
SELECT can_no ,fringe_park_oc       AS oc ,fringe_park_amt        AS amount FROM civ_work_ext WHERE fringe_park_amt       != 0 UNION ALL
SELECT can_no ,on_call_oc           AS oc ,on_call_amt            AS amount FROM civ_work_ext WHERE on_call_amt           != 0 UNION ALL
SELECT can_no ,phys_dent_spec_oc    AS oc ,phys_dent_spec_pay     AS amount FROM civ_work_ext WHERE phys_dent_spec_pay    != 0 UNION ALL
SELECT can_no ,tea_emplr_tv_oc      AS oc ,tea_emplr_tv_amt       AS amount FROM civ_work_ext WHERE tea_emplr_tv_amt      != 0 UNION ALL
SELECT can_no ,tea_emplr_pk_oc      AS oc ,tea_emplr_pk_amt       AS amount FROM civ_work_ext WHERE tea_emplr_pk_amt      != 0 UNION ALL
SELECT can_no ,tea_emplr_cashout_oc AS oc ,tea_emplr_cashout_amt  AS amount FROM civ_work_ext WHERE tea_emplr_cashout_amt != 0 UNION ALL
SELECT can_no ,child_sup_sub_oc     AS oc ,child_sup_sub_amt      AS amount FROM civ_work_ext WHERE child_sup_sub_amt     != 0 UNION ALL
SELECT can_no ,rel_comp_ls_oc       AS oc ,rel_comp_ls_amt        AS amount FROM civ_work_ext WHERE rel_comp_ls_amt       != 0 UNION ALL
SELECT can_no ,cr_hrs_oc            AS oc ,cr_hrs_amt             AS amount FROM civ_work_ext WHERE cr_hrs_amt            != 0 UNION ALL
SELECT can_no ,cr_hrs_ls_oc         AS oc ,cr_hrs_ls_amt          AS amount FROM civ_work_ext WHERE cr_hrs_ls_amt         != 0 
                       )
        ,wcanoc2   AS (
SELECT /*+PARALLEL(4)*/
       can_no
      ,oc
      ,SUM(amount)                                                          AS amount
  FROM wcanoc1
 GROUP BY can_no, oc HAVING SUM(amount) != 0
                       )
        ,acanoc    AS (
SELECT can_no
      ,object_class_code                                                    AS oc
      ,CASE WHEN reversal_code = '1' THEN amount ELSE amount * -1 END       AS amount
  FROM civ_acctg_ext
                       )
        SELECT NVL(w.can_no,a.can_no)          AS can_no
              ,NVL(w.oc    ,a.oc)              AS oc
              ,w.amount                        AS work_amount
              ,a.amount                        AS acctg_amount
              ,NVL(a.amount,0)-NVL(w.amount,0) AS diff_amount
          FROM      wcanoc2 w
          FULL JOIN acanoc  a ON a.can_no = w.can_no AND NVL(a.oc,'XXXXX') = NVL(w.oc,'XXXXX')
         WHERE w.can_no IS NULL OR w.oc IS NULL OR a.can_no IS NULL OR a.oc IS NULL OR a.amount != w.amount
         ORDER BY 1, 2;


This change completely eliminated the ORA-00942 errors. Any ideas what's going on here?

P.S. The oc column is CHAR(4), to explain the NVL to XXXXX.
Chris Saxon
May 25, 2016 - 3:45 am UTC

Quick question - is this a single instance or RAC system ?

Peter Nosko, May 25, 2016 - 4:16 pm UTC

It is a single instance.
Chris Saxon
May 26, 2016 - 3:58 am UTC

Ta,

Does the problem still arise if you run it serially ?

I'm wondering if there's an issue in our parallel processing of external tables - that's what prompted my RAC question (because there has been issues sometimes with cross-instance parallel external table processing).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library