Solved

pls-00320

Posted on 2004-10-15
7
917 Views
Last Modified: 2012-06-21
help me out here please

When i compile my proc it gives me error at this cursor

cursor cursor_insert is
select process_status,organization_code,plan_name,r2.emp_cd,r2.emp_nm,r2.docomo_date,r2.project_cd,r2.job_cd,r2.hours
from qa_result_initial r1 , dump_tran_initial r2
where r1.emp_cd = r2.emp_cd and
r1.docomo_date = r2.docomo_date and
to_char(r2.docomo_date,'MON-YYYY') = myear;

PLS-00320: the declaration of the type of this expression is incomplete or malformed
0
Comment
Question by:frinpd
  • 4
  • 3
7 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 12323900
Is myear:
1) Exists?
2) A valid parameter or variable?
3) Is it VARCHAR2?


0
 

Author Comment

by:frinpd
ID: 12323944
All right let me tell you what i did

previously this query was like this

select process_status,organization_code,plan_name,emp_cd,emp_nm,character3,project_cd,job_cd,hours
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = to_date(r1.character3,'YYYY-MM-DD') and
to_char(r2.docomo_date,'MON-YYYY') = 'AUG-2004'  and plan_id = 108;

Where character3 from Qa_result table has data type Varchar2(25) (This is Oracle Application's Default table) and it stores data in there.

But above query does work but below query doesn't work

select process_status,organization_code,plan_name,emp_nm,docomo_date,project_cd,job_cd,hours
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select character1, to_date(character3,'yyyy-mm-dd')
from qa_results  q
where to_char(to_date(NVL(character3,'1900-01-01'),'yyyy-mm-dd'),'MON-YYYY') = 'AUG-2004'  and
plan_id = 108
);

so what i did i created qa_result_initial table and convert character3 field's data type from varchar2 to data and insert data using

insert into qa_result_initial
    (emp_cd,project_cd,docomo_date,hours,month_year,job_cd,
     emp_nm,overtime_flg
       )
       (
   select character1 as emp_cd,character2 as Project_cd,to_date(character3,'YYYY-MM-DD') as Docomo_date,character4 as Hours,
   character5 as Month_year,
   character6 as Job_cd,character7 as Emp_nm,character8 as Ot_flg from qa_results
   where plan_id = 108 and character5 = 'AUG-2004'
     );

i play around that field but couldn't find solution...
0
 

Author Comment

by:frinpd
ID: 12324036
Any idea....???
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:frinpd
ID: 12324063
Below are the tables...

SQL> desc qa_result_initial;
 Name                          
 ----------------------------
 EMP_CD                        
 EMP_NM                        
 PROJECT_CD                    
 JOB_CD                        
 DOCOMO_DATE                    
 HOURS                          
 MONTH_YEAR                    
 OVERTIME_FLG                  

SQL> desc dump_tran_initial;
 Name                          
 ----------------------------
 PROCESS_STATUS                
 ORGANIZATION_CODE              
 PLAN_NAME                      
 EMP_CD                        
 EMP_NM                        
 PROJECT_CD                    
 JOB_CD                        
 DOCOMO_DATE                    
 HOURS                          
 MONTH_YEAR                    
 OVERTIME_FLG                  
0
 

Author Comment

by:frinpd
ID: 12324083
Allright here is the procedure i am posting... any clue

CREATE OR REPLACE procedure initial_time_p(p_month varchar2, p_year varchar2) is
begin
declare

cursor CURemps is
select docomo_employee, emp_nm_ms,
project_cd,job_cd, Date_opened,Date_closed
from q_employee_master_v;


cursor cursor_insert is
select process_status,organization_code,plan_name,r2.emp_cd,r2.emp_nm,r2.docomo_date,r2.project_cd,r2.job_cd,r2.hours
from qa_result_initial r1 , dump_tran_initial r2
where r1.emp_cd = r2.emp_cd and
r1.docomo_date = r2.docomo_date and
to_char(r2.docomo_date,'MON-YYYY') = myear;



cursor cursor_update is
select process_status,organization_code,plan_name,emp_nm,docomo_date,project_cd,job_cd,hours
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select emp_cd,docomo_date
from qa_result_initial  
where month_year = myear
) ;


v_end_date date;
day_count number;
cur_date date;

myear varchar2(150);


begin

myear := p_month ||'-'||p_year;


delete from dump_tran_initial;
delete from qa_result_initial;
commit;

insert into qa_result_initial
    (emp_cd,project_cd,docomo_date,hours,month_year,job_cd,
     emp_nm,overtime_flg
       )
       (
   select character1 as emp_cd,character2 as Project_cd,to_date(character3,'YYYY-MM-DD') as Docomo_date,character4 as Hours,
   character5 as Month_year,
   character6 as Job_cd,character7 as Emp_nm,character8 as Ot_flg from qa_results
   where plan_id = 108 and character5 = myear
     );


for r1 in CURemps loop

v_end_date := nvl(r1.Date_closed,last_day(to_date(p_month || '/01/' || p_year, 'MON/DD/YYYY')));

cur_date := to_date(p_month ||p_year,'MONYYYY');
day_count := v_end_date - (cur_date - 1);

for i in 1..day_count loop

if TO_CHAR(cur_date,'fmDY') NOT IN ('SAT','SUN') then
           
                                 insert into dump_tran_initial
                                 (
                                 process_status,organization_code,plan_name,
                                 emp_cd,emp_nm,project_cd,job_cd,
                                 Docomo_date,Hours,Month_year,Overtime_flg
                                 )
                                 values
                                 (
                                 1,'USA','INITIAL TIME PARAMETER',
                                 r1.docomo_employee, r1.emp_nm_ms,r1.project_cd,
                                 r1.job_cd, cur_date, 7,myear,'No'
                                 );

end if;
cur_date := cur_date + 1;
end loop;
end loop;

commit;


for i in cursor_insert LOOP
insert into q_initial_time_parameter_iv
 (
 process_status,organization_code,plan_name,insert_type,matching_elements,
 emp_cd,emp_nm,project_cd,job_cd,
 Docomo_date,Hours,month_year,Overtime_flg
 )
values(i.process_status,i.org_code,i.plan_name,2,'emp_cd,project_cd,job_cd,docomo_date',
 i.emp_cd,i.emp_nm,i.project_cd,i.job_cd,i.Docomo_date,i.hours,i.month_year,i.overtime_flg
);
END LOOP;

for j in cursor_update LOOP
insert into q_initial_time_parameter_iv
 (
 process_status,organization_code,plan_name,
 emp_cd,emp_nm,project_cd,job_cd,
 Docomo_date,Hours,month_year,Overtime_flg
 )
values(i.process_status,i.org_code,i.plan_name,
 i.emp_cd,i.emp_nm,i.project_cd,i.job_cd,i.Docomo_date,i.hours,i.month_year,i.overtime_flg
);
END LOOP;


end;
end;
/


Mike .... help me
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12337832
Sorry, I was out for the weekend, I'll look into it...
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12338302
Does it give compile error? Whart error,  on which statement?

Compile with SET ECHO ON and then SHOW ERROR

IF You are getting no compilation errors, then it may be some data error.
Your procedure seem's to be ok.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now