Recently I have to develop/modify one of the most complex batch programs in Oracle Retail: the dealinc batch.
After making several optimizations and change the program logic (Oracle even in the most recent version of Oracle Retail 14 & 15 did not fix all the performance issues related with this batch), I was not able to compile dealinc Pro*C program.
This is the error I received:
Syntax error at line 587, column 8, file dealinc.pc:
Error at line 587, column 8 in file dealinc.pc
order by dail.reporting_date;
PCC-S-02201, Encountered the symbol “order” when expecting one of the following:
After taking a deep analysis, I can’t find anything with the wrong syntax that could lead me to fix the error.
This is the complete SQL:
EXEC SQL INSERT INTO c_detail ( deal_id, deal_detail_id, stock_ledger_ind, deal_income_calculation, threshold_limit_type, threshold_value_type, rebate_calc_type, currency_code1, currency_code2, growth_rate_to_date, calc_to_zero_ind, total_actual_fixed_ind, rebate_purch_sales_ind, dai_id, item, loc_type, location, reporting_date, order_no, actual_turnover_units, actual_turnover_revenue, actual_income, act_for_turnover_total, dept, class, subclass, last_reporting_date, rebate_ind ) SELECT /*+ ordered */ gdd.deal_id, gdd.deal_detail_id, gdd.stock_ledger_ind, gdd.deal_income_calculation, gdd.threshold_limit_type, gdd.threshold_value_type, gdd.rebate_calc_type, NVL(gdd.currency_code, :ps_primary_currency_code), NVL(vloc.currency_code, :ps_primary_currency_code), gdd.growth_rate_to_date, gdd.calc_to_zero_ind, gdd.total_actual_fixed_ind, gdd.rebate_purch_sales_ind, dail.dai_id, dail.item, dail.loc_type, dail.location, TO_CHAR(dail.reporting_date,'YYYYMMDD'), NVL(dail.order_no, -1), dail.actual_turnover_units, dail.actual_turnover_revenue, NVL(dail.actual_income,0), gdd.act_for_turnover_total, im.dept, im.class, im.subclass, DECODE(gdd.last_reporting_date, dail.reporting_date,'Y','N') last_period, gdd.rebate_ind FROM gtt_dealinc_deals gdd, deal_actuals_item_loc dail, item_master im, (SELECT st.store loc, st.currency_code, 'S' loc_type FROM store st UNION ALL SELECT wh.wh loc, wh.currency_code, 'W' loc_type FROM wh WHERE stockholding_ind = 'Y' AND finisher_ind = 'N') vloc WHERE 1 = 1 AND dail.deal_id = gdd.deal_id AND dail.deal_detail_id = gdd.deal_detail_id AND dail.item = im.item AND dail.location = vloc.loc AND dail.loc_type = vloc.loc_type AND dail.reporting_date = gdd.reporting_date AND dail.reporting_date <= TO_DATE(:ps_vdate, 'YYYYMMDD') AND dail.reporting_date > TO_DATE(:ps_last_stkldgr_close_date, 'YYYYMMDD') order by dail.reporting_date; EXEC SQL COMMIT;
and this is the line 587:
order by dail.reporting_date;
As you can see, nothing special. Besides, I get the several other errors like:
Syntax error at line 1152, column 13, file dealinc.pc:
Error at line 1152, column 13 in file dealinc.pc
OVER (PARTITION BY dail.deal_id,dail.deal_detail_id,dail.reporting_d
ate,dail.item,dail.location,dail.loc_type), 0) sum_actual_turnover_units
PCC-S-02201, Encountered the symbol “OVER” when expecting one of the following:
, ) * + – / | at, day, hour, minute, month, second, year,
Error at line 0, column 0 in file dealinc.pc
PCC-F-02102, Fatal error while doing C preprocessing
make: 1254-004 The error code from the last command is 1.
Target “dealinc” did not make because of errors.
make: 1254-004 The error code from the last command is 2.
After going through the Pro*C manuals, I’ve found what could be the issue:
COMMON_PARSER option for Pro*C
Specifies that the SQL99 syntax for SELECT, INSERT, DELETE, UPDATE and body of
the cursor in a DECLARE CURSOR
statement will be supported.
In $ORACLE_HOME/precomp/admin/ directory I’ve changed the file pcscfg.cfg by adding:
at the end of the configuration.
After making that change, dealinc.pc Pro*C program compiles successfully.
There are some bugs related to Pro*C precompiler, that can also lead to strange behavior of Pro*C compilation, and I strongly suggests to read Metalink notes to check if it affect your case.
Anyway, Pro*C still has many performance advantages, and is still heavily used, although for number crunching intensive programs now it has competitors in Java stored procedures.
But that is the topic for some other blog.