Solved

pls-00320

Posted on 2004-10-15
7
942 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

630 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