Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Portal - Error WWV-17050 - Failed to parse..

Posted on 2006-04-13
3
Medium Priority
?
695 Views
Last Modified: 2013-12-01
First let me say that I am not very experienced with PL-SQL and that is probably where my error is.  I am getting this error inside of Oracle portal.  I do have a schema called famis7i.world where I have successfully established a database link.  With other simples SQL statements I am able to run reports and get the data I want.  In this particular PL-SQL code I don’t know where the problem is.  The portlet I wan to add is a “report from SQL”  The code for the report is as fallowed:  PS: Please refer to the rest of my posting after the SQL code!

select 'Q'||(to_char(add_months(sysdate,6),'Q') - 1) "QUATER",
bsc_training_hours(decode( (to_char(add_months(sysdate,6),'Q') - 1), '1','07/01/'||to_char(sysdate,'RR'), '2','10/01/'||to_char(sysdate,'RR'),'3','01/01/'||(to_char(sysdate,'RR')+1),'4','04/01/'||(to_char(sysdate,'RR')+1)),
decode( (to_char(add_months(sysdate,6),'Q') - 1), '1','10/01/'||to_char(sysdate,'RR'), '2','01/01/'||to_char(sysdate,'RR'),'3','04/01/'||(to_char(sysdate,'RR')+1),'4','07/01/'||(to_char(sysdate,'RR')+1))) "TRAINING_HOURS_FOR_QUARTER",
        bsc_training_hours_year(decode( (to_char(add_months(sysdate,6),'Q') - 1), '1','07/01/'||to_char(sysdate,'RR'), '2','10/01/'||to_char(sysdate,'RR'),'3','01/01/'||(to_char(sysdate,'RR')+1),'4','04/01/'||(to_char(sysdate,'RR')+1)),
decode( (to_char(add_months(sysdate,6),'Q') - 1), '1','10/01/'||to_char(sysdate,'RR'), '2','01/01/'||to_char(sysdate,'RR'),'3','04/01/'||(to_char(sysdate,'RR')+1),'4','07/01/'||(to_char(sysdate,'RR')+1))) "TRAINING_HOURS_PER_YEAR",
        fm_emps('1') "FM_EMPS",
        round((bsc_training_hours_year(decode( (to_char(add_months(sysdate,6),'Q') - 1), '1','07/01/'||to_char(sysdate,'RR'), '2','10/01/'||to_char(sysdate,'RR'),'3','01/01/'||(to_char(sysdate,'RR')+1),'4','04/01/'||(to_char(sysdate,'RR')+1)),
decode( (to_char(add_months(sysdate,6),'Q') - 1), '1','10/01/'||to_char(sysdate,'RR'), '2','01/01/'||to_char(sysdate,'RR'),'3','04/01/'||(to_char(sysdate,'RR')+1),'4','07/01/'||(to_char(sysdate,'RR')+1))) / fm_emps('1')),2) "AVG_PER_EMP"
from dual
     

I can tell this is calling:  (bsc_training_hours_year) and (bsc_training_hours) which are functions inside of a system schema.  So, I know this function must exist.  When I try to create (BSC_TRAINING_HOURS_YEAR) I get the fallowing error.  I am posting the code first then the error!

(p_start IN VARCHAR2, p_end IN VARCHAR2) RETURN NUMBER IS

begin
  declare
  vtrain number;
begin

  select  sum(hours)
    into vtrain
  from famis_labor@famis7i.world
  where wo_number = 'SWO001252'
  and trunc(work_date) >= to_date(p_end, 'MM/DD/RR') -365
  and trunc(work_date) <= to_date(p_end, 'MM/DD/RR')
    and employee in (select employee from famis_emp@famis7i.world where crew in ('ENGR','ENERGY','REFR','PAINT','CARP','LOCK','PLUM','SIGNAGE','AUTO','ELEC','METAL','GDSFM','GDSPARK','GDSHSE','EQUIP OPER','IRR SPEC','LEAD GRDS','RECYCLE','MOVE', 'CUST')
                   )
  order by 1;
return vtrain;
exception when others then
 vtrain := '0';
 return vtrain;
end;



end;

ERROR:

Line No. 3 : PLS-00103: Encountered the symbol "(" when expecting one of the following:

. @ % ; is authid as cluster order using external character
deterministic parallel_enable pipelined aggregate
(WWV-17050)
Line No. 5 : PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

language
(WWV-17050)
Line No. 27 : PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin function package pragma procedure form
(WWV-17050)
ORA-24344: success with compilation error (WWV-11230)
Failed to parse as PORTAL - create or replace Function SYSTEM.BSC_TRAINING_HOURS_YEAR
return VARCHAR2
(p_start IN VARCHAR2, p_end IN VARCHAR2) RETURN NUMBER IS

begin
declare
vtrain number;
begin

select sum(hours)
into vtrain
from famis_labor@famis7i.world
where wo_number = 'SWO001252'
and trunc(work_date) >= to_date(p_end, 'MM/DD/RR') -365
and trunc(work_date) <= to_date(p_end, 'MM/DD/RR')
and employee in (select employee from famis_emp@famis7i.world where crew in ('ENGR','ENERGY','REFR','PAINT','CARP','LOCK','PLUM','SIGNAGE','AUTO','ELEC','METAL','GDSFM','GDSPARK','GDSHSE','EQUIP OPER','IRR SPEC','LEAD GRDS','RECYCLE','MOVE', 'CUST')
)
order by 1;
return vtrain;
exception when others then
vtrain := '0';
return vtrain;
end;



end; (WWV-08300)


Please help!!!

Thank you.
0
Comment
Question by:tadeu_lbc
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
bochgoch earned 2000 total points
ID: 16455193
Your syntax is wrong>>

begin
declare
vtrain number;
begin

You have a declare within the first begin that is not valid, the sequence is...

declare
begin
end;

One other thing that spring to mind is your database link - has that been created within the PORTAL schema?

bg
0
 
LVL 13

Expert Comment

by:bochgoch
ID: 16455202
...Check those couple of things and I'm sure we'll get further errors to look at...
0
 

Author Comment

by:tadeu_lbc
ID: 16455413
Thank you.

I tried but still same erros!  Here is the code again!  And yes I do a database link under the rigth schema!

(p_start IN VARCHAR2, p_end IN VARCHAR2) RETURN NUMBER IS

begin
  declare
  vtrain number;
begin

  select  sum(hours)
    into vtrain
  from famis_labor@famis7i.world
  where wo_number = 'SWO001252'
  and trunc(work_date) >= to_date(p_end, 'MM/DD/RR') -365
  and trunc(work_date) <= to_date(p_end, 'MM/DD/RR')
    and employee in (select employee from famis_emp@famis7i.world where crew in ('ENGR','ENERGY','REFR','PAINT','CARP','LOCK','PLUM','SIGNAGE','AUTO','ELEC','METAL','GDSFM','GDSPARK','GDSHSE','EQUIP OPER','IRR SPEC','LEAD GRDS','RECYCLE','MOVE', 'CUST')
                   )
  order by 1;
return vtrain;
exception when others then
 vtrain := '0';
 return vtrain;
end;



end;
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question