ORACLE 11g uses ROWNUM to perfect ORA-00600: internal error code

Posted by postmanager on Tue, 30 Jul 2019 08:56:41 +0200

 

1, ORA-00600: Internal error code

Oracle upgraded from 11.2.0.1 to 11.2.0.4. Developers reported a job failure, debugging error messages, ORA-00600: internal error code, parameters: [rwoirw: check ret val], [], [], [], ORA-06512:... As shown in the following figure:

 

C:\pic\oracle\2017040501.png

 

 

Look at the 2000-line package and see that 454 lines of code are a long sql of create tables, as follows:

 

/*===========================================================================*/
  /*Building ads_amp_pd_ma_merchant_sale table*/
/*===========================================================================*/
  PROCEDURE ads_amp_pd_ma_merchant_sale AS
    v_createsql varchar2(32767);
  BEGIN
    PKG_COMMON.drop_table('ads_amp_pd_ma_merchant_sale');
 
    v_createsql := '
    create table ads_amp_pd_ma_merchant_sale as
    (select
      --0 as ID,
      PKG_ADS_AMP_PD.PARTNER_ID as PARTNER_ID,
      0 as PROJECT_ID,
      0 as CONT_ID,
      zjwgsals.bis_shop_name as BRAND_NAME,
      zjwgsals.sales_money as SALE_AMOUNT,
      0 as BUDGET_AMOUNT,
      decode(zjwgsals.rent_square, 0, 0, round(zjwgsals.sales_money / zjwgsals.rent_square, 2)) as PERFORMANCE_VALUE,
      decode(zjwgsals.sales_money, 0, 0, round(zjwgsals.must_zj / zjwgsals.sales_money, 2)) as RENT_SALE_RATE,
      decode(zjwgsals.sort_name_root, ''Supermarket'', ''100'', ''Cinema'', ''200'', ''Department store'', ''300'', ''clothing'', ''400'', ''children'', ''500'', ''Restaurant'', ''600'', ''Matching'', ''700'', ''800'') as COMMERCIAL_TYPE,
      zjwgsals.sort_name_root as COMMERCIAL_TYPE_NAME,
      zjwgsals.YEAR as YEAR,
      zjwgsals.MONTH as MONTH,
      zjwgsals.qz_year_month as DUTY_MONTH,
      ''0'' as IS_DEL,
      sysdate as CREATE_DATE,
      --null as UPDATE_DATE,
      PKG_ADS_AMP_PD.USER_ID as CREATER_ID,
      --null as UPDATER_ID,
      zjwgsals.bis_project_id as OUT_PROJECT_ID,
      zjwgsals.bis_cont_id as OUT_CONTRACT_ID,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100 / 30.42, 2) as RENT_PER_DAY,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100, 2) as RENT_PER_MONTH
    from
      ads_amp_pd_ma_contract cont
    inner join
      (select
        a.*,
        to_number(substr(qz_year_month, 1, 4)) as YEAR,
        to_number(substr(qz_year_month, 6, 2)) as MONTH
      from dws_pd_cont_zjwgsals_1m a
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
        and a.status_cd not in (''3'', ''5'')
        and a.sals_data_flg = ''1''
      ) zjwgsals
    on cont.OUT_PROJECT_ID = zjwgsals.bis_project_id and cont.OUT_CONTRACT_ID = zjwgsals.bis_cont_id
    left outer join
      --Percentage of commission
      (select
        bis_cont_id,
        year,
        royalty_ratio,
        guaranteed_money
      from ods_pd_bis_must_rent
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
      ) mustrent
    on zjwgsals.bis_cont_id = mustrent.bis_cont_id and (to_number(zjwgsals.YEAR) - to_number(to_char(cont.START_DATE, ''yyyy'')) + 1) = mustrent.year
    --TODO:
    where zjwgsals.bis_project_id in (''6D7E1C7AFAFB43E986670A81CF444235'', ''402834702db81ec3012dbca135f20c6a'')
    )';
    executeimmediate v_createsql;
    COMMIT;
  END;
 

 

 

 

 

2. The official solution is not the best one.

It's executed by dynamic sql, creating a table error problem. It's ok in version 11.2.0.1, and it's ok in version 11.2.0.4. Gogle said it's a bug in 11.2.0.4.

 

The official description of the bug is as follows and provides two solutions:

Refrence: Bug 14275161 - ORA-600 [rwoirw:check ret val] on CTAS with predicate move around (Doc ID 14275161.8)

 

 

The first is to upgrade to 12.1:

It is obviously inappropriate to upgrade to 12.1 after 11.2.0.4, and it would be more troublesome if there were new bug s at 12.1.
 

The second method: bypass the error by setting the implicit parameter _pred_move_around to false

This has also been tried, no, add altersystem set_pred_move_around= false in the dynamic sql of stored procedure, and then report the following error:

SQL> call PKG_ADS_AMP_PD.build();

call PKG_ADS_AMP_PD.build()

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body "DW.PKG_ADS_AMP_PD" has been invalidated

ORA-04065: not executed, altered or dropped package body "DW.PKG_ADS_AMP_PD"

 

