Home » SQL & PL/SQL » SQL & PL/SQL » Compilation Error in PLSQL
Compilation Error in PLSQL [message #214264] Mon, 15 January 2007 10:42 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi All,

Please find the strange query situation in PLSQL.
If i run the query without PLSQL block (i.e. declar begin end) it runs well and insert data in table

but if i put the same query in PLSQL block it gives compilation error. Following is the spool

SQL> select * from v$version;

BANNER                                                                                                                                      
----------------------------------------------------------------                                                                            
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production                                                                                  
PL/SQL Release 8.1.7.4.0 - Production                                                                                                       
CORE	8.1.7.0.0	Production                                                                                                                   
TNS for IBM/AIX RISC System/6000: Version 8.1.7.4.0 - Production                                                                            
NLSRTL Version 3.4.1.0.0 - Production                                                                                                       

SQL> insert into smcbom_load_hours_temp
  2  select data_set_name,deptclass,dept,smcbom_flex_budget.get_period(v_date) v_period,sum(v_hr),-1,sysdate,-1,sysdate
  3  from (
  4  select plan_level,sp.data_set_name,
  5  sp.value1,smcbom_flex_budget.calculate_period_days(sp.period1),inweight ,
  6  usagerate ,operationseq,percent,
  7  sbov.group_id,sp.alloy,sp.planner_code,
  8  sbov.days,sbov.totaloffsetdays,deptclass,dept,
  9  decode(plan_level,1,(sp.value14/smcbom_flex_budget.calculate_period_days(sp.period14)) * (percent/100) * inweight * usagerate *
 10  (SELECT MAX(INWEIGHT)
 11  FROM  SMCBOM_BOM_OPERATION_VIEW
 12  WHERE ALLOY=sbov.alloy
 13  AND   PLANNER_CODE=sbov.planner_code
 14  AND   PLAN_LEVEL = 0
 15  AND   GROUP_ID = sbov.group_id ),
 16  0,(sp.value14/smcbom_flex_budget.calculate_period_days(sp.period14)) * (percent/100) * inweight * usagerate ,
 17  1) v_hr,
 18  smcbom_flex_budget.get_start_date(sp.period14)+sbov.totaloffsetdays v_date,
 19  sum(-sbov.totaloffsetdays)
 20  over (partition by sp.alloy,sp.planner_code,sbov.group_id
 21  order by plan_level asc,operationseq desc)  new_offset
 22  from smcbom_bom_operation_view sbov,smcbom_sales_prod_forecasts sp
 23  where sbov.alloy= sp.alloy
 24  and   sbov.planner_code=sp.planner_code
 25  and group_id=521136
 26  )
 27  group by data_set_name,deptclass,dept,smcbom_flex_budget.get_period(v_date);

23 rows created.

SQL> commit;

Commit complete.

SQL> declare
  2  begin
  3  insert into smcbom_load_hours_temp
  4  select data_set_name,deptclass,dept,smcbom_flex_budget.get_period(v_date) v_period,sum(v_hr),-1,sysdate,-1,sysdate
  5  from (
  6  select plan_level,sp.data_set_name,
  7  sp.value1,smcbom_flex_budget.calculate_period_days(sp.period1),inweight ,
  8  usagerate ,operationseq,percent,
  9  sbov.group_id,sp.alloy,sp.planner_code,
 10  sbov.days,sbov.totaloffsetdays,deptclass,dept,
 11  decode(plan_level,1,(sp.value14/smcbom_flex_budget.calculate_period_days(sp.period14)) * (percent/100) * inweight * usagerate *
 12  (SELECT MAX(INWEIGHT)
 13  FROM  SMCBOM_BOM_OPERATION_VIEW
 14  WHERE ALLOY=sbov.alloy
 15  AND   PLANNER_CODE=sbov.planner_code
 16  AND   PLAN_LEVEL = 0
 17  AND   GROUP_ID = sbov.group_id ),
 18  0,(sp.value14/smcbom_flex_budget.calculate_period_days(sp.period14)) * (percent/100) * inweight * usagerate ,
 19  1) v_hr,
 20  smcbom_flex_budget.get_start_date(sp.period14)+sbov.totaloffsetdays v_date,
 21  sum(-sbov.totaloffsetdays)
 22  over (partition by sp.alloy,sp.planner_code,sbov.group_id
 23  order by plan_level asc,operationseq desc)  new_offset
 24  from smcbom_bom_operation_view sbov,smcbom_sales_prod_forecasts sp
 25  where sbov.alloy= sp.alloy
 26  and   sbov.planner_code=sp.planner_code
 27  and group_id=521136
 28  )
 29  group by data_set_name,deptclass,dept,smcbom_flex_budget.get_period(v_date);
 30  end;
 31  /
(SELECT MAX(INWEIGHT)
 *
ERROR at line 12:
ORA-06550: line 12, column 2: 
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: 
( - + mod not null others <an identifier> 
  avg 
count current exists max min prior sql stddev sum variance 
execute forall time timestamp interval date 
 
  
ORA-06550: line 22, column 6: 
PLS-00103: Encountered the symbol "(" when expecting one of the following: 
, from 


SQL> spool off;


Any idea/suggestion what is wrong
Thanks in advance
Re: Compilation Error in PLSQL [message #214268 is a reply to message #214264] Mon, 15 January 2007 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Any idea/suggestion what is wrong
IIRC, this is/was an Oracle bug & reason to use only supported version of Oracle.
Re: Compilation Error in PLSQL [message #214273 is a reply to message #214268] Mon, 15 January 2007 11:09 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Can you please elaborate what would be the meaning of supported version of oracle???
Re: Compilation Error in PLSQL [message #214276 is a reply to message #214264] Mon, 15 January 2007 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Can you please elaborate what would be the meaning of supported version of oracle???
V10.2
Re: Compilation Error in PLSQL [message #214278 is a reply to message #214276] Mon, 15 January 2007 11:23 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
So you mean there is bug in PLSQL release and need to upgrade to latest PLSQL ( implied we have to upgrade to 10g).

Thanks for reply
Re: Compilation Error in PLSQL [message #214282 is a reply to message #214278] Mon, 15 January 2007 12:12 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

Just FYI

Replace the statement with String and executed it with EXECUTE IMMEDIATE in PLSQL block and it worked.

Regards
Prashant Pathak
Re: Compilation Error in PLSQL [message #214382 is a reply to message #214282] Tue, 16 January 2007 03:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that it is the analytic function in the SQL that is causing the problem, then you'll be fine on 9iR2

What you can do as a workaround is to create a view which contains the SELECT statement you're trying to use. You will be able to access this view from within Pl/Sql, even though it contains SQL syntax that you can't use directly from Pl/Sql.
Previous Topic: how to refresh a materialzed view which refreshes on demand
Next Topic: Entries in Tnsnames.ora and Sqlnet.ora
Goto Forum:
  


Current Time: Tue Dec 06 08:14:59 CST 2016

Total time taken to generate the page: 0.30721 seconds