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;
…….1
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
…………1
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.
(continuing)
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

Purpose
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:

common_parser=yes

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.



Get notified when a new post is published!

 

Loading

Comments

CaMan
2019-04-16 02:11:15
Hi, Hope you read this. You juste give me a solution i couldn´t find anywhere, and just want to say THANKS. Grettings from México. 15/04/2019
Pai N
2019-06-26 19:29:22
Big Thanks from myside. Spent Long Hrs as the Query was running fine on other SQL editor and Pro *C was throwing error. It worked after the config update.
Jag
2020-04-24 07:47:05
Thanks very much for this article. Initially I was suspecting that Proc will not allow OVER() and trying to find other ways. But, luckily find out this one. Once again thank you very much. Just an added not, Instead of updating the config file which might be used by other projects (which refers the same config file and as I dont know how the impact would be on their projects), I added this as flag to Proc command line.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.