SQL>

 

 

So the above two methods are not feasible. It is an error to take the create sql out of the dynamic stored procedure and execute it on 11.2.0.4 separately, and the error is exactly the same. Suddenly it comes to mind that there are too many temporary tables. Then immediately try adding rownum < 100; manually executing the create sql statement results in success.

 

 

 

3. Perfect solution with ROWNUM

Think about it, maybe in 11.2.0.4, create tablexx as select... from... The restrictions are stricter (or maybe a real bug?) It means that you don't know the selection behind it. from... If the total number or number has exceeded the default value of oracle, such as 1000, the error of ORA-00600 will be prompted. In accordance with this idea, I inquired out to select ___________. from... total number, add and rownum < 100000; execute the stored procedure again, alas, Skydrop Chery, succeeded.

 

Then divergent thinking, in case the total number of selects exceeds 100,000, what can we do? We can't always select count(1) from ____________ every time. Then create it. According to the test boundary value theory, a maximum value ok, then I try the minimum value, and rownum > - 1; (because it is possible to select empty records)? After a try, the stored procedure executes successfully, and rownum > - 1. Then the sql in the stored procedure is finally rewritten as follows:

 

/*===========================================================================*/
  /*Building ads_amp_pd_ma_mercha, nt_sale table*/
/*===========================================================================*/
  PROCEDURE ads_amp_pd_ma_merchant_sale AS
    v_createsql varchar2(32767);
  BEGIN
    PKG_COMMON.drop_table('ads_amp_pd_ma_merchant_sale');
 
    v_createsql := '
    create table ads_amp_pd_ma_merchant_sale as
    (select
      --0 as ID,
      PKG_ADS_AMP_PD.PARTNER_ID as PARTNER_ID,
      0 as PROJECT_ID,
      0 as CONT_ID,
      zjwgsals.bis_shop_name as BRAND_NAME,
      zjwgsals.sales_money as SALE_AMOUNT,
      0 as BUDGET_AMOUNT,
      decode(zjwgsals.rent_square, 0, 0, round(zjwgsals.sales_money / zjwgsals.rent_square, 2)) as PERFORMANCE_VALUE,
      decode(zjwgsals.sales_money, 0, 0, round(zjwgsals.must_zj / zjwgsals.sales_money, 2)) as RENT_SALE_RATE,
      decode(zjwgsals.sort_name_root, ''Supermarket'', ''100'', ''Cinema'', ''200'', ''Department store'', ''300'', ''clothing'', ''400'', ''children'', ''500'', ''Restaurant'', ''600'', ''Matching'', ''700'', ''800'') as COMMERCIAL_TYPE,
      zjwgsals.sort_name_root as COMMERCIAL_TYPE_NAME,
      zjwgsals.YEAR as YEAR,
      zjwgsals.MONTH as MONTH,
      zjwgsals.qz_year_month as DUTY_MONTH,
      ''0'' as IS_DEL,
      sysdate as CREATE_DATE,
      --null as UPDATE_DATE,
      PKG_ADS_AMP_PD.USER_ID as CREATER_ID,
      --null as UPDATER_ID,
      zjwgsals.bis_project_id as OUT_PROJECT_ID,
      zjwgsals.bis_cont_id as OUT_CONTRACT_ID,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100 / 30.42, 2) as RENT_PER_DAY,
      round(zjwgsals.sales_money * nvl(mustrent.royalty_ratio, 0) / 100, 2) as RENT_PER_MONTH
    from
      ads_amp_pd_ma_contract cont
    inner join
      (select
        a.*,
        to_number(substr(qz_year_month, 1, 4)) as YEAR,
        to_number(substr(qz_year_month, 6, 2)) as MONTH
      from dws_pd_cont_zjwgsals_1m a
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
        and a.status_cd not in (''3'', ''5'')
        and a.sals_data_flg = ''1''
      ) zjwgsals
    on cont.OUT_PROJECT_ID = zjwgsals.bis_project_id and cont.OUT_CONTRACT_ID = zjwgsals.bis_cont_id
    left outer join
      --Percentage of commission
      (select
        bis_cont_id,
        year,
        royalty_ratio,
        guaranteed_money
      from ods_pd_bis_must_rent
      where dt = PKG_COMMON.YESTERDAY_YYYYMMDD
      ) mustrent
    on zjwgsals.bis_cont_id = mustrent.bis_cont_id and (to_number(zjwgsals.YEAR) - to_number(to_char(cont.START_DATE, ''yyyy'')) + 1) = mustrent.year
    --TODO:
    where zjwgsals.bis_project_id in (''6D7E1C7AFAFB43E986670A81CF444235'', ''402834702db81ec3012dbca135f20c6a'')
    )' and rownum>-1;
    executeimmediate v_createsql;
    COMMIT;
  END;

 

 

 

 

 

 

 

 

 

OK, so far, the problem is solved perfectly, without changing the program code, system parameters, restart db, just add a simple and rownum > - 1 after where condition of the stored procedure create statement.

 

 

Reference article: http://blog.csdn.net/wengtf/article/details/25713255

 

Topics: SQL Stored Procedure Oracle